Evaluating and specifying storage for Oracle LOBs

The Oracle database provides two options for storing data in large object (LOB) columns in a database table:

  • In-row option—Stores the LOB column in the row (inline) with other columns in the same data block if the length of the LOB is less than 4000 bytes. (This is the default option.)
  • Out-row option—Stores the LOB column out of the row in a separate LOB segment.

If the length is greater than 4000 bytes, the LOB value is stored out of the row in the LOB segment no matter which storage option is specified.

To specify the storage option at the Oracle database level, use the ENABLE|DISABLE STORAGE IN ROW clause of the CREATE TABLE statement.

To control Oracle character large object (CLOB) storage at the system level, use the AR System server configuration file Oracle-Clob-Storage-In-Row option.

A CLOB can hold more than 4000 characters. To create a CLOB in AR System, use one of the following fields:

  • AR System character field with a database input length of 0
  • AR System diary field 

Setting CLOB fields to be stored in-row can save storage space and improve performance for values that have fewer than 4000 characters. When values have more than 4000 characters, the in-row option is similar to the out-row option with respect to storage and performance. Although the in‑row option saves space and improves performance in many cases, BMC recommends that you conduct similar benchmark tests with your data to determine which option is best for your environment.

For details about the CLOB data type and different storage options, see your Oracle documentation.

In AR System, the attachment field creates a BLOB column. BLOBs are stored in separate tables as part of the AR System schema design. By default, BLOBs are stored in-row and are not affected by the Oracle-Clob-Storage-In-Row server configuration option.

The following topics provide more information and instructions:

Action

Reference

Understand the difference between in-row and out-row storage with an example that shows the impact on storage size and performance

Learn how to change the LOB storage option for your AR System. 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*