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

Hello

First analyze all indexes and then
I think that the following query will give you a good approximation. The first part of the sub query compute the size needed to store the keys values
 based on the columns length times distinct values. The second part of the sub query compute the length needed to store the rowids
for the rows in the index.

SELECT SUM(LEN) FROM
(select ALL_INDEXES.INDEX_NAME , SUM(COLUMN_LENGTH * DISTINCT_KEYS) LEN from ALL_IND_COLUMNS , ALL_INDEXES
where ALL_IND_COLUMNS.index_name IN
(SELECT INDEX_NAME FROM ALL_INDEXES WHERE OWNER = 'TAV') AND ALL_IND_COLUMNS.INDEX_NAME = ALL_INDEXES.INDEX_NAME GROUP BY ALL_INDEXES.INDEX_NAME
UNION
select ALL_INDEXES.INDEX_NAME , SUM(NUM_ROWS * 18) LEN from ALL_INDEXES
where OWNER = 'TAV'
GROUP BY ALL_INDEXES.INDEX_NAME) Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Ayyappan S [SMTP:ayyappan.subramaniyan_at_ssiworldwide.com]
> Sent: Mon, February 18, 2002 10:13 AM
> To: Multiple recipients of list ORACLE-L
> Subject: How to find avg data blocks for an index.
>
> 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).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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