LOB Storage and Space Issues

What Happens If I Specify LOB Tablespace and ENABLE STORAGE IN ROW?

Question

What happens if I specify a LOB TABLESPACE, but also say ENABLE STORAGE IN ROW?

Answer

If the length of the LOB value is less than approximately 4K, then the data is stored inline in the table. When it grows to beyond approximately 4K, then the LOB value is moved to the specified tablespace.

What Are the Pros and Cons of Storing Images in a BFILE Versus a BLOB?

Question

I am looking for information on the pros and cons of storing images in a BFILE versus a BLOB.

Answer

Here's some basic information.

• Security:
  • BFILEs are inherently insecure, as insecure as your operating system (OS).

• Features:
  • BFILEs are not writable from typical database APIs whereas BLOBs are.

  • One of the most important features is that BLOBs can participate in transactions and are recoverable. Not so for BFILEs.

• Performance:
  • Roughly the same.

  • Upping the size of your buffer cache can make a BIG improvement in BLOB performance.

  • BLOBs can be configured to exist in Oracle's cache which should make repeated/multiple reads faster.

  • Piece wise/non-sequential access of a BLOB is known to be faster than a that of a BFILE.

• Manageability:
  • Only the BFILE locator is stored in an Oracle BACKUP. One needs to do a separate backup to save the OS file that the BFILE locator points to. The BLOB data is backed up along with the rest of the database data.

• Storage:
  • The amount of table space required to store file data in a BLOB will be larger than that of the file itself due to LOB index which is the reason for better BLOB performance for piece wise random access of the BLOB value.

When Should I Specify DISABLE STORAGE IN ROW?

Question

Should DISABLE STORAGE IN ROW always be specified if many UPDATEs, or SELECTs including full table scans are anticipated?

Answer

Use DISABLE STORAGE IN ROW if the other table data will be updated or selected frequently, not if the LOB data is updated or selected frequently.

Do <4K BLOBs Go Into the Same Segment as Table Data, >4K BLOBs Go Into a Specified Segment?

Question

If I specify a segment and tablespace for the BLOB, and specify ENABLE STORAGE IN ROW then look in USER_LOBS, I see that the BLOB is defined as IN_ROW and it shows that it has a segment specified. What does this mean? That all BLOBs 4K and under will go into the same segment as the table data, but the ones larger than that go into the segment I specified?

Answer

Yes.

Is 4K LOB Stored Inline?

Question

Release 8.1.5 Oracle8i SQL Reference, Chapter 4, states the following:

"ENABLE STORAGE IN ROW--specifies that the LOB value is stored in the row (inline) if its length is less than approximately 4K bytes minus system control information. This is the default. "

If an inline LOB is > 4K, which of the following possibilities is true?

1. The first 4K gets stored in the structured data, and the remainder gets stored elsewhere

2. The whole LOB is stored elsewhere

It sounds to me like #2, but I need to check.

Answer

You are correct -- it's number 2. Some meta information is stored inline in the row so that accessing the LOB value is faster. However, the entire LOB value is stored elsewhere once it grows beyond approximately 4K bytes.

1. If you have a NULL value for the BLOB locator, i.e., you have done the following:

   INSERT INTO blob_table (key, blob_column) VALUES (1, null);
   
   

   In this case I expect that you do not use any space, like any other NULL value, as we do not have any pointer to a BLOB value at all.

2. If you have a NULL in the BLOB, i.e., you have done the following:

   INSERT INTO blob_table (key, blob_column) VALUES (1, empty_blob());
   
   

   In this case you would be right, that we need at least a chunk size of space.

   We distinguish between when we use BLOBs between NULL values and empty strings.

How is a LOB Locator Stored If the LOB Column is EMPTY_CLOB() or EMPTY_BLOB() Instead of NULL? Are Extra Data Blocks Used For This?

Question

If a LOB column is EMPTY_CLOB() or EMPTY_BLOB() instead of NULL, how is the LOB locator stored in the row and are extra data blocks used for this?

Answer

See also Chapter 7, "Modeling and Design", in this manual, under "LOB Storage".

You can run a simple test that creates a table with a LOB column with attribute DISABLE STORAGE IN ROW. Insert thousands of rows with NULL LOBs.

Note that Oracle8i does not consume thousands of chunks to store NULLs!