Descending Indexes Quiz (Up On The Ladder) September 8, 2011

OK, you won’t find the answer to these questions on my blog, so using my search facility won’t be of any help :)

Actually, it’s quite an easy one this, honest ;)

If you have a query such as:

SELECT * FROM bowie WHERE id BETWEEN 42 and 84 ORDER BY id DESC;

1) Can a default B-Tree index on the single ID column that stores data in Ascending order be used by the CBO to retrieve the data automatically in the required Descending order, without the need for a sort operation ?

2) Can a Descending B-Tree index on the ID column be used by the CBO to retrieve the data automatically in Ascending order, without the need for a sort operation ?

3) Depending on your answers above, what are the differences (if any) between the implementation of an Ascending and Descending index ?

And another difference – although of a kind you probably didn’t have in mind – it becomes impossible to tell using SQL only whether the column is indexed or not (user_ind_expressions.column_expression being of type LONG). That’s a grudge I hold against descending indexes since the time Oracle 8i was the newest version.

Not that interesting in 10.2 and above, actually. Unless I specifically request the RBO, I always get the CBO, stats or no stats.

Mildly interesting is that apparently with the RBO (when forced using a hint) the answer to #1 would be “no”, as well as that the INDEX (RANGE SCAN) in case 1 is replaced by a TABLE ACCESS (FULL) in case 2. RBO sorts in both cases.