SET INDEXES statement

Use the SET INDEXES statement to enable or disable a user-defined index, or to change the filtering mode of a unique index.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Notes:

¹ Unique indexes only
² See Filtering Modes


Element	Description	Restrictions	Syntax
index	Index to be enabled, disabled, or changed in its filtering mode	Must exist	Identifier
table	Table whose indexes are all to be enabled, disabled, or changed in their filtering mode	Must exist	Identifier

Usage

You can use this statement to enable or disable a specific index or a list of indexes. You can also use the table option to enable or disable all of the user-defined indexes on a table without specifying their individual identifiers. For example, the next two examples respectively disable and enable all of the indexes on the cust_calls table:

 SET INDEXES FOR cust_calls DISABLED;
 SET INDEXES FOR cust_calls ENABLED;

This simple syntax can be convenient in operations where you intend to LOAD or TRUNCATE all the data in a table, or to consolidate the free space in a table.

Explicitly-defined and implicitly-defined indexes

The SET INDEXES statement operates on indexes that the CREATE INDEX statement created explicitly. It is not useful, however, with system-defined indexes that PRIMARY KEY or FOREIGN KEY constraint definitions create implicitly. The SET INDEXES statement cannot specify system-generated names that begin with the blank (ASCII 32) character, even if your database has the DELIMIDENT environment variable set to support double quotation marks as delimiters for database object identifiers.

To enable or disable implicitly-defined indexes, use instead the SET CONSTRAINTS statement, whose FOR table option can reference system-generated constraints implicitly, as in the following examples:

 SET CONSTRAINTS FOR cust_calls DISABLED;
 SET CONSTRAINTS FOR cust_calls ENABLED;

To disable all the explicitly-defined and implicitly-defined indexes of a table, use the FOR table options of both the SET INDEXES and SET CONSTRAINTS statements, as in the following examples:

 SET INDEXES FOR cust_calls DISABLED;
 SET CONSTRAINTS FOR cust_calls DISABLED;

You can similarly enable all the explicitly-defined and implicitly-defined indexes of a table, without referencing the system-generated names of the implicitly-defined indexes, by substituting ENABLED for DISABLED in the examples above.

The SET INDEXES statement is a special case of the SET Database Object Mode statement. The SET Database Object Mode statement can also enable or disable a trigger or constraint, or can change the filtering mode of constraints and unique indexes.

For the complete syntax and semantics of the SET INDEXES statement, see SET Database Object Mode statement.

Do not confuse the SET INDEXES statement with the SET INDEX statement, which was supported in releases earlier than Version 9.40. The HCL OneDB™ database server ignores the SET INDEX statement in current releases.

Restrictions on Secondary Servers

In cluster environments, the SET INDEXES statement is not supported on updatable secondary servers. (More generally, session-level index, trigger, and constraint modes that the SET Database Object Mode statement specifies are not redirected for UPDATE operations on table objects in databases of secondary servers.)