Browse Prior Art Database

Method of Using an Index in the Opposite Order to satisfy a Sort Order

IP.com Disclosure Number: IPCOM000031863D
Original Publication Date: 2004-Oct-14
Included in the Prior Art Database: 2004-Oct-14
Document File: 2 page(s) / 31K

Publishing Venue

IBM

Abstract

Method of Using an Index in the Opposite Order to satisfy a Sort Order

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 53% of the total text.

Page 1 of 2

Method of Using an Index in the Opposite Order to satisfy a Sort Order

SQL queries often require descending sort order on key values which forces the database management systems to sort the rows possibly in the exact opposite order in which they were retrieved via an index. Depending on the number of rows in the result table, the sort can be CPU intensive and also require disk storage for sort work files. Many customers create 2 indexes, at the expense of maintaining two indexes during insert, update, delete and utility operations, just to get better response for queries with descending sort order. This problem is ameliorated by eliminating the sort by accessing the data using the index in the backward order to satisfy the descending index. This method takes advantage of the backward index scan capability and backward sequential detect capability to avoid sort and improve performance and reduce cost.

The database management systems optimizer will select and ascending index and use a backward scan to avoid sort. The reverse can also be true. That is, a descending index will be selected to avoid the sort and the descending index is scanned backwards to provide an ascending sort order.

Example:

Consider the two statements:

SELECT STATUS_DATE, STATUS

FROM ACCOUNT_STAT

WHERE ACCT_NUM = :HV
ORDER BY STATUS_DATE DESC, STATUS_TIME DESC;

and

SELECT STATUS_DATE, STATUS

FROM ACCOUNT_STAT

WHERE ACCT_NUM = :HV
ORDER BY STATUS_DATE ASC, STATUS_TIME ASC;

If the index on table ACCT_STAT is ACCT_NUM, STATUS_DATE, STATUS_TIME, where all columns are in ascending order, both statements will be able to use the same index.

To be able to use backward index scan to avoid sort, the index must be defined on the same columns as the ORDER BY and the ordering must be exactly opposite of what is req...