Use of ASC & DESC indexes

[itiger]

CREATE INDEX emp_first_name_idx
ON copy_emp(first_name DESC); 
 

Does the following query uses the above created index

SELECT * 
FROM copy_emp
ORDER BY first_name DESC;
 

What are other possibilities (i mean queries) where ASC or DESC indexes can be used.

[Markus Winand]

hi!

You query will most likely not use the index because you select *.

The following query will most likely use the index:

SELECT first_name
  FROM copy_emp
 ORDER BY first_name DESC;

However, it will also use the same query declared with DESC if you query with ASC:

SELECT first_name
  FROM copy_emp
 ORDER BY first_name ASC;

Typically, indexes are not explicitly ASC/DESC. There is only one particular reason to do so, that is if you have mixed ASC/DESC combination in you ORDER BY:

CREATE INDEX t_idx ON t (c1 ASC, c2 desc);

The following two queries might benefit from the index:

SELECT c1, c2
  FROM t
 ORDER BY c1 ASC, c2 DESC;
 
SELECT c1, c2
  FROM t
 ORDER BY c1 DESC, c2 ASC;

The first query can read in index order, the second query against index order. But an index without explicit ASC/DESC could not support those ORDER BY clauses.

However, all of that is only if the optimizer decides that an INDEX FULL SCAN is best for that query. Recent Release often prefer a FULL scan followed by an SORT. By selecting * you will probably get a FULL TABLE SCAN with a SORT.

Covering the ORDER BY clause with the index is most useful in FIRST_ROWS mode or for indexed Top-N queries.

Have a look at my e-Book SQL Performance Explained if you like to learn more about SQL Indexing.