Re: Explanation of index statistics

On Fri, 14 May 2004 22:00:25 +1000 "Richard Foote" <richard.foote_at_bigpond.com> wrote:

:>They match because most of the leaf rows (106536) are deleted leaf rows
:>(106533) meaning you only have *3* non deleted leaf rows.

:>Basically your table has undergone a somewhat dramatic shrinkage in size.

:>If you want to learn a thing or two about indexes, take a took at a recent
:>presentation of mine at
:>www.actoug.org.au/Downloads/oracle_index_internals.pdf

Very interesting.

I got the impression from it that unused leaf entries will be reused.

Even though the table went down to zero rows, when it grew again the number of leaf entries increased as well.

Am I missing something?

:>----- Original Message -----
:>From: "Binyamin Dissen" <bdissen_at_dissensoftware.com>
:>To: <oracle-l_at_freelists.org>
:>Sent: Friday, May 14, 2004 8:50 PM
:>Subject: Explanation of index statistics
:>
:>
:>I did an analyze against the index that is using so many buffers (when there
:>was less than 10 rows in the table).
:>
:>analyze index &indexname validate structure
:>
:>The INDEX_STATS table shows
:>
:> HEIGHT BLOCKS NAME PARTITION_NAME
:>3
:>16384 &indexname
:>
:>LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN
:>106536 6465 2450320 3540 6464 157 122626
:>BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY
:> 3932 106533 2450251 30847 18
:>
:>BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
:>PRE_ROWS
:> 23503424 2572946 11 3.4536908 5.2268454
:>0
:>PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
:> 0 2 28
:>
:>How do these numbers match to such a small table (at the time of the
:>analyze)?
:>
:>--
:>Binyamin Dissen <bdissen_at_dissensoftware.com>
:>http://www.dissensoftware.com
:>
:>Director, Dissen Software, Bar & Grill - Israel
:>----------------------------------------------------------------
:>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.

--
Binyamin Dissen <bdissen_at_dissensoftware.com>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel
----------------------------------------------------------------
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
-----------------------------------------------------------------