Oracle Text Search Index Types

The Oracle Text component provides specialized indexing mechanisms for searching textual data inside Oracle databases.
Unlike standard B-tree indexes, Oracle Text indexes create inverted token structures optimized for text retrieval.

Oracle Text provides three primary index types, each designed for a different search problem:

These index types solve different information-retrieval scenarios inside Oracle.

1. CONTEXT Index

Purpose

The CONTEXT index is the primary Oracle Text index used for full-text document search.

It builds an inverted index of tokens extracted from text columns and stores them in internal DR$ tables.
This structure allows Oracle to quickly locate documents containing specific terms.

Typical use cases include:

• document repositories

• knowledge bases

• articles and reports

• email archives

• large descriptive text fields

Search operations use the CONTAINS operator.

Example search pattern:

Wildcard Searching

CONTEXT indexes support wildcard searches such as:

Wildcards available in Oracle Text:

• | prefix match |
  % | multi-character wildcard |
  _ | single-character wildcard |

Efficient wildcard searching requires enabling prefix indexing through a WORDLIST preference.
This configuration instructs Oracle Text to store additional prefix tokens during indexing.

Key parameters include:

Without these preferences, wildcard queries may require token scanning, which reduces performance.

2. CTXCAT Index

Purpose

The CTXCAT index is optimized for catalog-style searches, where text searching must be combined with structured column filtering.

Unlike CONTEXT indexes, CTXCAT indexes are designed for:

• short text fields

• transactional systems

• frequent updates

Typical use cases include:

• product catalogs

• item descriptions

• searchable transaction labels

• attribute filtering with numeric or date conditions

Search operations use the CATSEARCH operator.

Example search pattern:

In this query:

• 'laptop' represents the text search expression

• 'price < 1000' represents the structured filter

CTXCAT indexes update automatically during DML operations, so they do not require synchronization.

3. CTXRULE Index

Purpose

The CTXRULE index supports rule-based text classification.

Instead of searching documents, CTXRULE indexes rules expressed as text queries.
Incoming text is then evaluated against these rules to determine which rules match.

This reverses the traditional search direction:

becomes

Typical use cases include:

• email routing systems

• news topic filtering

• fraud detection

• alert systems

• automatic document classification

Matching operations use the MATCHES operator.

Example pattern:

The result identifies which rules apply to the document.

Comparison of Oracle Text Index Types

Conceptual Roles

The three index types address different search scenarios: