|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 4 of 21
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.
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. 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.
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
STORAGE IN ROWand 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.
LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal
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
LOB clause of the
However, once the table has been created, you cannot change the CHUNK size, or the ENABLE/DISABLE STORAGE IN ROW settings.
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
DBA_LOBS (see Oracle9i Reference), you see the
LOB data segment that you chose instead of system-generated names.
LOB storage characteristics that can be specified for a
LOB column or a
LOB attribute include the following:
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.
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.
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 system determines which tablespace to use for
LOB data and
LOB index depending on you specification in the
LOB storage clause:
LOBdata, the table's tablespace is used for the
LOBdata and index.
LOBdata, both the
LOBdata and index use the tablespace that was specified.
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.
LOB is modified, a new version of the
LOB page is produced in order to support consistent read of prior versions of the
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
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
|LOB Update Pattern||LOB Read Pattern||PCTVERSION|
Updates XX% of
Updates XX% of
Updates XX% of
Reads both updated and non-updated
Several LOB updates concurrent with heavy reads of LOBs.
PCTVERSION = 20%
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.
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":
|Cache Mode||Read ...||Written To ...|
Once or occasionally
Once or occasionally
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
LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [
LOGGING clause is omitted, this means that neither
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.
LOBs, there is a further alternative depending on how
CACHE is stipulated.
LOGGINGclause is omitted,
LOGGINGis automatically implemented (because you cannot have
LOGGINGclause is omitted, the process defaults in the same way as it does for tables and partitioned tables. That is, the [
LOGGINGvalue is obtained from the tablespace in which the
The following issues should also be kept in mind.
Regardless of whether
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.
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.
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
NOLOGGING. This provides good performance for the initial load of data.
Once you have completed loading data, if necessary, use
TABLE to modify the
LOB storage characteristics for the
LOB data segment for normal
LOB operations -- i.e. to
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
WRITE() during one access of the
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.
If you explicitly specify storage characteristics for the
LOB, make sure that
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
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
ROW clause to indicate whether the
LOB should be stored inline (i.e. in the row) or out of line.
The default is
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.