Re: Index freelists
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2004 14:04:10 +0300
Hi!
> Any advice on having more than one freelist for an index? I have looked
into
> freelists for tables, but I cannot find anything about indexes.
With indexes, the story is more complicated since you can't just insert a
row into any free block available like with tables. Multiple freelists with
tables help us to spread up inserts to different datablocks, since every
freelist has its distinct set of datablocks in it. With indexes, the
inserted key has to go exactly to the block where the structure of b?tree
index dictates, so multiple freelists can't help to spread contention here.
When any of the index blocks has to split, a new block has to be allocated
from the freelist (and possibly unlinked from previous location in index),
causing an update to freelist entry in segment header block. Now if you had
defined multiple freelists for your segment, they'd still remain in the
single segment header block and if you'd have several simultaneous block
splits, the segment header would become the bottleneck.
You could relieve this by having multiple freelist groups (spreading up
freelists into multiple blocks after segment header), but this approach has
it's problems as well - like a server process which maps to freelist group 1
doesn't see free blocks in freelist group 2, thus possibly wasting space in
some cases...
So, if you have huge contention on regular index blocks, then you should
rethink the design (avoid right hand indexes for example), or physical
design (partition the index), increasing freelists won't help here.
But if you have contention on index segment's header block because of block
splits/freelist operations, then either partition the index or have multiple
freelist groups, adding freelists again won't help here. Note that adding
freelist groups require segment rebuild.
Tanel.
----- Original Message -----
From: "Hitchman, Peter" <Peter.Hitchman@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, April 29, 2004 1:08 PM
Subject: Index freelists
> Hi,
>
> Thanks
>
> Pete
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
