You can set a persistent LOB — ­that is, a LOB column in a table, or a LOB attribute in an object type that you defined— to be NULL or empty:

• Set a Persistent LOB to NULL: A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other data types.

• Set a Persistent LOB to Empty: By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, then you get back a locator which you can use to populate the LOB with data using supported programmatic environments, such as OCI or PL/SQL(DBMS_LOB).

DECLARE
   Lob_loc  BLOB;
BEGIN
   INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc;
   /* Now use the locator Lob_loc to populate the BLOB with data */
END;

The first extent of any segment requires at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent if the LOB is a BasicFiles LOB and 16 blocks if the LOB is a SecureFiles LOB.

If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, then it still works because it is possible for segments in permanent dictionary-managed tablespaces to override the default storage setting of the tablespaces.

But if uniform, locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, then LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and the default storage setting of the tablespaces is not ignored.

Varying-width character data in CLOB and NCLOB data types is stored in an internal format that is compatible with UCS2 Unicode character set format. This ensures that there is no storage loss of character data in a varying-width format. Also note the following if you are using LOBs to store varying-width character data:

• You can create tables containing CLOB and NCLOB columns even if you use a varying-width CHAR or NCHAR database character set.

• You can create a table containing a data type that has a CLOB attribute regardless of whether you use a varying-width CHAR database character set.

LOB columns store locators that reference the location of the actual LOB value.

Actual LOB values are stored either in the table row (inline) or outside of the table row (out-of-line), depending on the column properties you specify when you create the table, and depending the size of the LOB.

LOB values are stored out-of-line when any of the following situations apply:

• If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage clause when you create the table.

• If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.

• If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.

LOB values are stored inline when any of the following conditions apply:

• When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the default).

• When the LOB value is NULL (regardless of the LOB storage properties for the column).

Using the default LOB storage properties (inline storage) can allow for better database performance; it avoids the overhead of creating and managing out-of-line storage for smaller LOB values. If LOB values stored in your database are frequently small in size, then using inline storage is recommended.

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

• TABLESPACE

• PCTVERSION or RETENTION

  Note that you can specify either PCTVERSION or RETENTION for BasicFiles LOBs, but not both. For SecureFiles, only the RETENTION parameter can be specified.

• CACHE/NOCACHE/CACHE READS

• LOGGING/NOLOGGING

• CHUNK

• ENABLE/DISABLE STORAGE IN ROW

• STORAGE

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

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 system determines which tablespace to use for LOB data and LOB index depending on your specification in the LOB storage clause:

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

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

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

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

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100.

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

• How often BasicFiles LOBs are updated?

• How often the updated BasicFiles LOBs are read?

Table 13-2 provides some guidelines for determining a suitable PCTVERSION value given an update percentage of 'X'.

If your application requires several BasicFiles LOB updates concurrent with heavy reads of BasicFiles LOB columns, then consider using a higher value for PCTVERSION, such as 20%.

Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of BasicFiles LOB columns, it may be useful to retain old versions of BasicFiles LOB pages. In this case, BasicFiles LOB storage may grow because the database does not reuse free pages aggressively.

If persistent BasicFiles LOB instances in your application are created and written just once and are primarily read-only afterward, then updates are infrequent. In this case, consider using a lower value for PCTVERSION, such as 5% or lower.

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

You can specify the RETENTION parameter in the LOB storage clause of the CREATE TABLE or ALTER TABLE statement as an alternative to the PCTVERSION parameter,. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the table space. For example:

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

The RETENTION parameter is designed for use with UNDO features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

Note the following with respect to the RETENTION parameter:

• UNDO SQL is not enabled for LOB columns as it is with other data types. You must set the RETENTION property on a LOB column to use Undo SQL on LOB data.

• You cannot set the value of the RETENTION parameter explicitly. The amount of time for retention of LOB versions in determined by the UNDO_RETENTION parameter.

• Usage of the RETENTION parameter is only supported in Automatic Undo Management mode. You must configure your table for use with Automatic Undo Management before you can set RETENTION on a LOB column. ASSM is required for LOB RETENTION to be in effect for BasicFiles LOBs. The RETENTION parameter of the SQL (in the STORE AS clause) is silently ignored if the BasicFiles LOB resides in an MSSM tablespace.

• The LOB storage clause can specify RETENTION or PCTVERSION, but not both.

  See Also:

  • Oracle Database Development Guide for more information on using flashback features of the database.

  • Oracle Database SQL Language Reference for details on LOB storage clause syntax.

Specifying the RETENTION parameter for SecureFiles indicates that the database manages consistent read data for the SecureFiles storage dynamically, taking into account factors such as the UNDO mode of the database.

• Specify MAX if the database is in FLASHBACK mode to limit the size of the LOB UNDO retention in bytes. If you specify MAX, then you must also specify the MAXSIZE clause in the storage_clause.

• Specify AUTO if you want to retain UNDO sufficient for consistent read purposes only. This is the default.

• Specify NONE if no UNDO is required for either consistent read or flashback purposes.

The default RETENTION for SecureFiles is AUTO.

When creating tables that contain LOBs, use the cache options according to the guidelines in Table 13-3:

The [NO]LOGGING parameter is applied to using LOBs in the same manner as for other table operations. In the usual case, if the [NO]LOGGING clause is omitted, then this means that neither NOLOGGING 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 segment resides.

The following issues should also be kept in mind.

The NOLOGGING and LOGGING parameters are applied to using LOBs in the same manner as for other table operations.

In the usual case, if the logging_clause is omitted, then the SecureFiles inherits its logging attribute from the tablespace in which it resides. In this case, if NOLOGGING is the default value, the SecureFiles defaults to FILESYSTEM_LIKE_LOGGING.

A chunk is one or more Oracle blocks.

You can specify the chunk size for the BasicFiles LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE. In OCI, use OCILobGetChunkSize().

The default is ENABLE STORAGE IN ROW.

The maximum amount of LOB data stored in the row is the maximum VARCHAR2 size (4000). This includes the control information and 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 approximately 4000, then 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 (less than approximately 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 (greater than approximately 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.

You might be able to improve the performance of queries by building indexes specifically attuned to your domain. Extensibility interfaces provided with the database allow for domain indexing, a framework for implementing such domain specific indexes.

Depending on the nature of the contents of the LOB column, one of the Oracle Text options could also be used for building indexes.

For example, if a text document is stored in a CLOB column, then you can build a text index to speed up the performance of text-based queries over the CLOB column.

A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Function-based indexes cannot be built on nested tables or LOB columns. However, you can build function-based indexes on VARRAYs.

Like extensible indexes and domain indexes on LOB columns, function-based indexes are also automatically updated when a DML operation is performed on the LOB column. Function-based indexes are also updated when any extensible index is updated.

The database provides extensible indexing, a feature which enables you to define new index types as required. This is based on the concept of cooperative indexing where a data cartridge and the database build and maintain indexes for data types such as text and spatial for example, for On-line-Analytical Processing (OLAP).

The cartridge is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure can be stored in Oracle as heap-organized, or an index-organized table, or externally as an operating system file.

To support this structure, the database provides an indextype. The purpose of an indextype is to enable efficient search and retrieval functions for complex domains such as text, spatial, image, and OLAP by means of a data cartridge. An indextype is analogous to the sorted or bit-mapped index types that are built-in within the Oracle Server. The difference is that an indextype is implemented by the data cartridge developer, whereas the Oracle kernel implements built-in indexes. Once a new indextype has been implemented by a data cartridge developer, end users of the data cartridge can use it just as they would built-in indextypes.

When the database system handles the physical storage of domain indexes, data cartridges

• Define the format and content of an index. This enables cartridges to define an index structure that can accommodate a complex data object.

• Build, delete, and update a domain index. The cartridge handles building and maintaining the index structures. Note that this is a significant departure from the medicine indexing features provided for simple SQL data types. Also, because an index is modeled as a collection of tuples, in-place updating is directly supported.

• Access and interpret the content of an index. This capability enables the data cartridge to become an integral component of query processing. That is, the content-related clauses for database queries are handled by the data cartridge.

By supporting extensible indexes, the database significantly reduces the effort needed to develop high-performance solutions that access complex data types such as LOBs.

You can create Oracle Text indexes on CLOB columns and perform queries on XML data.

As a result, LOBs can take advantage of all of the benefits of partitioning including the following:

• LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable.

• LOBs in a partitioned table become easier to maintain.

• LOBs can be partitioned into logical groups to speed up operations on LOBs that are accessed as a group.

This section describes some of the ways you can manipulate LOBs in partitioned tables.