jump to navigation

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 !!

About these ads

Comments»

1. Charles Hooper - September 8, 2011

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:

Unless the COMPATIBLE init.ora parameter is set to 8.1.0 or higher, the DESC option will be ignored when creating indexes.

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.

Richard Foote - September 9, 2011

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 :)

2. Flado - September 8, 2011

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?

Richard Foote - September 9, 2011

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 ?

Flado - September 9, 2011

No, but now that you mentioned it, it appears that SYS_OP_DESCEND adds a 0xFF byte to every indexed value.

Flado - September 9, 2011

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.

3. Dom Brooks - September 8, 2011

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.

Richard Foote - September 9, 2011

Hi Dom,

Yes well done !!

Note the possible implications of the FBI and on index space.

4. Joseph Thvedt - September 9, 2011

Don’t try this without table stats! Or, rather, do — it’s interesting. Of course, the question did specify “by the CBO.”

Richard Foote - September 9, 2011

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 :)

Flado - September 9, 2011

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.

Richard Foote - September 10, 2011

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.

5. Log Buffer #237, A Carnival of the Vanities for DBAs | The Pythian Blog - September 9, 2011

[...] Richard Foote is yet again busy at testing the brain cells with yet another quiz. [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,900 other followers

%d bloggers like this: