Reverse Key Indexes

Do you think Reverse Key Indexes on Surrogate Keys in Dimension tables are a good practise.

Thanks,

Dil

What do you mean by 'reverse key' indexes? An inverted list or a key where the order of characters/digits are reversed?

In either case, I would say no. An inverted list is only helpful where there are duplicate keys and as for the latter, it wouldn't make any difference.

http://oracletoday.blogspot.com/2006/09/there-is-option-to-create-index.html

Please, comment afterreading this thread.

Thanks,

Dil

Ok, its the latter...

I don't think it will make much difference for a dimension table. The article talks about the advantages when there are heavy inserts or deletes coming from multiple concurrent processes, but is that usually an issue with a dimension table? If it is, go ahead, it certainly wouldn't hurt, but it would be an unusual situation.

Normally, you would never delete a dimension row and inserts usually come from a single process. With a single process inserting new rows, normal sequential keys would be an advantage because they are in the same block...the load can take full advantage of the locked buffer it has, which is the opposite of what reversing the keys would accomplish.

With fact tables there are a host of other issues with loading and index maintenance that I don't think reversing the keys would make much difference.

So, is it a 'best practice'? No, not in the general sense. Is it a resonable thing to do? Sure, if you have a situation that warrants it.

Thanks for commenting. I am considering RKI for dimension SK's. Dims are loaded by multiple processes in RAC envoirnment.

thanks...nice post