Modeling and Design, 11 of 21

Indexing a LOB Column

You cannot build B-tree or bitmap indexes on a LOB column. However, depending on your application and its usage of the LOB column, you might be able to improve the performance of queries by building indexes specifically attuned to your domain. Oracle8i and higher's extensibility interfaces allow for domain indexing, a framework for implementing such domain specific indexes.

See Also:

Oracle9i Data Cartridge Developer's Guide, for information on building domain specific indexes.

Depending on the nature of the contents of the LOB column, one of the Oracle interMedia options could also be used for building indexes. For example, if a text document is stored in a CLOB column, you can build a text index (provided by Oracle) to speed up the performance of text-based queries over the CLOB column.

See Also:

Oracle interMedia User's Guide and Reference and Oracle9i Text Reference, for more information regarding Oracle's interMedia options.

Functional Indexes on LOB Columns

Oracle9i now supports functional indexing on LOB columns. Analogous to extensible/domain indexes on LOB columns, functional indexes are also automatically updated when a DML operation is performed on the LOB column.

Note:

When extensible indexes are being updated, if any functional indexes are present on the LOB column, they are also updated.