Re: Index fragmentation and Performance.

It depends what you mean by index fragmentation.

If you mean there are block below the high water mark which have become empty, or that the index is made of a large number of small extents which are scattered all over the tablespace, then the performance of a single traverse to find some rows will not be adversely affected. When normal index access takes place, the index is read one block at a time, so the absolute location of such blocks is immaterial.

There is a new 'index fast full scan' optimiser option in 7.3.4 (-ish) which simply scans the entire index segment in order of physical block, discarding branch blocks, and selecting leaf values as it goes: this path would be affected by this type of fragmentation.

The structure of the index is:

    Root block:

        contains a list of pointers to branch blocks,
        each pointer has a partial key value to indicate
        the first key value to be found in that branch

    Branch block
        contains a list of pointers to branch blocks,
        each pointer has a partial key value to indicate
        the first key value to be found in that branch
    OR
        contains a list of pointers to leaf blocks,
        each pointer has a partial key value to indicate
        the first key value to be found in that leaf

    Leaf block
        contains a list of key values with rowid of
        the relevant row.
        Holds a pointer to the next sequential leaf
        in the index, and a pointer to the previous
        sequential leaf in the index.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Devinder Pal Singh wrote in message
<3704F2BC.3C60F439_at_pharma.novartis.com>...
>Dear all,
>I read some where that index fragmentation does not lead to performance
>decrease though it leads to wastage of space. Can somebody explain how
>index blocks are read. ie
>Is index block directory stored in the index header ? and oracle reads
>only the header of the index to go to the desired block or something
>else happens. The only thing I could think of is that since indexes are
>b-trees and this b-tree is stored in the index header so oracle can find
>which block to visit so even if some blocks are empty in the index
>segment, oracle need not visit those blocks as in full table scan.. All
>thoughts are welcome.
>Regards
>
>
Received on Fri Apr 02 1999 - 10:54:50 CST