Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Frank.

Asked: October 26, 2002 - 8:01 pm UTC

Last updated: September 14, 2006 - 9:00 am UTC

Version: 9i.2

Viewed 1000+ times

You Asked

Hi Tom,

From your book, it seems that the reserve key index would only be beneficial in an OPS environment. That implies in a non-OPS environment, it won't help to reduce the skewness of the index tree and therefore it's not applicable.

On the regular B-tree index, I understand when you create an index the b-tree is balanced automatically no matter the index column is sorted or not. However, the index tree will not re-balance itself for the updating, insertion and deletion after the index is created, obviously for performance reason.

Therefore, the index should be disabled/dropped before a massive insertion operation and the index created or recreated after the opetion, not only for the efficiency of the insertion but also for after operations since the insertion may result in an un-balanced index tree.

Please correct me if I am wrong on any of these points.

Thanks,

Frank

and Tom said...

Depends on how you use the index.

The tree does in fact re-balance itself with update/insert/deletes -- that is what the B in B*Tree stands for. The data structure is self balancing.


During a large bulk load -- one might disable an index

o to speed the load
o because the index rebuild can be done in parallel and NOLOGGING instead of serially with lots of log
o and the rebuilt index would be in "prime" form -- as clean as can be.


A reverse key index can be useful in non-ops / non-RAC environments as well. Consider the case where

o you have a table with a primary key ID
o id is populated by an increasing sequence 1, 2, 3, 4, .....
o you delete old records from time to time (not all of them, but some)
o you always either FULL SCAN this table or do a keyed read (where id = :x) and never do range scans (where id between :x and :y)

Here, the index will end up with lots of delete leaf rows over time. By that I mean you inserted 1, 2, 3, 4, 5, .... N and go back later and delete 2, 4, 6, 8 (all of the even numbers). Well, since you will NEVER insert or update a row to have an id that falls between 1 and 3 -- you will NEVER reuse that space released by the number 2 (or 4, or 6 or 8 and so on). So, in this exaggerated case -- the index would end up with lots and lots of empty space over time.

On the other hand, if you used a reverse key index -- there is no reason why the slot used by 2 wouldn't be reused -- because 2 isn't between 1 and 3 anymore -- its just somewhere. The data is getting inserted all over the place in the index structure and the space has a high probability of reuse.

Does that mean you should all run out and use reverse key primary key indexes on tables with ID's populated by sequences? NO (very loudly NO). Fact is -- many tables do not suffer from "deletes" (if you think about it -- deletes are very very rare) and the COALESCE function of the alter index can be quite good at getting this space back.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very good example but it would be based on one condition

Frank, October 27, 2002 - 1:02 pm UTC

Thanks Tom. As always your answer is very helpful. However, the example would be based on one condition--deletions don't shack up the index tree unless a whole block is deleted. What I mean is the deletion of 2, 4, 6, ... from the index will not resulting in an automatic moving of 3,5,7, ... I think this point is mentioned/implied in your book or Lewis.

Would alter index coalesce make the space holes reusable?

Tom Kyte
October 27, 2002 - 2:03 pm UTC

Correct -- I'm using the degenerate case where the deletes are very widespread and don't end up in deleting all entries on the leaf nodes. As long as one entry is there -- that block will stay in that position in the index and if you never insert/update data to be near that entry (near the number "3" for example), it'll be a mostly empty block forever. (my book definitely goes into this - in the myths section on indexes)

Index coalescing:
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/indexes.htm#4443 <code>
is definitely something that would help this particular situation by coalescing adjacent index blocks in the same branches -- freeing up those almost empty blocks for reuse elsewhere in the index structure.

Very useful regarding Reverse Key Index

Saroj, November 09, 2004 - 7:53 am UTC

Hi Tom,

What would be the problem if we will always use reverse key primary key indexes on tables with ID's populated by sequences?

Thanks,
Saroj.


Tom Kyte
November 09, 2004 - 9:21 am UTC

o no range scans

o you lose the 90/10 block split that is there in place just for primary keys associated with indexes.

o you'll start a myth that in Oracle all primary keys populated by sequences MUST be reverse key

o you'll chew up more CPU to reverse the bytes


If they were always to be done one way or the other -- there would be only one way. reverse key indexing is a tool, like a hammer or a drill. Use them when you've identified an issue that would benefit from their use.

Reverse key help

Katherine Harms, December 21, 2004 - 3:20 pm UTC

When I started reading, I really did not understand what "reverse key" meant, and I certainly did not understand why to use it. I was ready to try it just to see what happened. After reading this page, I know that "reverse key" won't help me on any table in my database. That is worth knowing.

how about reverse key for high inserts only

A reader, September 13, 2006 - 5:42 pm UTC

Hello Tom,
we have a system with heavy inserts. The PK is generated by a sequence. We dont do any deletes or updates. The inserts are very high at the rate of 1000/sec.

Should we or not use Reverse Key Index? Please advice/recommend.

Tom Kyte
September 14, 2006 - 9:00 am UTC

the answer is:

</code> http://asktom.oracle.com/Misc/i-believe-strongly-there-are-only-two.html <code>

After reading this page (which basically tells what they do - gives you an understanding of the mechanics) and/or my book (which has a section on them - goes into perhaps more detail then this page) Expert Oracle Database Architecture - you should be able to make an informed judgement call as to whether they will be beneficial to you.... or not.

Thanks

A reader, September 19, 2006 - 9:20 am UTC

Tom,
I have your book, I will read it.

Appreciate your suggestion.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.