CREATE TEXT INDEX Statement

Note: This statement requires the Unstructured Data Analytics (IQ_UDA) license.

Quick Links:

Syntax

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

Parameters

(back to top)

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

IN – 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 – 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.

IMMEDIATE REFRESH – (default) refreshes the TEXT index each time changes in the underlying table impact data in the TEXT index. Only permitted value for tables in SAP Sybase IQ main store. Once created, the IMMEDIATE REFRESH clause cannot be changed.

Examples

(back to top)

Example 1 – creates 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

(back to top)

You cannot create a TEXT index on views or temporary tables, or on an IN SYSTEM materialized view. The BEGIN PARALLEL IQ…END PARALLEL IQ statement does not support CREATE TEXT INDEX.

Side Effects:

Automatic commit.

Permissions

(back to top)

Requites one of:

CREATE ANY INDEX system privilege along with CREATE privilege on the dbspace where the index is being created.
CREATE ANY OBJECT system privilege.