Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> why index taking more space.
Hi gurus,
I am try to create index on table ... and after esimating the size of index
... then I created the index ....
the estimated size is different from actaul allocated size... I am missing
something.
Non unique index calculation:
block size - 161
16384-161 = 16223
2) pct free of table 8%
16223 * (8/100) = 1297.84
3)avilable space to store index entries
16223 - 1297.84 = 14925.16
4)space used per row:
avg.row length + no of columns + number oof long columns + 8 header bytes
avg row size :
select avg(nvl(vsize(rollno),0)) + avg(nvl(vsize(asses_no),0)) "avg length"
from lis.parcel
= 6.56
space used per row:
6.56 + 2 + 0 + 8 = 16.56 bytes
5) entries /block = trunc(14925.16/16.56) = 901 entries.
6) no of blocks needed = totno of rows/entries per block = 1874863 /901 = 2080.9 (approx)
size of index = no of blocks * block size = 2080.9* 16384 = 32 mb
aproximately around 32 -33 mb
So when I created index
CREATE INDEX VNZ_PARCEL_INDEX ON
VNZ_PARCEL(ROLL_NO, ASSESS_NO)
TABLESPACE LIS_IDX3 PCTFREE 5 STORAGE(INITIAL 56623104 NEXT 1048576
PCTINCREASE 0 )
;
and I cheked the size of index by going through the extents it is around 59 mb with 4 extens.
Can some body explain me why the index space more than expected ? Am I missing some thing.
regards
naren
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Narender Akula
INET: narender.akula_at_terralinkltd.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). Received on Sun May 13 2001 - 20:45:47 CDT