SET INDEXES statement

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

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.

VISIBLE and INVISIBLE options are used to make a user-created index on a table visible or invisible. An index that is marked invisible, is maintained but ignored by the optimizer. By default a newly created index is VISIBLE.

As shown in the example given below, three indexes idx1, idx2 and idx3 are created on tab1. idx1 and idx2 will be created VISIBLE. Although idx1 is created without the VISIBLE keyword, since a newly created index is visible by default, idx1 will be created visible. idx3 will be created INVISIBLE.


CREATE INDEX idx1 on tab1(col1);
CREATE INDEX idx2 on tab1(col2) VISIBLE;
CREATE INDEX idx3 on tab1(col3) INVISIBLE;

Using SET INDEXES, idx1 and idx2 will be made INVISIBLE and idx3 will be made visible as shown in this example:

SET INDEXES idx1 INVISIBLE;
SET INDEXES idx2 INVISIBLE;
SET INDEXES idx3 VISIBLE;

Using the table option, you can make all of the user-defined indexes on a table visible/invisible without specifying their individual identifiers. e.g. all the indexes of tab1 can be made visible as shown in this example:

SET INDEXES FOR tab1 VISIBLE;

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 Informix® 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.)