RE: How to find avg data blocks for an index.

From: Mike Hately, Choiceline IT Ltd. <mike.hately_at_choiceline.co.uk>
Date: Mon, 18 Feb 2002 01:28:25 -0800
Message-ID: <F001.004121B5.20020218012825@fatcity.com>

Hi,

2 possibles spring to mind:

A simple average of the space allocated to indexes

SELECT AVG(BYTES)
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='INDEX'; This is a bit rough-and-ready but may be OK if your indexes are all well sized.

Alternatively you can analyze each index in turn (ANALYZE INDEX <index_name> VALIDATE STRUCTURE or VALIDATE INDEX <index_name>) and use the column INDEX_STATS.USED_SPACE to derive an average of the actual space in use rather than the space allocated to index extents. Because each VALIDATE overwrites the previous set of results you'll find it's convenient to write a procedure which will validate an index and archive the INDEX_STATS results to a separate table (perhaps prefixing a DATE_VALIDATED column to help you later. Then you can call the procedure for each of your indexes and summarise the results at your leisure.

Hope this helps,
Mike Hately
(Oracle DBA)

-----Original Message-----
Sent: 18 February 2002 08:13
To: Multiple recipients of list ORACLE-L

Hi

I want to estimate the size for a growing database for 2004, I want to find the avg bytes occupied by an index. how to find & calculate it.

Ayyappan.S

---

This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it.

---

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ayyappan S
  INET: ayyappan.subramaniyan_at_ssiworldwide.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mike Hately, Choiceline IT Ltd.
  INET: mike.hately_at_choiceline.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Mon Feb 18 2002 - 03:28:25 CST