Re: estimate sort_area_size

sort_area_size is not a finite value below which an index won't be created. It's just the size of memory that Oracle can use as buffer for the sort operations. Beyond that, the RDBMS will use disc space in the temporary tablespace to make multiple sort/merge passes.

So, you don't need to "size it" for a specific size of index.

Of course, if you have a particularly large index to create, you MAY want to increase the sort_area_size just for that operation.

But your determining factor should be to minimize sort/merge passes rather than match sort_area_size to size of index.

As a rule of thumb, DBAs usually set the sort_area_size to the default allocation size of the temporary tablespace, which itself should be a multiple of the blocksize.

IOW, if segments are allocated in temporary tablespace in chunks of say, 1Mb, then your sort_area_size should be 1Mb or a multiple of it.

Again, and as a VERY GENERAL rule of thumb: set it to 500Kb for most uses and 1Mb for very large table index creation. You may even go to 5 or 10Mb. But we're talking VERY large tables for that to be needed.

--
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
"zeb" <thierry.constant1.nospam_at_free.fr> wrote in message
news:3ec747cb$0$17283$626a54ce_at_news.free.fr...


>

> Is there a way  to estimate the sort_area_size for the creation of index

for


> a big table ?

>