|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Frequently Asked Questions about LOBs, 8 of 12
What happens if I specify a LOB TABLESPACE, but also say ENABLE STORAGE IN ROW?
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.
I am looking for information on the pros and cons of storing images in a BFILE versus a BLOB.
Here's some basic information.
Should DISABLE STORAGE IN ROW always be specified if many UPDATEs, or SELECTs including full table scans are anticipated?
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.
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?
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?
It sounds to me like #2, but I need to check.
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.
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.
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.
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?
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!
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:
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?
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.