CREATE TEXT INDEX statement

Creates a text index.

Syntax

CREATE TEXT INDEX text-index-name 
ON [ owner.]table-name( column-name, ... ) 
 [ IN dbspace-name ]
 [ CONFIGURATION [ owner.]text-configuration-name ]
 [ { IMMEDIATE REFRESH 
      | MANUAL REFRESH 
      | AUTO REFRESH [ EVERY integer { MINUTES | HOURS } ] ]
    }

Parameters

ON clause Use this clause to specify the table and columns on which to build the text index.
IN clause Use this clause to specify the dbspace in which the text index is located. If this clause is not specified, then the text index is created in the dbspace specified by the default_dbspace option. Otherwise, the SYSTEM dbspace is used. For more information, see Using additional dbspaces.
CONFIGURATION clause Use this clause to specify the text configuration object to use when creating the text index. If this clause is not specified, the default_nchar text configuration object is used if any of the columns in the index are NCHAR; otherwise, the default_char text configuration object is used.
REFRESH clause Use this clause to specify the refresh type for the text index. If you do not specify a REFRESH clause, IMMEDIATE REFRESH is used as the default. Following are the list of refresh types you can specify:
- IMMEDIATE REFRESH Specify IMMEDIATE REFRESH to refresh the text index each time changes in the underlying table impacts data in the text index.
- MANUAL REFRESH Use this clause to specify that the text index is refreshed manually.
- AUTO REFRESH Use this clause to refresh the materialized view automatically using an internal server event. Use the EVERY subclause to specify the refresh interval in minutes or hours. If you specify AUTO REFRESH without supplying interval information, the database server refreshes the text index every 60 minutes.

Remarks

You cannot create a text index on views or temporary tables.

An IMMEDIATE REFRESH text index is populated at creation time and an exclusive lock is held on the table during this initial refresh. IMMEDIATE REFRESH text indexes provide full support for queries that use snapshot isolation.

MANUAL and AUTO REFRESH text indexes must be initialized (refreshed) after creation.

Refreshes for AUTO REFRESH text indexes scan the table using isolation level 0. See isolation_level option [compatibility].

A text index may be refreshed earlier than specified by the AUTO REFRESH clause if the pending_size value, as returned by the sa_text_index_stats system procedure, exceeds 20% of the text index size at the last refresh. An internal event executes once per minute to check this condition for all AUTO REFRESH text indexes.

Once a text index is created, you cannot change it to, or from, being defined as IMMEDIATE REFRESH. If either of these changes is required, you must drop the text index and create it again.

To view text indexes and the text configuration objects they refer to, see Accessing text index information in the database.

Permissions

Must be the owner of the underlying table, or have DBA authority, or have REFERENCES permission.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Side effects

Automatic commit

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following example creates a text index, myTxtIdx, on the Description column of the MarketingInformation table in the demo database. The MarketingTextConfig text configuration object is used, and the refresh interval is set to every 24 hours.

CREATE TEXT INDEX myTxtIdx ON MarketingInformation ( Description ) 
   CONFIGURATION MarketingTextConfig
   AUTO REFRESH EVERY 24 HOURS;

Send feedback about this page via email or DocCommentXchange