Re: TEMP tablespace: a big one or 10 small ones?

schonlinner_at_yahoo.com schrieb:

> And my current answer is:
> Use a tablespace group with 10 temp tablespaces with autoextend on and
> simply ensure that there is enough space in the file system...

This will work.

> That way whatever will happen the temp tablespaces may increase to
> whatever they like until the FS is full and as soon as the sort is
> done, the temp tablespaces fall down to their initial size.

Only partially correct. The temp tablespaces increase to meet the sort requirements, _but_ they don't decrease afterwards. Once allocated space is reused, so if you have done the biggest sort ever (TM) you have your maximum of this particular tablespace. But don't count one users don't invent queries that are worse than what you ever thought of.

> Or is something wrong in my thoughts?

The temp tablespaces only increase, the don't shrink.

hth,

   Moritz Received on Wed Sep 14 2005 - 09:00:52 CDT