Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index freelists

Re: Index freelists

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 29 Apr 2004 14:04:10 +0300
Message-ID: <04f701c42dd9$b33c4080$28a423d5@porgand>


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.

> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Apr 29 2004 - 06:01:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US