RE: Explanation of index statistics

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 14 May 2004 13:33:51 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B0064A@EXCHMN3>

Richard - Thanks for posting this paper. It looks really great.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Richard Foote Sent: Friday, May 14, 2004 7:00 AM
To: oracle-l_at_freelists.org
Subject: Re: Explanation of index statistics

Hi Binyamin,

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

Regards

Richard

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.
--

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.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Sat May 15 2004 - 12:42:41 CDT