Oracle-L: RE: INDEX_STATS question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 11 Apr 2003 14:43:51 -0800
Message-ID: <F001.0057FF75.20030411144351@fatcity.com>

Keith - Simple-minded idea. What is the PCT_FREE setting in your USER_INDEXES table for this index?

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, April 11, 2003 4:09 PM
To: Multiple recipients of list ORACLE-L

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

--

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

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.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 - 17:43:51 CDT