TEXT Indexes

This chapter describes how to create and drop TEXT indexes and explains relevant objects and constraints.

Tibero TEXT improves the performance of queries for text data. It provides the CTXCAT index to rapidly process search queries for columns that include relatively simple information, such as book titles and product names, and the CONTEXT index to index a large volume of data such as text of books.

CTXCAT Indexes

The following describes how to create and drop CTXCAT indexes and explains relevant objects and constraints.

Creating Indexes

Create CTXCAT indexes as follows:

Usage

CREATE INDEX idx_name on [sch_name.]tbl_name (col_name) CTXCATINDEX 
[PARAMETERS('preferences_class_name user_defined_preference_name')]

Item	Description
idx_name	CTXCAT index name to create.
sch_name	Owner of the table for the index.
tbl_name	Name of the table for the index.
col_name	Name of a VARCHAR2 column for the index.
CTXCATINDEX	Reserve word that indicates that a CTXCAT index is created.
parameters	Reserve word that specifies the configuration used when creating a CTXCAT index.
preferences_class_name	Class that specifies the configuration used when creating a CTXCAT index.
user_defined_preference_name	Preference created for the class that was specified in preferences_class_name.

Note

For detailed information about index configuration preferences and classes, please refer to “TEXT Indexing Elements”.

Example

SQL>create table t(name varchar(400)); 

Table 'T' created.

SQL>create index t_idx on t(name) ctxcatindex;

Index 'T_IDX' created.

Caution

Do not set a table name to 'TEXT'. When executing TEXT index related queries, 'TEXT' is used internally as a package name.

Dropping Indexes

Drop TEXT indexes as follows. The way is the same as for other indexes.

Usage

DROP INDEX index_name

Item	Description
index_name	TEXT index name to drop.

Example

SQL>drop index t_idx;

Index 'T_IDX' dropped.

Index-related Objects

The following objects are created when a CTXCAT index is created.

Name	Type	Description
DR$index_name$I	Table	Table that saves column text tokens.
DR$index_name$R	Index	ROWID index for a token table.
DR$index_name$X	Index	Index for the token and ROWID columns of a token table.
DR$index_name_TRG_I	Trigger	Trigger used to insert input data in a token table when the data is inserted in the column for which the CTXCAT index is used.
DR$index_name_TRG_D	Trigger	Trigger used to delete a corresponding token when data in the column for which the CTXCAT index is used is deleted.
DR$index_name_TRG_U	Trigger	Trigger used to update a corresponding token when data in the column for which the CTXCAT index is used is updated.

Index Constraints

CTXCAT indexes have the following constraints.

● A single CTXCAT index cannot be used for multiple columns.

● TEXT indexes cannot be partitioned.

● TEXT indexes can only be created for VARCHAR2 columns.

---

CONTEXT Indexes

The following describes how to create and drop CONTEXT indexes and explains relevant objects and constraints.

Creating Indexes

Create the CONTEXT index as follows:

Usage

CREATE INDEX idx_name on [sch_name.]tbl_name INDEXTYPE IS CTXSYS.CONTEXT 
parameters('preferences_class_name user_defined_preference_name sync(option)')

Item	Description
idx_name	CONTEXT index name to create.
sch_name	Owner of the table for the index.
tbl_name	Name of the table for the index.
CTXSYS.CONTEXT	Reserve word that indicates that a CONTEXT index is created.
parameters	Reserve word that specifies the configuration used when creating a CONTEXT index.
preferences_class_name	Class that specifies the configuration used when creating a CONTEXT index.
user_defined_preference_name	Preference created for the class that was specified in preferences_class_name.
sync(option)	Used to automatically synchronize CONTEXT indexes. One of the following can be set. – ON COMMIT: synchronize indexes after commit. The commit completes after the synchronization completes. – EVERY: synchronize indexes at the interval set in interval-string. The privilege of creating jobs is required.

Note

For detailed information about index configuration preferences and classes, please refer to "TEXT Indexing Elements”.

Example

SQL>create table t(name varchar(400), value varchar(1000)); 

Table 'T' created.

SQL>create index t_idx on t(name) indextype is ctxsys.context 
parameters('sync(on commit)');

Index 'T_IDX' created.

SQL>create index t_idx2 on t(value) indextype is ctxsys.context 
parameters('sync(every sysdate+1/24/60)');

Index 'T_IDX2' created.

Caution

Do not set a table name to 'TEXT'. When executing TEXT index related queries, 'TEXT' is used internally as a package name.

Dropping Indexes

Drop TEXT indexes as follows. The way is the same as for other indexes.

Usage

DROP INDEX index_name

Item	Description
index_name	TEXT index name to drop.

Example

SQL>drop index t_idx;

Index 'T_IDX' dropped.

Index-related Objects

The following objects are created when a CONTEXT index is created.

Name	Type	Description
DR$index_name$I	Table	Table that saves column text tokens.
DR$index_name$X	Index	Index for the token and ROWID columns of a token table..
DR$index_name$K	Table	Table that saves document IDs of a token table.

Index Constraints

CONTEXT indexes have the following constraints.

• A single CONTEXT index cannot be used for multiple columns.

• CONTEXT indexes cannot be global partitioned.

• When BASIC_LEXER is used, CONTEXT indexes can only be created for VARCHAR2 and CLOB columns.

• When JAPANESE_LEXER is used, CONTEXT indexes can only be created for VARCHAR2 columns.