Creating an Index on LOB Column

11.3 Creating an Index on LOB Column

The contents of a LOB are often specific to the application, so an index on the LOB column will usually deal with application logic. You can create a function-based or a domain index on a LOB column to improve the performance of queries accessing data stored in LOB columns. You cannot build a B-tree or bitmap index on a LOB column.

Function-based and domain indexes are automatically updated when a DML operation is performed on the LOB column, or when a LOB is updated using an API like DBMS_LOB.

You can use the LOB Open/Close API to defer index maintenance to after a bunch of write operations. Opening a LOB in read-write mode defers any index maintenance on the LOB column until you close the LOB. This is useful when you do not want the database to perform index maintenance every time you write to the LOB. This technique can improve the performance of your application if you are doing several write operations on the LOB while it is open. Any index on the LOB column is not valid until you explicitly close the LOB.

See Also:

Before You Begin

Function-Based Indexing on LOB Columns
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.
Domain Indexing on LOB Columns
Indexes created by using Extensible Indexing interfaces are known as Domain indexes.

Parent topic: Persistent LOBs: Advanced DDL

11.3.1 Function-Based Indexing on LOB Columns

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.

See Also:

When to Use Function-Based Indexes

The following example demonstrates the creation of a function-based index on a LOB column using a SQL function:

-- Function-Based Index using a SQL function
CREATE INDEX ad_sourcetext_idx_sql ON print_media(to_char(substr(ad_sourcetext,1,10)));

The following example demonstrates the creation of a function-based index on a LOB column using a PL/SQL function:

-- Function-Based Index using a PL/SQL function
-- LOB can be an input but cannot be the return type of hte function
CREATE OR REPLACE FUNCTION Ret1st2Char(CLobInput CLOB) RETURN CHAR DETERMINISTIC IS
       First2Char         CHAR(2) ;
       NoOfChar           INTEGER ;
BEGIN
        NoOfChar := 2 ;
        DBMS_LOB.Read(CLobInput, NoOfChar, 1, First2Char) ;
        RETURN First2Char ;
END ;
/
 
CREATE INDEX ad_sourcetext_idx_plsql on print_media(Ret1st2Char(ad_sourcetext));

Parent topic: Creating an Index on LOB Column

11.3.2 Domain Indexing on LOB Columns

Indexes created by using Extensible Indexing interfaces are known as Domain indexes.

The database provides extensible indexing interfaces, 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.

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 index types.

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. For instance, an inverted index for text documents or a quad-tree for spatial features.
Build, delete, and update a domain index. The cartridge handles building and maintaining the index structures.
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 domain indexes, the database significantly reduces the effort needed to develop high-performance solutions that access complex data types such as LOBs.

Extensible Optimizer
Extensible Optmizer enables collection of statistics on user-defined functions and domain indexes.
Text Indexes on LOB Columns
If the contents of your LOB column correspond to that of a document type, users are allowed to index such a column using Oracle Text indexes.

Parent topic: Creating an Index on LOB Column

11.3.2.1 Extensible Optimizer

Extensible Optmizer enables collection of statistics on user-defined functions and domain indexes.

The SQL optimizer cannot collect statistics over LOB columns nor can it estimate the cost and selectivity of predicates involving LOB columns. Instead, the Extensible Optimizer functionality allows authors of user-defined functions and domain indexes to create statistics collection, selectivity, and cost functions. This information is used by the optimizer in choosing a query plan. The cost-based optimizer is thus extended to use the user-supplied information.

The Extensible Indexing interfaces enable you to define new operators, indextypes, and domain indexes. For such user-defined operators and domain indexes, the Extensible Optimizer interfaces allows users to control the three main components used by the optimizer to select an execution plan: statistics, selectivity, and cost. This allows the cartridge developer to tune the Extensible Optimizer for efficient execution of queries involving predicates or indexes over complex data types such as LOBs.

See Also:

Extensible Optimizer

Parent topic: Domain Indexing on LOB Columns

11.3.2.2 Text Indexes on LOB Columns

If the contents of your LOB column correspond to that of a document type, users are allowed to index such a column using Oracle Text indexes.

For example, consider the following table DOCUMENT_TABLE storing text-based documents on a CLOB column:

CREATE TABLE document_table (
    docno NUMBER,
    document CLOB);

You can index the contents of the DOCUMENT column with one of the Oracle Text indexing options to speed up text-based queries. The following example will create a SEARCH index used for text-search queries over the DOCUMENT column.

CREATE INDEX document_index ON document_table (document) INDEXTYPE IS CTXSYS.CONTEXT; 

CREATE SEARCH INDEX document_index ON document_table (document);

Note:

You can create an Oracle Text index on other formats as well. Examples of other formats include PDF, JSON, or XML.

See Also:

Creating Oracle Text Indexes

Parent topic: Domain Indexing on LOB Columns