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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Hately, Choiceline IT Ltd. 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).
