Re: Right Hand Index
- From: Tim Johnston <oraclestuff@xxxxxxxxx>
- To: sinardyxing@xxxxxxxxxxxxx
- Date: Sun, 3 Oct 2004 14:48:30 -0400
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@xxxxxxxxxxxxx> wrote:
> Hi all,
>
> What is "RIGHT-HAND INDEXES" and how to find out that I have such =
> indexing syndrome.
>
> Thanks
>
> Sinardy
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
