Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)

Part Number A88879-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to beginning of chapter Go to next page

Modeling and Design, 4 of 21

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.

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

      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.


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:

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.


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.


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:

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.


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:

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 LOB


Updates XX% of LOB data 

Reads LOBs but not the updated LOB


Updates XX% of LOB data 

Reads both updated and non-updated LOB


Never updates LOB 

Reads LOB


Example 1:

Several LOB updates concurrent with heavy reads of LOBs.


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.


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 ... 




NOCACHE (default) 

Once or occasionally 




Once or occasionally 

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

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.


[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.

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.


CACHE implies that you also get LOGGING


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.


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:


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.


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 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:

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.

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index