Random ordering for index columns alleviates index page contention

DB2® Version 10.5 adds a type of random ordering for index key columns. Random ordering on index columns helps to alleviate page contention on frequently accessed pages in certain insert scenarios.

This new function focuses specifically on index leaf pages that are accessed frequently in a DB2 pureScale® environment.

If rows are added to a table in the order of an index, the last leaf page of the index is frequently accessed. The last leaf page is frequently accessed since every key added to the index is inserted into the page. This situation is most common when there is an index on a timestamp, or identity column with increasing keys.

This problem is further exacerbated in a DB2 pureScale environment, where pages are shared between multiple members. In a DB2 pureScale environment, rather than just having latch contention on the page, the page itself must go back and forth to the cluster caching facility. The cluster caching facility then moves the page to the different members that are updating or inserting into it. This situation leads to a drastic drop in throughput.

When you specify the RANDOM option, values are stored at random places in the index tree. As a result, the number of consecutive insertions on a page decreases. Which in turn, alleviates the issue of page contention. You can use a randomly ordered index to run equality lookups on a specified column. In addition, key columns that are in random order can be used in nonmatching index scans, and index-only access on random key columns is possible. Even though values are stored in random order, you can retrieve the original value of the random key column.