Re: tablespace management in data warehouse

jeremycdba_at_my-deja.com wrote:
>
> I'm looking for information on defining tablespaces for partitions in a
> data warehouse. For example if I'm partitioning a table by month what's
> the best way to predefine the tablespaces for each monthly partition.
> I'm considering defining each tablespace with a small datafile size and
> using autoextend to grow the datafile if required. Should I define all
> tablespaces for the next couple of years or should I define them
> dynamically i.e at load time??
>
> Any information appreciated especially documents, articles or white
> papers.
>
> I'm using Oracle 8i on Unix Solaris.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Autoextend is one of those features which sounds nice but can result in getting phone calls at 0300. You are better off accurately sizing the partitions in advance. You can allow for growth by using historical patterns with seasonal adjustments (check out a statistics text on time series analysis for detailed data on this).

Don't worry about drastic changes such as those that would result from a merger with a company of equal size since no one is able to forecast extraordinary events.

BTW, whatever you do in this case is quite likely to be wrong due to circumstances beyond your control. I once sized a table for a mill where they swore that they would never work on Sundays, never work a third shift, and never expand beyond 80 looms. A year later I got a phone call because the system wasn't working right. It turns out that they were now working 24x7 and had managed to squeeze 100 looms into the mill.

hth
--
Jerry Gitomer
Once I learned how to spell DBA, I became one. Received on Tue Feb 01 2000 - 23:36:23 CST