TRUNCATE TEXT INDEX statement

Deletes the data in a MANUAL or an AUTO REFRESH text index.

Syntax

TRUNCATE TEXT INDEX text-index-name 
ON [ owner.]table-name

Use the TRUNCATE TEXT INDEX statement when you want to delete data from a manual text index without dropping the text index definition. For example, if you want to alter the text configuration object for the text index to change the stoplist, you must first truncate the text index, change the text configuration object it refers to, and then refresh the text index to populate it with new data.

You cannot perform a TRUNCATE TEXT INDEX statement on a text index defined as IMMEDIATE REFRESH (the default). For IMMEDIATE REFRESH text indexes, you must drop the index instead.

Permissions

Must be the owner of the table the text index is built on, or have DBA authority, or have ALTER permissions on the table.
The TRUNCATE TEXT INDEX requires exclusive access to the table. This means that any open cursors that reference the table being truncated must be closed, and a COMMIT or ROLLBACK statement must be executed to release the reference to the table.

CREATE TEXT INDEX txt_index_manual ON MarketingInformation ( Description )
   MANUAL REFRESH;
REFRESH TEXT INDEX txt_index_manual ON MarketingInformation;
TRUNCATE TEXT INDEX txt_index_manual ON MarketingInformation;

The truncated text index is repopulated with data the next time it is refreshed.

Discuss this page in DocCommentXchange.