why index taking more space.

From: Narender Akula <narender.akula_at_terralinkltd.com>
Date: Sun, 13 May 2001 18:45:47 -0700
Message-ID: <F001.00302303.20010513180520@fatcity.com>

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