07 November 2006

Reverse Key Indexes

So I started reading the Oracle 10g DBA Handbook because I've deluded myself into thinking that I'll just memorize everything in there. The first chapter seemed to cover very basic stuff that even I had a grasp on, but it didn't take long for me to learn something new. 18 pages in, I read of the magic of reverse key indexes. This is a pretty fundamental index type that I had somehow never heard about before this, so I thought I'd share it here.

A reverse key index does just that, stores the indexed values in reverse, so an id value of 123456 would be stored as 654321. One immediate advantage in doing so is reducing contention for blocks by distributing inserts across all leaf keys of the index. For example, inserting orders 1234, 1235, 1236 and 1237 would normally probably all go into the same block (if space is available), and the writers would wait for each other to finish with it. With a reverse key index, they would be spread to 4321, 5321, 6321 and 7321, which are not close together and probably not on the same node.

For a complete analysis, the link to Jonathan Lewis' FAQ above is quality stuff.

No comments:

Post a Comment