Re: tablespace management in data warehouse

Strategies depend on sizes.

Do not create loads of tablespaces in advance, it gives you lots of pointless space to backup.

If you are partitioning by week (say) and the size of a week is such that a period-based tablespace is a comfortable size (2gb to 8Gb is a range I happen to feel comfortable with) then create the next period in the 3rd week of the current period.

With a 2-grained strategy like this it is relatively easy to use current size of current weeks to decide to add on space to current period if needed, and to predict roughly the right size of next period.

Hints for code strategies -
a) Decide a naming convention for partitions

        that lets you derive partition names for
        date information.

b)    Keep a table of (table-name, partition type)
        and drive Pl/SQl procedures off it.

Hints for tablespaces
a) Don't give tablespaces 'date-related' names.

        given them meaningless names and recycle
       them, otherwise SMON has to scan ever
        increasing numbers of blocks in TS$ every
        5 minutes.

b)    Use locally managed tablespaces, but not
        system managed tablespaces.

Thoughts on archivelog mode:
Typical strategies for VLDBs include lots of

    create table as select NOLOGGING.

1. this keeps the log sizes rather small
2. it makes the archive logs pretty redundant except for recovering header blocks from the most recent backup.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

jeremycdba_at_my-deja.com wrote in message <877nd4$75g$1_at_nnrp1.deja.com>...
>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.
>
Received on Wed Feb 09 2000 - 17:34:42 CST