Index Modes (IDS)

Use the index mode options to specify the behavior of the index during insert, delete, and update operations.

Index Modes:
 
   .-ENABLED----------------------.
|--+-DISABLED---------------------+-----------------------------|
   |            .-WITHOUT ERROR-. |
   '-FILTERING--+-WITH ERROR----+-'
 

The following table explains the index modes

Mode

Effect

DISABLED

The database server does not update the index after insert, delete, and update operations that modify the base table. The optimizer does not use the index during the execution of queries.

ENABLED

The database server updates the index after insert, delete, and update operations that modify the base table. The optimizer uses the index during query execution. If an insert or update operation causes a duplicate key value to be added to a unique index, the statement fails.

FILTERING

The database server updates a unique index after insert, delete, and update operations that modify the base table. (This option is not available with duplicate indexes.)

The optimizer uses the index during query execution. If an insert or update operation causes a duplicate key value to be added to a unique index in filtering mode, the statement continues processing, but the bad row is written to the violations table associated with the base table. Diagnostic information about the unique-index violation is written to the diagnostics table associated with the base table.

If you specify filtering for a unique index, you can also specify one of the following error options.

Error Option

Effect

WITHOUT ERROR

A unique-index violation during an insert or update operation returns no integrity-violation error to the user.

WITH ERROR

Any unique-index violation during an insert or update operation returns an integrity-violation error to the user.