Indexes with randomized key columns to reduce hot spots

You can randomize the index key columns in database tables where you observe hot spots within the indexes during INSERT, DELETE, or UPDATE processing by using the new RANDOM option on the CREATE INDEX statement.

This option can be used when you want to alter an index to add a key column that is randomized. Note that even though the indexes are stored in random order, it is still possible to get index only access. Also, when the index is in random order it is only usable for equality lookups.

Example: A user defines an index on the EMPNO column of the EMP table in ASCENDING order as follows:

CREATE INDEX DSN8C10.XEMP3                 
                   ON DSN8C10.EMP          
                       (EMPNO    ASC);

The user then continually inserts an ascending sequence of values into the EMPNO column (000010, 000020, 000030, ...). These values are always added to the end of the index, creating a hotspot at the end of the index. In this particular index, the user only wants to look up specific employees by their EMPNO (i.e. only equality predicates are applied to the EMPNO column). To reduce contention, the user changes the definition of the index to the following:

CREATE INDEX DSN8C10.XEMP3                  
                   ON DSN8C10.EMP          
                       (EMPNO    RANDOM);

With the RANDOM specification, the EMPNO values will now be spread randomly throughout the index, preventing the contention that results from always inserting values at the end of the index. Since the common use of this index is for looking up specific employees by their EMPNO, and the user wants to avoid a hotspot at the end, the new RANDOM ordering option provides a good solution.