Re: How to interpret INDEX_STATS

Chuck:

68% is a bit below the 'nominal average' of 75% for a b-tree. The decision as to whether it NEEDS a re-org is largely application dependent, but may be affected by the ease of scheduling the re-org, and the availability of free space.

The usage could be this way simply because of the timing of the arrival of the data. e.g. assume I have blocks with can take 100 keys.

If I insert 100 rows with key = A, then 100 with B, the 100 with C then 100 with D
I use 4 consecutive blocks at the leaf level, and have 100% usage.

If I insert 75 A, 75B, 75C, then 75D, I use 3 consecutive blocks. Watch closely for the next bit as I insert the next 25A, 25B, 25C, 25D

We start with:

    Block 1 Block 2 Block 3
    75A 25B 50B 50C 25C 75D

As the 25 extra As
Block 1 Splits, and the new rows are inserted between the As and Bs (this is a side-effect of a non-unique key including the rowid).

    Block 1A Block 1B
    50A 25 oldA 25 newA 25B

As the 25 Bs go in, they go into Block 2, not into the spare space in block 1b (same side effect) splitting the block to

    Block2a                    Block 2b
    50 old B 25 new B        50 old C

As the 25 Cs go in, they go into Block 3, again missing the 'spare space' this time in block 2B

    Block 3a                                    Block 3b
   25 old C 25 new C   25 old D         50 old D 25 new D

Finally the last 25 Ds go in to block 3b - leaving:

Block 1a        50A
Block 1b        50A 25B
Block 2a        75B 50C
Block 2b        50C
Block 3a        50C 25D
Block 3b        75 D

And a usage down to 66.7% with no deletes

Ouch ! that took longer than I thought. If you try this experiement, the numbers won't work exactly because Oracle does not split the block exactly in half: the position of the split is affected by the position that the new row should take in the block, and a couple of other factors. The broad effect is the same though.

HOWEVER - having gone through all that, the delete_rows entry in index_stats is the count of the number of entries marked for deletion. When block cleanout takes place on indexes, entries marked for deletion are really deleted, and the figures in INDEX_STATS change.

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

Chuck Hamilton wrote in message <36b1d3be.7470010_at_news.axs2000.net>...
>How do you interpret the data in INDEX_STATS to see if an index needs
>to be reorganized? I have an index that has 13 million distinct keys,
>0 deleted lead rows, and pct_used of 68. The index isn't oversized as
>it extends every 3 to 4 days. Is this index a reorg candidate? Why
>would it only be using 68% of the space if there are no deleted rows?
>--
>Chuck Hamilton
>chuckh_at_safeaccess.net
>
>Never share a foxhole with anyone braver than yourself!
Received on Fri Jan 29 1999 - 09:54:12 CST