14.2.5 Using an index with an exceptional key value set

When an index is defined for a column, all the data in the column is loaded into the index as the index values. Sometimes an index will contain unused values, such as the null value. In this case, the null value can be specified as an exceptional key value so that its occurrences will be excluded from the index. This is appropriate for an index that contains many occurrences of the null value in all its component columns.

Organization of this subsection
(1) Effects of setting an exceptional key value for an index
(2) Setting procedure
(3) Notes

(1) Effects of setting an exceptional key value for an index

The following are the effects of setting an exceptional key value for an index:

  1. The size of the index is reduced because the null value key is not created in the index.
  2. Overhead for index maintenance during row insertion, deletion, and update processing (CPU time, number of input/output operations, number of lock requests, and frequency of deadlock) is reduced, in addition to the amount of log information being reduced.
  3. When the null value is specified as the exceptional key value and IS NULL, VALUE, or CASE expression is specified as the search condition for the index component column, then the index is not used for the retrieval processing. As a result, the retrieval performance is improved in the following case:
    • Input/output operations occur on the same page because the index contains many occurrences of the null value and the data page is accessed at random.

(2) Setting procedure

An exception value is set by specifying EXCEPT VALUES in the CREATE INDEX definition SQL.

(3) Notes