CREATE TEXT INDEX statement

Creates a text index.

Syntax

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

Parameters

IF NOT EXISTS clause
When the IF NOT EXISTS clause is specified and the named text index exists, no changes are made and an error is not returned.
ON clause
Specify the table and columns on which to build the text index.
IN clause
Specify the dbspace in which the text index is located. If this clause is not specified, then the text index is created in the same dbspace as the table it references.
CONFIGURATION clause
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
Specify the refresh type for the text index. If you do not specify a REFRESH clause, IMMEDIATE REFRESH is used as the default. You can specify the following refresh types:
- IMMEDIATE REFRESH
  Refreshes the text index each time changes in the underlying table or the materialized view impact data in the text index.
- AUTO REFRESH
  Refreshes the text index automatically using an internal server event. Use the EVERY sub-clause 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. A text index may be refreshed earlier than the interval 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 or if the deleted_length exceeds 50% of the text index size. An internal event executes once per minute to check this condition for all AUTO REFRESH text indexes.
- MANUAL REFRESH
  The text index is refreshed manually.

Remarks

Creating more than one text index referencing a column can return unexpected results, and is not recommended.

You cannot create a text index on a regular view or a temporary table.

Once a text index is created on a materialized view, it cannot be refreshed or truncated, it can only be dropped. The text index on a materialized view is maintained by the database server whenever the underlying materialized view is refreshed or updated.

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

An IMMEDIATE REFRESH text index on a base table 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.

An IMMEDIATE REFRESH text index on a materialized view is populated at creation time if the view is populated.

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.

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, drop and recreate the text index.

You can choose to manually refresh an AUTO REFRESH text index by using the REFRESH TEXT INDEX statement.

Privileges

To create a text index on a table, you must be the owner of the table, or have one of the following privileges:

REFERENCES privilege on the table
CREATE ANY INDEX system privilege
CREATE ANY OBJECT system privilege

To create a text index on a materialized view, you must be the owner of the materialized view, or have one of the following privileges:

CREATE ANY INDEX system privilege
CREATE ANY OBJECT system privilege

Side effects

Automatic commit

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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

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