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 [EMAIL PROTECTED] > -----Original Message----- > From: Ayyappan S [SMTP:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
