Reverse Key Indexes

View previous topic View next topic Go down

Reverse Key Indexes

Post  DilMustafa on Mon Jun 29, 2009 12:27 pm

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

Thanks,

Dil

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Reverse Key Indexes

Post  ngalemmo on Mon Jun 29, 2009 1:29 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Reverse Key Indexes

Post  DilMustafa on Mon Jun 29, 2009 5:28 pm

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

Please, comment afterreading this thread.

Thanks,

Dil

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Reverse Key Indexes

Post  ngalemmo on Mon Jun 29, 2009 7:13 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Reverse Key Indexes

Post  DilMustafa on Mon Jun 29, 2009 7:31 pm

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

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

thanks

Post  frankiben123 on Sat Jul 04, 2009 2:09 pm

thanks...nice post
sales tracking software

frankiben123

Posts : 1
Join date : 2009-07-04

View user profile

Back to top Go down

Re: Reverse Key Indexes

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum