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. For more information about the options for this clause, see CREATE TEXT INDEX statement.

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.

To view text indexes and the text configuration objects they refer to, see View text indexes in the database.

Permissions

Must be the owner of the underlying table, or have DBA authority, or have REFERENCES permission.

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

Side effects

Automatic commit

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

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 MarketingInformation ( Description ) 
   MANUAL REFRESH;
ALTER TEXT INDEX txt_index_manual ON MarketingInformation 
   AUTO REFRESH EVERY 24 HOURS;
ALTER TEXT INDEX txt_index_manual ON MarketingInformation 
   RENAME AS txt_index_daily;