Oracle-L: INDEX_STATS question

From: Keith Moore <kmoore7_at_jcpenney.com>
Date: Fri, 11 Apr 2003 13:08:40 -0800
Message-ID: <F001.0057FD64.20030411130840@fatcity.com>

('binary' encoding is not supported, stored as-is)

I populated index_stats with data from an index for a 13 million row table. The data is shown below.

The index is not very fragmented as del_lf_rows is about 2% of the total. So why is the PCT_USED only 27%?It seems like we are using many more blocks than we need and I'm not sure how we got here. The indexed column is 19 characters long.

What am I missing here?

Here is the data:

HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN ---------- ---------- ---------- ---------- ----------- ----------

4 512000 13800504 429284 427815624 3860

BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN ---------- ---------- ----------- ---------- ----------- ---------------

429283 6512 13156923 3916 309298 9588238

DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY

------------- ----------------- ----------- ---------- ---------- ------------
       240216           1243022  1682537232  440972547         27   57.4503946

BLKS_GETS_PER_ACCESS

33.2251973

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Keith Moore
INET: kmoore7_at_jcpenney.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Apr 11 2003 - 16:08:40 CDT