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?
>
> Thanks!
>
> DW
>

I think you've got all the details you need from other posts - but you might like to look at the following:

http://www.jlcomp.demon.co.uk/index_efficiency_2.html

Assuming your numeric columns are all worst case, then you have:

    18.8M *         -- rows
    (6 + 1 +          -- non-unique index rowid
    4 +                  -- row overhead
    6 + 1 +                --number(10) + length byte
    6 + 1 +                --number(10) + length byte
    6 + 1 +                --number(10) + length byte
    7 + 1                -- date + length byte
    ) * 100/90   *     -- fudge factor for pctfree 10
    8192 / 8100  *      -- fudge factor of block overhead
    1.01                    -- fudge factor for branches

• 18.8M * 40 * 100/90 * 8192/8100 * 1.01
• 853M

Which is pretty close - especially if you've been looking at dba_segments rather than the high water mark on the index segment.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005