All data values in a column for which an index is defined, even null values, are incorporated into the index as index key values. However, null-value keys in an index serve no purpose, so their presence in the index is wasteful of space. Therefore, if an entire column in an index is redundant null values, the null value can be specified as an exception key value for the index. Assigning an exception key value to an index has the following benefits:

Expected benefits

1. Because null-value keys are not used in the index, the size of the index is reduced.
2. Overhead for index maintenance (CPU time, number of I/O operations, number of lock requests, frequency of deadlock, etc.) during row insertion, deletion, and updating operations is reduced.
3. If the only retrieval condition for a column of an index that has the null value as an exception key value is IS NULL, that index will not be used. Consequently, retrieval performance would be improved in the following situation:

• When input/output processes have occurred on the same page because data pages were accessed randomly using an index that contained many redundant null values.

Specifying an exception key value

To set an exception key value, you specify the EXCEPT VALUES option in the CREATE INDEX of the definition SQL. For details on index exception key values, see the HiRDB Version 8 Installation and Design Guide.