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

On 24 May 2005 15:59:43 -0700, "DW" <dwhittier_at_shaw.ca> wrote:

>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.

Incorrect. Numbers store two digits per byte and have a length byte. A date is *always* 7 bytes not *max*.

You also need to take into account pct free (default 10 percent) and room for transaction space (IIRC 23 bytes). For a 2k block, the max number of bytes that can be used is 1889.

>
>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

--
Sybrand Bakker, Senior Oracle DBA