Bidirectional Traversal of Indexes

If you do not specify the ASC or DESC keywords when you create an index on a single column, key values are stored in ascending order by default; but the bidirectional-traversal capability of the database server lets you create just one index on a column and use that index for queries that specify sorting of results in either ascending or descending order of the sort column.

Because of this capability, it does not matter whether you create a single-column index as an ascending or descending index. Whichever storage order you choose for an index, the database server can traverse that index in ascending or descending order when it processes queries.

If you create a composite index on a table, however, the ASC and DESC keywords might be required. For example, if you want to enter a SELECT statement whose ORDER BY clause sorts on multiple columns and sorts each column in a different order, and you want to use an index for this query, you need to create a composite index that corresponds to the ORDER BY columns. For example, suppose that you want to enter the following query:

SELECT stock_num, manu_code, description, unit_price
   FROM stock ORDER BY manu_code ASC, unit_price DESC;

This query sorts first in ascending order by the value of the manu_code column and then in descending order by the value of the unit_price column. To use an index for this query, you need to issue a CREATE INDEX statement that corresponds to the requirements of the ORDER BY clause. For example, you can enter either of the following statements to create the index:

CREATE INDEX stock_idx1 ON stock
   (manu_code ASC, unit_price DESC);
CREATE INDEX stock_idx2 ON stock
   (manu_code DESC, unit_price ASC);

The composite index that was used for this query (stock_idx1 or stock_idx2) cannot be used for queries in which you specify the same sort direction for the two columns in the ORDER BY clause. For example, suppose that you want to enter the following queries:

SELECT stock_num, manu_code, description, unit_price
   FROM stock ORDER BY manu_code ASC, unit_price ASC;
SELECT stock_num, manu_code, description, unit_price
   FROM stock ORDER BY manu_code DESC, unit_price DESC;

If you want to use a composite index to improve the performance of these queries, you need to enter one of the following CREATE INDEX statements. You can use either one of the created indexes (stock_idx3 or stock_idx4) to improve the performance of the preceding queries.

CREATE INDEX stock_idx3 ON stock
   (manu_code ASC, unit_price ASC);
CREATE INDEX stock_idx4 ON stock
   (manu_code DESC, unit_price DESC);

You can create no more than one ascending index and one descending index on a column. Because of the bidirectional-traversal capability of the database server, you only need to create one of the indexes. Creating both would achieve exactly the same results for an ascending or descending sort on the stock_num column.

After INSERT or DELETE operations are performed on an indexed table, the number of index entries can vary within a page, and the number of index pages that a table requires can depend on whether the index specifies ascending or descending order. For some load and DML operations, a descending single-column or multi-column index might cause the database server to allocate more index pages than an ascending index requires.