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.

  • 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 impact data in the text index.

    • 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. 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.

    • MANUAL REFRESH   Use this clause to specify that the text index is refreshed manually.

    For more information about refresh types, see Text index refresh types.

Remarks

You cannot create a text index on views, materialized 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 [database] [compatibility].

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

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

To view text indexes and the text configuration objects they refer to, see View text indexes 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

See also
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;
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1