How indexes can help to avoid sorts
Db2 can use indexes to avoid sorts when processing queries with the ORDER BY clause.
When a query contains an ORDER BY clause, Db2 looks for indexes that satisfy the order in the query. For Db2 to be able to use an index to access ordered data, you must define an index on the same columns as specified in the ORDER BY clause.
- Forward index scan
- For Db2 to use a forward index scan, the ordering must be exactly the same as in the ORDER BY clause.
- Backward index scan
- For Db2 to use a backward index scan, the ordering must be exactly the opposite of what is requested in the ORDER BY clause.
In addition to forward and backward scans, you have the option to create indexes with a pseudo-random order. This ordering option is useful when ascending insertions or hotspots cause contention within the indexes. Indexes created with the RANDOM option do not support range scans. They do support equality lookups.
Examples
- Example 1
- For example, if you define an index by specifying DATE DESC, TIME ASC as the column names and order, Db2 can use this same index for both of the following ORDER BY clauses:
- Forward scan for ORDER BY DATE DESC, TIME ASC
- Backward scan for ORDER BY DATE ASC, TIME DESC
You do not need to create two indexes for the two ORDER BY clauses. Db2 can use the same index for both forward index scan and backward index scan.
- Example 2
- Suppose that the query includes a WHERE clause with a predicate of the form COL=constant. For example:
... WHERE CODE = 'A' ORDER BY CODE, DATE DESC, TIME ASCDb2 can use any of the following index keys to satisfy the ordering:
- CODE, DATE DESC, TIME ASC
- CODE, DATE ASC, TIME DESC
- DATE DESC, TIME ASC
- DATE ASC, TIME DESC
Db2 can ignore the CODE column in the ORDER BY clause and the index because the value of the CODE column in the result table of the query has no effect on the order of the data. If the CODE column is included, it can be in any position in the ORDER BY clause and in the index.
