Re: Question: Normal index structure/size under 9i

"DW" <dwhittier_at_shaw.ca> wrote in message news:1116975583.298036.253570_at_g44g2000cwa.googlegroups.com...
> Greetings:
>
> Basic architectural question here:
>
> I have a normal index against 4 columns of a 18.8M row table. The types
> of the 4 indexed columns are 3x NUMBER(10) and a DATE.
>
> By my math, with a DATE being max 7 bytes, this adds up to 37 bytes of
> indexed data per row.
>
> 37 * 1.8M = 666M bytes, or rougly 680MB. That tells me that for a 1.8M
> table with these columns, the amount of indexed data in the index
> should be 680MB.
>
> After I do a rebuild, the index size estimate (using TOAD 8) is 868MB.
>
> So is it typical for an index to be roughly 1/3 again the size of the
> indexed data?
>

Hi DW

As others have mentioned, there are a number of errors with your calculations such as your Number lengths, missing out on the rowids (which btw are 6 not 18 bytes), block header overheads which include the 2 (by default) ITL entries, 4 bytes of overhead per index entry, etc. etc. At the leaf block level, most of these overheads are somewhat static/proportional and so depending on the actual used proportion of the index block and importantly the block size itself, your 1/3 guesstimate ratio is therefore somewhat dependent on the average length of indexed data; the larger the average length, the lower the comparable overhead ratio.

The amount of unused space (pctfree value after the rebuild or introduced later via block splitting) is probably the most significant factor with 25% unused space an "expected" average over time with "randomly" modified B*Tree indexes, although this is dependent on a number of factors.

However, you also need to take into consideration in your calculations the space used by branch level blocks in the B-tree hierarchy. The "ratio" of this overhead is dependent on the number of the of branch blocks required to reference the number of leaf blocks (which again is very much dependent on the average length of a branch entry).

You may want to have a look at a presentation of mine at www.actoug.org.au/Downloads/oracle_index_internals.pdf where I look at some of these details as it may give you an insight on how you can determine some of these things for yourself (which was one of it's main aims).

Cheers

Richard Received on Wed May 25 2005 - 08:14:03 CDT