Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » Querying data » Full text search


Text indexes

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:

Statement Description
CREATE TEXT INDEX statement Use this statement to create a text index.
ALTER TEXT INDEX statement Use this statement to alter a text index.
DROP TEXT INDEX statement Use this statement to drop a text index.
REFRESH TEXT INDEX statement Use this statement to refresh a text index, or to repopulate a text index that you truncated.
TRUNCATE TEXT INDEX statement Use this statement to truncate the data in a text index without having to recreate the text index. This is useful if you want to alter the underlying text configuration object without having to recreate dependent text indexes.

Creating text indexes
Altering text indexes