Queries that use a mix of ascending and descending order in an order by clause do not perform a separate sort step if the index was created using the same mix of ascending and descending order as that specified in the order by clause, or if the index order is the reverse of the order specified in the order by clause. Indexes are scanned forward or backward, following the page chain pointers at the leaf level of the index.
For example, this command creates an index on the titles table with pub_id ascending and pubdate descending:
create index pub_ix
on titles (pub_id asc, pubdate desc)
The rows are ordered on the pages as shown in Figure 5-4. When the ascending and descending order in the query matches the index creation order, the result is a forward scan, starting at the beginning of the index or at the first qualifying row, returning the rows in order from each page, and following the next-page pointers to read subsequent pages.
If the ordering in the query is the exact opposite of the index creation order, the result is a backward scan, starting at the last page of the index or the page containing the last qualifying row, returning rows in backward order from each page, and following previous page pointers.
Figure 5-4: Forward and backward scans on an index
The following query using the index shown in Figure 5-4 performs a forward scan:
select * from titles order by pub_id asc, pubdate desc
This query using the index shown in Figure 5-4 performs a backward scan:
select * from titles order by pub_id desc, pubdate asc
For the following two queries on the same table, the plan requires a sort step, since the order by clauses do not match the ordering specified for the index:
select * from titles order by pub_id desc, pubdate desc
select * from titles order by pub_id asc, pubdate asc
Parallel sort operations are optimized very differently
for partitioned tables. See Chapter 9, “Parallel Sorting,” for more information.
