Home » SQL & PL/SQL » SQL & PL/SQL » reverse key (oracle9i)
reverse key [message #315794] Tue, 22 April 2008 15:43 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
All, One question about reverse key index. When we use reverse key index, my understanding here is, oracle reverse the index field and stored in the index block. The purpose of the reverse key index is, to avoid the contention in the index block. But even we reverse, the reversed data will be stored in the same index block. where are we avoiding the contention?? I hope, i am not confusing any one ....

Regards
Govind
Re: reverse key [message #315801 is a reply to message #315794] Tue, 22 April 2008 16:32 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
the reversed data will be stored in the same index block
Wrong, what makes you think this?

By the way, what has this got to do with PL/SQL, (and it's not really an expert question either). Please take more care over which forum you choose to post in.

[Updated on: Tue, 22 April 2008 16:33]

Report message to a moderator

Re: reverse key [message #315822 is a reply to message #315801] Tue, 22 April 2008 21:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Say that you are allocating keys from a sequence. Two people allocate the next two keys: 34567 and 34568. I think the reversal is byte-wise, not digit-wise, but lets pretend for simplicity.

After reversal, these two become 76543 and 86543 respectively.

76543 gets placed in between 76542 and 76544, which represent 24567 (allocated 10,000 rows earlier) and 44567 (will not be allocated for another 10000 rows).

Can you see how sequentially allocated numbers will end up in different blocks?

Ross Leishman
Re: reverse key [message #315951 is a reply to message #315822] Wed, 23 April 2008 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect the OP is getting mixed up between which block the index key ends up in, and which block the row ends up in.

As I understand it, the purpose of Reverse Key indexes is to reduce index contention (as ably explained by @rleishman). The existance of a reverse key index will not change the allocation of the record data on bit.
Re: reverse key [message #316025 is a reply to message #315822] Wed, 23 April 2008 09:06 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Dear rleishman, your reply makes more sense to me and it makes me to understand. Thanks again
Re: reverse key [message #316070 is a reply to message #315822] Wed, 23 April 2008 11:41 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Ross Leishman, One question.. Now i do understand that, Oracle optimizer will not use reverse key index for range scan..

I am taking your example here..

If we fire a query select * from table1 where col1=34567
Does oracle convert 34567 to 76543 when it refer the index segement?? Just to confirm this... Thanks again.
Re: reverse key [message #316144 is a reply to message #316070] Wed, 23 April 2008 22:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, Oracle does the reversal for you when it accesses the index.
Re: reverse key [message #316238 is a reply to message #316144] Thu, 24 April 2008 06:14 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank Ross. I appreciate your response.
Previous Topic: Error in DBMS_SCHEDULER.CREATE_JOB
Next Topic: SEQEUNCE
Goto Forum:
  


Current Time: Fri Dec 09 15:29:10 CST 2016

Total time taken to generate the page: 0.23742 seconds