Reverse Index ... Advantages

Hi,

Does any one know what is the benifit of having Reverse Index and how it is used.

Suppose a reverse index is created on Empno, What is the advantage of this instead of the Reguler one.

Thanks

It helps reduce I/O bottlenecks and index sliding. Here is a link:

http://www.oracle.com/pls/use/use_as...ad_asset_form?

HTH,

- Chris

Hi Chris,

Did you see that Link byr the Way.

I did not get any thing except some parameters ... That's it.

Thanks any way.

Doh!

Sorry about that, but I can't seem to get a better link for this one. Best I can tell you is go to OTN and search on 'Indexing Strategies', which will point you right to the doc. You can also search for 'reverse key' or 'index sliding' to get lots more info on the subject.


Basically, reversing the key allows the data to be evenly distributed across the entire index, rather than always adding the data at the end - which is only true or even a concern if you are indexing a current-tyoe DATE field or a field populated via a SEQUENCE (or similar). In such cases, you are always adding to the end of the index, which means the same leaf block, and can cause I/O contention, especially in parallel server, but also in non-parallel mode. Further, such inserting will eventually cause the index to 'slide', or become longer on that side, since that is where all the activity and page splitting is occurring.

The drawback is that you cannot do a ranged-select on a reversed-key index. Well, *you* can, but the optimizer cannot :). You will obviously get the correct answer, just not nearly as quickly. This is not an issue (or should not be) with a sequence-populated key, since this usually is a PK, but can definitely be a concern when dealing with dates.

HTH,

- Chris

Try http://oradoc.photo.net/ora816/serve...schem.htm#3239


It gives info about index types, their use and their advantages.


Adriano.