From oracle-l-bounce@freelists.org Sun Oct 3 13:44:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i93Ii1T06105 for ; Sun, 3 Oct 2004 13:44:01 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i93Ii1I06100 for ; Sun, 3 Oct 2004 13:44:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BBB3B72C3A5; Sun, 3 Oct 2004 13:50:05 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02086-97; Sun, 3 Oct 2004 13:50:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 318E372C1F6; Sun, 3 Oct 2004 13:50:05 -0500 (EST) Message-ID: <69a371a7041003114821157f3d@mail.gmail.com> Date: Sun, 3 Oct 2004 14:48:30 -0400 From: Tim Johnston To: sinardyxing@bkgcomsvc.com Subject: Re: Right Hand Index Cc: oracle-l@freelists.org In-Reply-To: <7534CC34CD1D9F49BB05A00B9C6F20F4016BC732@antares.bcsgroup.com.sg> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <7534CC34CD1D9F49BB05A00B9C6F20F4016BC732@antares.bcsgroup.com.sg> X-archive-position: 10617 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: oraclestuff@gmail.com Precedence: normal Reply-To: oraclestuff@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 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