LOB Storage

Where are NULL Values in a LOB Column Stored?

NULL LOB Column Storage: NULL Value is Stored

If a LOB column is NULL, no data blocks are used to store the information. The NULL value is stored in the row just like any other NULL value. This is true even when you specify DISABLE STORAGE IN ROW for the LOB.

EMPTY_CLOB() or EMPTY_BLOB() Column Storage: LOB Locator is Stored

If a LOB column is initialized with EMPTY_CLOB() or EMPTY_BLOB(), instead of NULL, a LOB locator is stored in the row. No additional storage is used.

• DISABLE STORAGE IN ROW: If you have a LOB with one byte of data, there will be a LOB locator in the row. This is true whether or not the LOB was created as ENABLE or DISABLE STORAGE IN ROW. In addition, an entire chunksize of data blocks is used to store the one byte of data if the LOB column was created as DISABLE STORAGE IN ROW.

• ENABLE STORAGE IN ROW: If the LOB column was created as ENABLE STORAGE IN ROW, Oracle8i and higher only consumes one extra byte of storage in the row to store the one byte of data. If you have a LOB column created with ENABLE STORAGE IN ROW and the amount of data to store is larger than will fit in the row (approximately 4,000 bytes) Oracle uses a multiple of chunksizes to store it.

Defining Tablespace and Storage Characteristics for Internal LOBs

When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal LOB.

Defining Tablespace and Storage Example1

CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)  
      lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096 
                        PCTVERSION 5 
                        NOCACHE LOGGING 
                        STORAGE (MAXEXTENTS 5) 
                       ); 

There are no extra tablespace or storage characteristics for external LOBs since they are not stored in the database.

If you later wish to modify the LOB storage parameters, use the MODIFY LOB clause of the ALTER TABLE statement.

Note: Only some storage parameters may be modified! For example, you can use the ALTER TABLE ... MODIFY LOB statement to change PCTVERSION, CACHE/NO CACHE LOGGING/NO LOGGING, and the STORAGE clause. You can also change the TABLESPACE via the ALTER TABLE ...MOVE statement. However, once the table has been created, you cannot change the CHUNK size, or the ENABLE/DISABLE STORAGE IN ROW settings.

Assigning a LOB Data Segment Name

As shown in the "Defining Tablespace and Storage Example1" , specifying a name for the LOB data segment makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, DBA_LOBS (see Oracle9i Reference), you see the LOB data segment that you chose instead of system-generated names.

LOB Storage Characteristics for LOB Column or Attribute

LOB storage characteristics that can be specified for a LOB column or a LOB attribute include the following:

• TABLESPACE

• PCTVERSION

• CACHE/NOCACHE/CACHE READS

• LOGGING/NOLOGGING

• CHUNK

• ENABLE/DISABLE STORAGE IN ROW

• STORAGE. See the "STORAGE clause" in Oracle9i SQL Reference for more information.

For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, you should consider the following guidelines.

TABLESPACE and LOB Index

Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention.

The LOB index is an internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it.

Note: The LOB index cannot be altered.

The system determines which tablespace to use for LOB data and LOB index depending on you specification in the LOB storage clause:

• If you do not specify a tablespace for the LOB data, the table's tablespace is used for the LOB data and index.

• If you specify a tablespace for the LOB data, both the LOB data and index use the tablespace that was specified.

Tablespace for LOB Index in Non-Partitioned Table

If in creating tables in Oracle8i Release 8.1 you specify a tablespace for the LOB index for a non-partitioned table, your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.

Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the table's tablespace.

PCTVERSION

When a LOB is modified, a new version of the LOB page is produced in order to support consistent read of prior versions of the LOB value.

PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

Default: 10 (%) Minimum: 0 (%) Maximum: 100 (%)

To decide what value PCTVERSION should be set to, consider the following:

• How often LOBs are updated?

• How often the updated LOBs are read?

Table 7-2, "Recommended PCTVERSION Settings" provides some guidelines for determining a suitable PCTVERSION value.

Table 7-2 Recommended PCTVERSION Settings

LOB Update Pattern	LOB Read Pattern	PCTVERSION
Updates XX% of LOB data	Reads updated LOBs	XX%
Updates XX% of LOB data	Reads LOBs but not the updated LOBs	0%
Updates XX% of LOB data	Reads both updated and non-updated LOBs	XX%
Never updates LOB	Reads LOBs	0%

Example 1:

Several LOB updates concurrent with heavy reads of LOBs.

SET PCTVERSION = 20%

Setting PCTVERSION to twice the default allows more free pages to be used for old versions of data pages. Since large queries may require consistent reads of LOBs, it may be useful to retain old versions of LOB pages. In this case LOB storage may grow because Oracle will not reuse free pages aggressively.

Example 2:

LOBs are created and written just once and are primarily read-only afterwards. Updates are infrequent.

SET PCTVERSION = 5% or lower

The more infrequent and smaller the LOB updates are, the less space needs to be reserved for old copies of LOB data. If existing LOBs are known to be read-only, you could safely set PCTVERSION to 0% since there would never be any pages needed for old versions of data.

CACHE / NOCACHE / CACHE READS

When creating tables that contain LOBs, use the cache options according to the guidelines in Table 7-3, "When to Use CACHE, NOCACHE, and CACHE READS":

Table 7-3 When to Use CACHE, NOCACHE, and CACHE READS

Cache Mode	Read ...	Written To ...
CACHE	Frequently	Frequently
NOCACHE (default)	Once or occasionally	Never
CACHE READS	Frequently	Once or occasionally

CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache

• CACHE: Oracle places LOB pages in the buffer cache for faster access.

• NOCACHE: As a parameter in the LOB_storage_clause, NOCACHE specifies that LOB values are either not brought into the buffer cache or are brought into the buffer cache and placed at the least recently used end of the LRU list.

• CACHE READS: LOB values are brought into the buffer cache only during read and not during write operations.

Downgrading to 8.1.5 or 8.0.x

If you have CACHE READS set for LOBs in 8.1.6 and you downgrade to 8.1.5 or 8.0.x, your CACHE READS LOBs generate a warning and become CACHE LOGGING LOBs.

You can explicitly alter the LOBs' storage characteristics later if you do not want your LOBs to be CACHE LOGGING. For example, if you want the LOBs to be NOCACHE, use ALTER TABLE to clearly modify them to NOCACHE.

LOGGING / NOLOGGING

[NO] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [NO]LOGGING clause is omitted, this means that neither NO LOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.

For LOBs, there is a further alternative depending on how CACHE is stipulated.

• CACHE is specified and [NO]LOGGING clause is omitted, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).

• CACHE is not specified and [NO]LOGGING clause is omitted, the process defaults in the same way as it does for tables and partitioned tables. That is, the [NO]LOGGING value is obtained from the tablespace in which the LOB value resides.

The following issues should also be kept in mind.

LOBs Will Always Generate Undo for LOB Index Pages

Regardless of whether LOGGING or NOLOGGING is set LOBs will never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes.

When LOGGING is Set Oracle Will Generate Full Redo for LOB Data Pages

NOLOGGING is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, you will not be able to recover your changes from the log since the changes were never logged.

NOLOGGING is Useful for Bulk Loads or Inserts.

For instance, when loading data into the LOB, if you do not care about redo and can just start the load over if it fails, set the LOB's data segment storage characteristics to NOCACHE NOLOGGING. This provides good performance for the initial load of data.

Once you have completed loading data, if necessary, use ALTER TABLE to modify the LOB storage characteristics for the LOB data segment for normal LOB operations -- i.e. to CACHE or NOCACHE LOGGING.

Note: CACHE implies that you also get LOGGING.

CHUNK

Set CHUNK to the total bytes of LOB data in multiples of database block size, that is, the number of blocks that will be read or written via OCILobRead(), OCILobWrite(), DBMS_LOB.READ(), or DBMS_LOB.WRITE() during one access of the LOB value.

Note: The default value for CHUNK is one Oracle block and does not vary across platforms.

If only one block of LOB data is accessed at a time, set CHUNK to the size of one block. For example, if the database block size is 2K, then set CHUNK to 2K.

Set INITIAL and NEXT to Larger than CHUNK

If you explicitly specify storage characteristics for the LOB, make sure that INITIAL and NEXT for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK of 8K, make sure that INITIAL and NEXT are bigger than 8K and preferably considerably bigger (for example, at least 16K).

Put another way: If you specify a value for INITIAL, NEXT or the LOB CHUNK size, make sure they are set in the following manner:

• CHUNK <= NEXT

• CHUNK <= INITIAL

ENABLE | DISABLE STORAGE IN ROW

You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (i.e. in the row) or out of line.

Note: You may not alter this specification once you have made it: if you ENABLE STORAGE IN ROW, you cannot alter it to DISABLE STORAGE IN ROW and vice versa.

The default is ENABLE STORAGE IN ROW.

Guidelines for ENABLE or DISABLE STORAGE IN ROW

The maximum amount of LOB data stored in the row is the maximum VARCHAR2 size (4000). This includes the control information as well as the LOB value. If you indicate that the LOB should be stored in the row, once the LOB value and control information is larger than 4000, the LOB value is automatically moved out of the row.

This suggests the following guidelines:

The default, ENABLE STORAGE IN ROW, is usually the best choice for the following reasons:

• Small LOBs: If the LOB is small (i.e. < 4000 bytes), then the whole LOB can be read while reading the row without extra disk I/O.

• Large LOBs: If the LOB is big (i.e., > 4000 bytes), then the control information is still stored in the row if ENABLE STORAGE IN ROW is set, even after moving the LOB data out of the row. This control information could enable us to read the out-of-line LOB data faster.

However, in some cases DISABLE STORAGE IN ROW is a better choice. This is because storing the LOB in the row increases the size of the row. This impacts performance if you are doing a lot of base table processing, such as full table scans, multi-row accesses (range scans), or many UPDATE/SELECT to columns other than the LOB columns.