Oracle Database Discussions

Hi - I'm currently in the process of designing/developing a new warehouse. My main fact table will be range partitioned by a numeric representation of hour.

Further - it will be hash partitioned on another logical key.

So when I define the table - I specify the tablespace for the partition. I then define the subpartition - and drop the 8 subpartitions into their own tablespaces.

Now - As I create a new partition on the table - the new partition will go into its own tablespace. However - what about the subpartitions? Will they default into the tablespace of the new partition? Or into the 8 tablespaces initially defined for the subpartitions?


Further - I will be loading the data into a temporary table, index it, analyze it, and then swap it into my main table.

My temporary table has to be partitioned according to the subpartitions of my main table in order for the exchange to work.

Can anyone explain to me how the exchange works in regards to the tablespaces? And - the tablespaces for the partitions - which become sub-partitions after the swap?

Thanks for any insight!

Todd