Re: treedump versus validate structure

There are problems with treedump.

1. It crashes the session on some versions of Oracle
2. It doesn't work on all index segment types
3. In recent versions it dumps every leaf block in full

Particularly in light of (c) you need to consider the fact that a tree dump walks the index and writes to file for every single block. For large indexes this could be a significant overhead.

On the plus side, it doesn't lock the index, of course.

In answer to the questions:
>1. To obtain the percentage of deleted rows, would something like
>1-(rrow/(nrow - rrow))*100 for the leaf blocks work? Is there a
>better way?

   nrow is the number of rows currently in the block    rrow is the number of rows NOT marked for deletion So the numbers do not give you a meaningful indication of how well used the space is; only of how many of the current rows are marked for deletion (and these deletes may or may not be rolled back) so you get no idea of how much extra free space there is in the block.
In fact I think you meant to express the formula as:   100 * (nrow - rrow) / nrow

>2. To obtain the height of the index, do you count the number of
>branches?

Depending on your point of view this is the 'level' or 'level + 1' of the first branch block (i.e. the root) that you hit. Add the one if you want the height to include the leaf blocks.

>3. Is there any table locking or known performance impacts that would
>argue against doing a treedump?

See above regarding performance.

A better option is to know the definition of the index, the typical column lengths for the indexed column (you could write your own code to do a fast full index scan summing the length of the vsizes() of the columns in the index) Add 5 bytes per entry plus one byte per column and 6 bytes for the rowid (adding one byte for a non-unique index) add one byte per row per descedning column. This gives you a rough guide to the optimum size of the index. Add about 2-5 percent for branch blocks, divide by the block size - 100; and you get the number of blocks needed. Compare this with the actual number of blocks below the HWM.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Russ Brooks wrote in message ...


>Hi,

>  I'm looking into rebuilding some indices and have seen

>recommendations to use treedumps instead of doing a validate structure

>on the index.  I have a couple of questions.

>1. To obtain the percentage of deleted rows, would something like

>1-(rrow/(nrow - rrow))*100 for the leaf blocks work?  Is there a

>better way?

>2. To obtain the height of the index, do you count the number of

>branches?

>3. Is there any table locking or known performance impacts that would

>argue against doing a treedump?

>

>If anyone has compared the performance between the two methods, I'd be

>very interested in hearing from you.

>

>Thanks,

>Russ Brooks