Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: calculate size of idx
Srinivas
The space required to store a NUMBER will vary by the number of digits the number contains. IIRC the bytes are 1 + CEIL(digits/2). You can get closer on your index size estimate if you will use the avg(vsize(column_name)) on each of the columns you will have in your index.
For both tables and indexes you can further improve the precision of your estimate by calculating the number of rows that will fit into a block, then compute the number of blocks that will be required.
If you use Google to search using terms like oracle index size formula you will find several formulas for estimating the table and index sizes. Unfortunately in the recent tests I conducted, I could not find any formula that produced really accurate results.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: Kommareddy, Srinivas (MED, Wissen Infotech)
[mailto:Srinivas.Kommareddy_at_med.ge.com]
Sent: Monday, February 16, 2004 10:36 PM
To: oracle-l_at_freelists.org
Subject: calculate size of idx
Hi Lists,
We are going to perform a data load to a table.
To calculate the space required for n rows for a table we use
avg_row_len*n rows from dba_tables.
But how we can do that for indexes ?
B'coz my table has 7 indexes which are built on 10 different columns. I want to add space to index tablespace also... (most of the indexes columns are NUMBER, should I take 32 bytes * n rows to calculate the size )
Thanks and Regards,
Srinivas