LOB Restrictions

1.6 LOB Restrictions

You have to keep a few restrictions in mind while working with LOB data.

LOB columns are subject to the following rules and restrictions:

You cannot specify a LOB as a primary key column.
You cannot specify LOB columns in the ORDER BY clause of a query, the GROUP BY clause of a query, or an aggregate function.
You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement, a query that uses the UNION, or a MINUS set operator if the object type of the column has a MAP or ORDER function defined on it.

Clusters cannot contain LOBs, either as key or nonkey columns.
Even though compressed VARRAY data types are supported, they are less performant.
The following data structures are supported only as temporary instances. You cannot store these instances in database tables:
- VARRAY of any LOB type
- VARRAY of any type containing a LOB type, such as an object type with a LOB attribute
- ANYDATA of any LOB type
- ANYDATA of any type containing a LOB
The first (INITIAL) extent of a LOB segment must contain at least three database blocks.
The minimum extent size is 14 blocks. For an 8K block size (the default), this is equivalent to 112K.
When creating an AFTER UPDATE DML trigger, you cannot specify a LOB column in the UPDATE OF clause. For a table on which you have defined an AFTER UPDATE DML trigger, if you use OCI functions or the DBMS_LOB package to change the value of a LOB column or the LOB attribute of an object type column, the database does not fire the DML trigger.
You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the indextype specification of a functional or domain index. In addition, Oracle Text lets you define an index on a CLOB column.
In SQL Loader, a field read from a LOB cannot be used as an argument to a clause.
Case-insensitive searches on CLOB columns often do not succeed. If you perform the following case-insensitive search on a CLOB column:
```
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
SELECT * FROM ci_test WHERE LOWER(clob_col) LIKE 'aa%';
```
The select fails without the LOWER function. You can perform case-insensitive searches with Oracle Text or the DBMS_LOB.INSTR() function.