Descending Indexes Quiz (Up On The Ladder) September 8, 2011
Posted by Richard Foote in Descending Indexes, Oracle Indexes, Quiz.trackback
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 ?
Enjoy !!
Richard Foote's Oracle Blog 
Your blog article reminded me of a paraphrase that I put into my notes while reading the book “Expert Oracle One-On-One” several years ago:
I have the answer for #1 and #3, and I am 50% confident that I know the answer to #2 (before testing of course).
By the way, I like your quiz posts.
LikeLike
Hi Charles
Thanks for the quote (and nice comments). I’ve restricted my list of differences to some of the key ones that might be relevant to shall we say more common database versions.
That said, we still have a couple of 8i databases that we’re still looking after on life (if not Oracle) support 🙂
LikeLike
1. Yes. Via the operation INDEX (RANGE SCAN DESCENDING)
2. Yes. Ditto.
3. A descending index is a function-based index. Additional access and filter predicates are added to any query using the descending index (SYS_OP_DESCEND/SYS_OP_UNDESCEND)
I did a quick test – is that cheating?
LikeLike
Hi Flado
No, it’s not cheating at all. In fact, by creating demos for most of the stuff I write about, I actually hope it makes people want to go out and play and test things for themselves.
Did you spot the issue with index space ?
LikeLike
No, but now that you mentioned it, it appears that SYS_OP_DESCEND adds a 0xFF byte to every indexed value.
LikeLike
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.
LikeLike
1. Sure, hence operation index range scan descending.
2. Yes. If you can do a descending range scan on an ascending index to avoid a sort by desc, then you should be able to do a descending range scan on a descending index to avoid a sort by asc.
3. Descending index is really FBI based on SYS_OP_DESCEND.
LikeLike
Hi Dom,
Yes well done !!
Note the possible implications of the FBI and on index space.
LikeLike
Don’t try this without table stats! Or, rather, do — it’s interesting. Of course, the question did specify “by the CBO.”
LikeLike
Hi Joseph
Yes indeed, the question had to be specific to the CBO. However, the issue of not collecting statistics is well noted with using a FBI.
Well done 🙂
LikeLike
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.
LikeLike
Hi Flado
It was kinda interesting in my demo and that was on 10.2.0.4 🙂
The RBO became less of an issue in 11g.
Nice point about determining whether the column is indexed or not.
LikeLike
[…] Richard Foote is yet again busy at testing the brain cells with yet another quiz. […]
LikeLike