Viewing text index terms and settings (SQL)

View text index terms and settings in Interactive SQL.

Prerequisites

To view settings and statistical information about a text index, you must have one of the following system privileges:

MANAGE ANY STATISTICS
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE ANY OBJECT
ALTER ANY OBJECT
DROP ANY OBJECT

To view terms for a text index, you must also have one of the following privileges:

SELECT privilege on the table or materialized view
SELECT ANY TABLE system privilege

The text index must be initialized.

Task

Connect to the database.
Run the sa_text_index_stats system procedure to view statistical information about a text index:
CALL sa_text_index_stats( );
Run the sa_text_index_vocab system procedure to view terms for a text index:
CALL sa_text_index_vocab( );

Results

The statistical information and terms for the text index is displayed.

Next

When a text index is created, the current database options are stored with the text index. To retrieve the option settings used during text index creation, execute the following statement:

SELECT b.object_id, b.table_name, a.option_id, c.option_name, a.option_value
FROM SYSMVOPTION a, SYSTAB b, SYSMVOPTIONNAME c
WHERE a.view_object_id=b.object_id
AND b.table_type=5;

A table_type of 5 in the SYSTAB view is a text index.

Viewing text index terms and settings (SQL)

Task

See also