Re: estimate sort_area_size

"zeb" <thierry.constant1.nospam_at_free.fr> wrote in news:3ec747cb$0$17283$626a54ce_at_news.free.fr:

> Hi,
>
> Is there a way to estimate the sort_area_size for the creation of
> index for a big table ?
>
> Thanks in advance
>
>

Do you mean "Is there a way to estimate the temp tablespace size needed to create a big index"? The rule I use for that is 1.25 * estimated index size. To estimate the index size compute the number of index entries that will fit in one block and divide the the number of rows in the index by the number of entries per block.

For 8i, the avg_index_value_size = R + D + F + V where

R=rowid size (10 for 8i and 9i, 8 for Oracle 7)
D=combined column lengths
F=number of columns whose length is < 127 bytes
V=2 * number of columns whose length is >= 127 bytes.

This formula used to be in the 7 and 8i documentation. I haven't found it in the 9i docs yet. Received on Mon May 19 2003 - 08:54:25 CDT