2.3.2 Text indexes

Text indexes are used for retrieving character string data that contains character strings specified as the retrieval criteria in SQL statements. A text index manages, in pages, information about the positions of data items (character strings) that are stored in columns in the base table for which the text index has been defined.

If one of the following items is specified in the retrieval criteria in an SQL statement that retrieves data from a table, use of a text index reduces the number of pages to be loaded. This improves table retrieval performance.

LIKE predicate
LIKE_REGEX predicate
Scalar function CONTAINS

A text index consists of the following two types of controls:

String control

String control manages text-indexed character strings.
Position control

Position control manages the positions of text-indexed character strings.

The following figure shows the structure of the text index controls.

Figure 2‒6: Structure of text index controls

The following figure shows the general procedure for using a text index to retrieve data from a base table.

Figure 2‒7: Overview of using a text index for retrieval processing

Explanation

A base table is retrieved using a text index with the following procedure:

Searches for the search string specified in the LIKE predicate (ai) from the string control. Then obtains the corresponding string number (12).
Retrieves the corresponding position page numbers (1, 3 and 511) from the position control by using the character string number (12) retrieved from the string control as the key value.
Imports to base table T1 the data pages that correspond to the position page numbers (1, 3 and 511) in the position control. Then retrieves the corresponding row IDs (2, 3, 8 and 1533) to obtain the table data. Data pages that do not correspond to the position page numbers are not imported.

Note: If a B-tree index and a text index are both defined for a column specified as a retrieval criterion, only the B-tree index or only the text index is used. However, depending on the specified retrieval criteria, neither of the indexes might be used.

To Page Top