Types of Oracle Text Indexes

With Oracle Text, you can create indexes of several types using CREATE INDEX. Table 3-1 describes each index type, its purpose, and the features it supports.

Table 3-1 Oracle Text Index Types

Index Type Description Supported Preferences and Parameters Query Operator Notes

CONTEXT

Use this index to build a text retrieval application when your text consists of large coherent documents. You can index documents of different formats such as MS Word, HTML or plain text.

You can customize the index in a variety of ways.

This index type requires CTX_DDL.SYNC_INDEX after DML on base table.

All CREATE INDEX preferences and parameters supported except for INDEX SET.

These supported parameters include the index partition clause, and the format, charset, and language columns.

CONTAINS

Grammar is called the CONTEXT grammar, which supports a rich set of operations.

The CTXCAT grammar can be used with query templating.

Supports all documents services and query services.

Supports indexing of partitioned text tables.

Supports FILTER BY and ORDER BY clauses of CREATE INDEX to also index structured column values for more efficient processing of mixed queries.

CTXCAT

Use this index type for better mixed query performance. Typically, with this index type, you index small documents or text fragments. Other columns in the base table, such as item names, prices, and descriptions can be included in the index to improve mixed query performance.

This index type is transactional, automatically updating itself after DML to base table. No CTX_DDL.SYNC_INDEX is necessary.

INDEX SET

LEXER

STOPLIST

STORAGE

WORDLIST (only prefix_index attribute supported for Japanese data)

Format, charset, and language columns not supported.

Table and index partitioning not supported.

CATSEARCH

Grammar is called CTXCAT, which supports logical operations, phrase queries, and wildcarding.

The CONTEXT grammar can be used with query templating.

Theme querying is supported.

This index is larger and takes longer to build than a CONTEXT index.

The size of a CTXCAT index is related to the total amount of text to be indexed, the number of indexes in the index set, and the number of columns indexed. Carefully consider your queries and your resources before adding indexes to the index set.

The CTXCAT index does not support index partitioning, documents services (highlighting, markup, themes, and gists) or query services (explain, query feedback, and browse words.)

CTXRULE

Use CTXRULE index to build a document classification or routing application. This index is created on a table of queries, where the queries define the classification or routing criteria.

See "CTXRULE Parameters and Limitations".

MATCHES

Single documents (plain text, HTML, or XML) can be classified using the MATCHES operator, which turns a document into a set of queries and finds the matching rows in the CTXRULE index.

See Also:

"Creating Oracle Text Indexes"

An Oracle Text index is an Oracle Database domain index. To build your query application, you can create an index of type CONTEXT with a mixture of text and structured data columns, and query it with the CONTAINS operator.

You create an index from a populated text table. In a query application, the table must contain the text or pointers to where the text is stored. Text is usually a collection of documents, but can also be small text fragments.

For better performance for mixed queries, you can create a CONTEXT index with FILTER BY and/or ORDER BY clauses to specify relational columns that will be used in structured criteria of the mixed query. Query this index with the CONTAINS operator.

Use the CTXCAT index type when your application relies heavily on mixed queries to search small documents or descriptive text fragments based on related criteria such as dates or prices. Query this index with the CATSEARCH operator.

To build a document classification application using simple or rule-based classification, create an index of type CTXRULE. This index classifies plain text, HTML, or XML documents using the MATCHES operator. Store your defining query set in the text table you index.

Note:

If you are building a new application that uses XML data, Oracle recommends that instead of using CTXRULE, you use XMLIndex. For information about XMLIndex and indexing XMLType data, see Oracle XML DB Developer's Guide.

Create a text index as a type of extensible index to Oracle Database using standard SQL. This means that an Oracle Text index operates like an Oracle Database index. It has a name by which it is referenced and can be manipulated with standard SQL statements.

The benefits of a creating an Oracle Text index include fast response time for text queries with the CONTAINS, CATSEARCH, and MATCHES Oracle Text operators. These operators query the CONTEXT, CTXCAT, and CTXRULE index types respectively.

Note:

Because a Transparent Data Encryption-enabled column does not support domain indexes, it cannot be used with Oracle Text. However, you can create an Oracle Text index on a column in a table stored in a Transparent Data Encryption-enabled tablespace.

See Also:

"Creating Oracle Text Indexes"