Re: Right Hand Index

To expand on this a bit... As Charles mentioned, the "right hand" index syndrome is typically seen on indexes that are based on an ever increasing column... The primary key column that uses a sequence is the perfect example... There are two issues with a right hand index...

The first issue is around space utilization... If the data is always going to the right hand block that means that the right hand block will constantly be filled up and have to be split... Now, assuming a normal index pattern, that right hand block would split into two equal parts... But, since the index is ever increasing, you never again touch the left hand side of the split... That would mean you leave a trail of half full block on the right hand side of the index... Thankfully, Oracle handles this by intelligently splitting the right hand block so that the right hand side of the split is basically empty and the left hand side is basically full... This helps resolve the space issue... It is worth noting that a fairly recent release of Oracle (somewhere in the 8i/9i timeframe) had an issue with this not working correctly... I do not remember the details but I do remember someone (Jonathan Lewis I think) mentioning this...

The second issue is around contention... Now, think about that right hand block... If you have multiple sessions all trying to insert values into the index, that means you're going to have a bunch of sessions lining up to modify the SAME index block... This is where the real problem comes in... These session can easily queue up and that queuing can cause performance issues... AFAIK, this is the real reason that the reverse key index was created... By reversing the index keys you distribute the incoming inserts across the index... This significantly reduces contention issues... However, be careful with reverse key indexes... They may solve one performance issue and create many others... You basically lose the ability to perform index range scans of your index... If your sequentially increasing key values are all over the index, you can not scan a small range of of index blocks to get your answer... Make sense?

HTH
Tim

On Sat, 2 Oct 2004 14:00:06 +0800, Sinardy Xing <sinardyxing_at_bkgcomsvc.com> wrote:
> Hi all,
>
> What is "RIGHT-HAND INDEXES" and how to find out that I have such =
> indexing syndrome.
>
> Thanks
>
> Sinardy
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l