Enhancing ORDER BY Query Performance

Using multicolumn HG indexes can enhance the performance of ORDER BY queries.

You can use multicolumn HG indexes to enhance the performance of ORDER BY queries with reference to multiple columns in a single table query. This change is transparent to users, but improves query performance.

Queries with multiple columns in the ORDER BY clause may run faster using multicolumn HG indexes. For example, if the user has multicolumn index HG(x,y,z) on table T, then this index is used for ordered projection:

SELECT abs (x) FROM T
ORDER BY x, y

In the above example, the HG index vertically projects x and y in sorted order.

If the ROWID() function is in the SELECT list expressions, multicolumn HG indexes are also used. For example:

SELECT rowid()+x, z FROM T
ORDER BY x,y,z

If ROWID() is present at the end of an ORDER BY list, and if the columns of that list—except for ROWID()— exist within the index, and the ordering keys match the leading HG columns in order, multicolumn indexes are used for the query. For example:

SELECT z,y FROM T
ORDER BY x,y,z,ROWID()