CREATE TEXT INDEX statement

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 } ] } ]

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.
For more information about refresh types, see Text index refresh types.

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.

To view text indexes and the text configuration objects they refer to, see Viewing text index terms and settings (Sybase Central).

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

Automatic commit

SQL/2008 Vendor extension.

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;

CREATE TEXT INDEX statement

Syntax

Parameters

Remarks

Privileges

Side effects

See also

Standards and compatibility

Example