Fantastic, much appreciated guys. RKIs was an area that i never really questioned before but having to look a bit closer now that the PT exam is coming up, i decided it was time to clear the doubts.

I assume (hope i don't become as ass for doing so) that reversing the key also means that the index will be less deeply nested (fewer levels) than if it were an ordinary B-tree index (when using sequence generated keys).

Jmodic....
"No, the index serach in this case will actually be a bit slower compared to normal btree index (the difference will be minimal though). The actual index tree traversing is identical in both cases, but there is a tiny overhead of CPU time with RK indexes as Oracle has first to reverse the order of bytes of the searched values to make it the same as it is stored in the index."

Believe it or not was not aware of this!

Jmodic & Slimdave....
Their main (and only?) purpose is to avoid "hot index blocks" when inserting rows with sequence-generated key values. So they were not invented to speed-up queries but to speed-up inserts....

This makes complete sense!


Slimdave...
The only disadvantage is that you lose the ability to range-scan, but that's generally not an issue with synthetic PK's, and you can still do a fast full index scan if need be.

Do you mean that range scans can be done with RKIs but will be very inefficent.

Beats me as to why the certification books (i am using Sybex) dont give these reasons as to why one would use RKIs. It would make things alot easier to understand on first meeting!


Much Appreciated
Gus