Re: Re-use of Leaf Nodes

Douglas Hawthorne wrote:

>
> Howard,
>
> The difference appears to come about because I was using the
> DBMS_STATS.GATHER_INDEX_STATS procedure. I am able to reproduce the
> phenomena you discovered by using the ANALYZE TABLE command.
>
> With the ANALYZE TABLE command, I get the following results:
> ID Test Done Min Inv# Max Inv# Leaf Blks B Level CF # Rows
> --- --------------- -------- -------- --------- ------- ------- -------
> 1 Insert 10,000 1 10,000 18 1 47 10,000
> 2 Insert 10,000 1 20,000 37 1 95 20,000
> 3 Insert 10,000 1 30,000 56 1 143 30,000
> 4 Delete 20,000 20,001 30,000 56 1 49 10,000
> 5 Insert 10,000 20,001 40,000 57 1 98 20,000
> 6 Insert 10,000 20,001 50,000 57 1 147 30,000
> 7 Insert 10,000 20,001 60,000 76 1 195 40,000
>
> With the DBMS_STATS.GATHER_INDEX_STATS procedure, I get the following
> results:
> ID Test Done Min Inv# Max Inv# Leaf Blks B Level CF # Rows
> --- --------------- -------- -------- --------- ------- ------- -------
> 1 Insert 10,000 1 10,000 18 1 47 10,000
> 2 Insert 10,000 1 20,000 37 1 95 20,000
> 3 Insert 10,000 1 30,000 56 1 143 30,000
> 4 Delete 20,000 20,001 30,000 20 1 49 10,000
> 5 Insert 10,000 20,001 40,000 38 1 98 20,000
> 6 Insert 10,000 20,001 50,000 57 1 147 30,000
> 7 Insert 10,000 20,001 60,000 76 1 195 40,000

Oh lord! It never stops, does it?! Just when you think you've got something nailed, some other degree of freedom pops loose!

The clarification is important, and maybe someone like Jonathan or Richard can explain the difference 'from the inside'.

It looks as if DBMS_STATS is merely counting those leaf nodes that actually have some data in them, and not ones which happen to be part of the physical structure but which happen also to be completely empty.

And I suppose that (since LEAF_NODES is used by the optimiser) the DBMS_STATS is more "accurate" as far as working out execution plans are concerned, but less accurate as a descriptor of physical reality.

I'm beginning to think that it was rather foolish of Oracle ever to have exposed most of the statistics in DBA_INDEXES to us in the first place!

Thanks for the important clear-up
Regards
HJR
> The command I used was:
> EXEC dbms_stats.gather_index_stats( -
> ownname => USER, -
> indname => 'TEST_LEAF_NODES_PK' -
> )
>
> I realised my mistake with PCTUSED after I had posted.
>
> Douglas Hawthorne
>
>
Received on Wed May 12 2004 - 07:25:56 CDT