Selecting LOB Storage

The large amounts of data stored in LOBs can have a significant effect on the storage characteristics of your Oracle database. Oracle gives you several different ways to store data in a LOB.

You have three options for how you store the actual LOBs, as shown in the following table:

Type of storage	Characteristics	Usage
In-line	· Enabled by defining the LOB with the ENABLE STORAGE IN ROW clause.· Can be used to store only LOBs that are smaller than 4,000 bytes.· Faster LOB retrieval performance than out of line, but slower table scans, because the table is larger.	· Use when you are fairly certain that the size of the LOB will not exceed 4,000 bytes.· Once the LOB becomes larger than 4,000 bytes, it is automatically moved to out-of-line storage.
Out-of-line	· Default storage characteristic of a LOB, or enabled with the DISABLE STORAGE IN ROW clause.· Data is stored in a tablespace, but not in the actual table row.· Data is backed up with a standard database backup.	· Use when LOBs are expected to contain more than 4,000 bytes.· Typically, you would define a separate tablespace for LOB storage.
Externally	· Uses the BFILE data type, which acts as a pointer to an operating system file.· Data in a BFILE is READ-ONLY.	Most frequently used when you either already have large file objects stored in the operating system or want to allow access to thesefiles without going through the Oracle database.

One way to store large amounts of data externally is through the BFILE data type, which is used as a pointer to an external datafile. Data stored in a BFILE can be read only by an Oracle database, but you can use the DBMS_LOB procedures on the data in a BFILE where appropriate. Regardless of whether you choose to use in-line or out-of-line storage, your LOB data types have the same functional characteristics:

1. The LOBs can participate in transactions.
2. You can use all the same SQL operations.
3. You can use the same DBMS_LOB procedures for CLOB and NCLOB data types.

In the next lesson, you will learn how to convert LONG and LONG RAW data types to LOB data types.

Click the Quiz link below to test your knowledge of LOB storage.
LOB Storage - Quiz