Re: Where to store indexes for partitioned tables?

I recommend you have a look at
http://www.rittman.net/archives/000822.html and specifically at the mentioned white paper by Arup Nanda http://download.oracle.com/owsf_2003/36605_nanda.doc. It helped me a lot deciding how to implement partitioning and maintenance. Also I am writing something, call it an API, that will be driven by a little metadata. When we are loading our datawarehouse, the oracle Warehouse Builder process flow, will call the API, giving the destination tablename and the systemdate, and the API will:
- check for the presence of the correct paritiong (either list or
range)
- if absent, check for the presence of the correct tablespace

• if absent, create the tablespace using Oracle Managed Files (OMF)
• create the partition and/or split and move the overflow partition
• if needed, create an exchange table if needed in the correct tablespace
• create all relevant indexes The API will also handling dropping partitions, dropping bitmap indexes, rebuilding indexes, gathering statics, all by just the tablename and sysdate as arguments. Also putting tablespaces readonly in order to reduce stress on the backup process will be added. Hence we will give Operations almost zero work in maintaining the pletora of partitions, tablespaces and datafiles. Indeed we will store local indexes in the same tablespace as the data resides (See Arup Nanda's paper). Good Luck, Erik Ykema