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.
Reply With Quote
Bookmarks