CREATE TEXT INDEX Statement

Syntax

CREATE TEXT INDEX text-index-name
   ON [ owner.]table-name( column-name, ...)
   [ IN dbspace-name ]
   [ CONFIGURATION [ owner.]text-configuration-name]
   [ IMMEDIATE REFRESH ]

Examples

• Example 1 – Create a TEXT index, myTxtIdx, on the CompanyName column of the Customers table in the iqdemo database, using the max_term_sixteen text configuration object:

  CREATE TEXT INDEX myTxtIdx ON Customers (CompanyName );

  CONFIGURATION max_term_sixteen;

Usage

Use CREATE TEXT INDEX to create a TEXT index and to specify the text configuration object to use.

You cannot create a TEXT index on views or temporary tables. You cannot create a TEXT index on an IN SYSTEM materialized view.

The BEGIN PARALLEL IQ…END PARALLEL IQ statement does not support CREATE TEXT INDEX.

ON clause – specifies the table and column on which to build the TEXT index.

IN clause – specifies 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 underlying table.

CONFIGURATION clause – specifies the text configuration object to use when creating the TEXT index. If this clause is not specified, the default_char text configuration object is used.

REFRESH clause – IMMEDIATE REFRESH is used as the default and is the only permitted value for tables in SAP Sybase IQ. Specify IMMEDIATE REFRESH to refresh the TEXT index each time changes in the underlying table impact data in the TEXT index.

An IMMEDIATE REFRESH TEXT index is populated at creation and is refreshed whenever the data in the underlying column is changed. Once a TEXT index is created, you cannot change it to, or from, IMMEDIATE REFRESH.

Permissions