Before you can perform a full text search, you must create a text index on the column(s) you will search. A text index stores complete positional information for every instance of every term in every indexed column. When you perform a full text search, a text index is used to find matching rows. For this reason, queries that use text indexes can be faster than those that must scan all the values in the table.

When you create a text index, you can specify how often you want the index to be refreshed. A more recently refreshed text index returns more accurate results. However, refreshing takes time and can impede performance. As well, updates to a table can become expensive if a text index is configured to refresh each time the underlying data changes. See Creating text indexes.

You can specify which text configuration object to use when creating a text index. A text configuration object contains settings that affect how an index is built. If you do not specify a text configuration object, the database server uses the default configuration object. See Text configuration objects.

You cannot create a text index on a materialized view, a non-materialized view, or a temporary table.

Accessing text index information in the database

To view information about text indexes in the database, including the text configuration objects they refer to and statistics for each index, execute the following statement:

CALL sa_text_index_stats( );

See sa_text_index_stats system procedure.

SQL statements for managing text indexes

The following SQL statements are provided to help you manage text indexes: