ALTER TEXT INDEX statement

Alters the definition of a text index.

Syntax

ALTER TEXT INDEX [ owner.]text-index-name 
ON [ owner.]table-name 
alter-clause

alter-clause : 
rename-object 
| refresh-alteration

rename-object :
 RENAME { AS | TO } new-name

refresh-alteration : 
{ MANUAL REFRESH 
| AUTO REFRESH [ EVERY integer { MINUTES | HOURS } ] }

Parameters

RENAME clause
Use the RENAME clause to rename the text index.
REFRESH clause
Specify the REFRESH clause to set the refresh type for the text index.

Remarks

Once a text index is created, you cannot change it to, or from, IMMEDIATE REFRESH. If either of these changes is required, you must drop and recreate the text index.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

You can only alter a text index built on a materialized view by renaming it. You cannot change the refresh type for a text index built on a materialized view.

Privileges

To alter a text index on a table, you must be the owner of the table, or have one of the following privileges:

REFERENCES privilege on the table
ALTER ANY INDEX system privilege
ALTER ANY OBJECT system privilege

To alter a text index on a materialized view, you must be the owner of the materialized, or have one of the following privileges:

ALTER ANY INDEX system privilege
ALTER ANY OBJECT system privilege

Side effects

Automatic commit

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The first statement creates a text index, txt_index_manual, defining it as MANUAL REFRESH. The second statement alters the text index to refresh automatically every day. The third statement renames the text index to txt_index_daily.

CREATE TEXT INDEX txt_index_manual ON GROUPO.MarketingInformation ( Description ) 
   MANUAL REFRESH;
ALTER TEXT INDEX txt_index_manual ON GROUPO.MarketingInformation 
   AUTO REFRESH EVERY 24 HOURS;
ALTER TEXT INDEX txt_index_manual ON GROUPO.MarketingInformation 
   RENAME AS txt_index_daily;