Blocksizes for BLOB table
I am working with a Oracle EE 9i (9.2.0.1) database and have a table that has a BLOB. The average size of attachments being stored in BLOB are 500k. The attachments are inserted and then, for the most part, only read. I have created a tablespace for a BLOB table with 32k blocksize, and the rest of database has an 8k blocksize. I made chunck size also 32k and pctversion 5, because of the low update. Would you recommend this setup? One thing I noticed when querying dba_blobs is that the chunk size for BLOB showed 131072 even though I created it as 32k. I thought max chunk size was 32k? I'm on SUN Solaris. What file system block size would you recommend for datafiles of my 32k tablespace? I believe default is 8k.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

You've indicated that your average BLOB size will be 500K. Unfortunately, you don't have a block size big enough to read the entire BLOB in one read operation. So multiple read operations will be required, no matter what you do. You've elected to go with the largest block size available to you, which isn't a bad idea. If you choose the 32K blocksize, then you can either leave the filesystem's blocksize at 8K or up it to 32K. It all depends on if there are other tablespaces, with other block sizes, on this filesystem. If you have a tablespace with a smaller blocksize on this filesystem, then use that blocksize for the filesystem blocksize.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top