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

Oracle9i SQL Reference, 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 Oracle9i does not consume thousands of chunks to store NULLs!

Storing CLOBs Inline: DISABLING STORAGE and Space Used

Question

I have questions about storing CLOBs inline outside the row. We know when you create a table with LOB column, you can specify DISABLE STORAGE IN ROW or ENABLE STORAGE IN ROW. My questions are:

1. When you specify ENABLE STORAGE IN ROW, does is mean it stores the LOB information in the same block as that row?

2. I found the size of the table itself (not including the CLOB segment) with ENABLE STORAGE IN ROW is much bigger than the size of the table with DISABLE STORAGE IN ROW, and I know I have separate segment for the CLOB column in both tables. Why?

3. I also noticed that DISABLing STORAGE IN ROW needs much more space. Why is this?

Answer

1. If the LOB value is less than approximately 4k than the value is stored inline in the table. Whether or not the entire row is stored in one block depends on the size of the row. Big rows will span multiple blocks. If the LOB is more than 4k, then the LOB value is stored in a different segment.

2. This is because LOBs less than 4k will be stored inline in the table's segment.

3. I need more information to see why this is happening.

Should I Include a LOB Storage Clause When Creating Tables With Varray Columns?

Question

What are the effects of providing or not providing a LOB storage clause when creating a table containing a Varray column? The documentation suggest that Varrays will be stored inline or in a LOB depending on their size, so I assume this would be the case even if no LOB storage clause were provided? Does providing one imply that a LOB will always be used?

I assume LOB are named for a reason. It is not clear to me what use the names might be. I understand that it is convenient to name the nested table storage table because you may want to index it, alter it, and so on. But what can I do with the LOB? The only example I found was one that modifies the LOB to cache it?

Answer

The documentation says: "Varrays are stored in columns either as raw values or BLOBs. Oracle decides how to store the varray when the varray is defined, based on the maximum possible size of the varray computed using the LIMIT of the declared varray. If the size exceeds approximately 4000 bytes, then the varray is stored in BLOBs. Otherwise, the varray is stored in the column itself as a raw value. In addition, Oracle supports inline LOBs; therefore, elements that fit in the first 4000 bytes of a large varray (with some bytes reserved for the LOB locator) are stored in the column of the row itself."

So, your data will be inline as raw data if you have less than about 4000 bytes and you do NOT specify a LOB storage clause for your varray.

The documentation also says (SQL Reference):

"varray_storage_clause: lets you specify separate storage characteristics for the LOB in which a varray will be stored. In addition, if you specify this clause, Oracle will always store the varray in a LOB, even if it is small enough to be stored inline."

So, if you do specify this varray_storage_clause, then you will always be storing your varrays in LOBs. However, according to the first paragraph, varrays also support inline LOBs, so by default your first 4000 bytes or so will still be stored inline in the table's row with the other data as an inline LOB. It will also have some extra LOB overhead.

To clarify, if you specify varray store as LOB, and the column you've defined has a max size less than 4000 bytes, then it will be stored as an inline LOB. Here's the whole synopsis:

Calculate MAX possible size of your column, remember that there is some overhead involved so if you have 10 elements of size 1000 the MAX size is still a little bit greater than 10*1000.

• If the max is less than (4000 minus some small number of bytes) and varray store as LOB is not specified, then it is stored as a raw inline.

• If the max is less than (4000 minus a small # of bytes) and varray store as LOB is specified and disable storage in row is not specified, then it is stored as an inline LOB

• If the max is less than (4000 - small # of bytes) and varray store as LOB is specified and disable storage in row is specified, then it is stored out of line in a LOB.

• If the max is greater than 4000 or so bytes it will always be stored in a LOB even if you don't have a varray store as LOB clause. It can be either an inline one or an out of line LOB depending on it's size and whether or not you've specified disable storage in row.

• If you do not specify a LOB storage clause or if you have a storage clause that doesn't specify disable storage in row then the system will automatically try to put small LOBs inline and only put them out of line when they are greater than approximately 4000 bytes. Otherwise, if the user specifies disable storage in row they will always be out of line.