Question about table design
Hi,
I have to design a large data warehouse(2 Tb) and wanted to ask a question.
For partitioned tables should I put each partition in its own tablespace or create a large tablespace and put all partitions in that tablespace ?
plz advise the design strategy.
Design 1 (each partition in its own tablespace)
===============================================
create table schema_owner.fact1
( sales_date date,
sales_amount number(10,3)
sales_type varchar2(32)
)
partition by range(sales_date)
(partition p_fact1_200307 values less than (to_date ('01-JUL-2003','DD-MON-YYYY'))
tablespace tbs_fact1_200307,
partition p_fact1_200308 values less than (to_date ('01-AUG-2003','DD-MON-YYYY'))
tablespace tbs_fact1_200308
....
....
)
/
Design 2(one tablespace all partitions in that tablespace)
================================================== ======
create table schema_owner.fact1
( sales_date date,
sales_amount number(10,3)
sales_type varchar2(32)
)
partition by range(sales_date)
(partition p_fact1_200307 values less than (to_date ('01-JUL-2003','DD-MON-YYYY'))
tablespace tbs_fact1_data ,
partition p_fact1_200308 values less than (to_date ('01-AUG-2003','DD-MON-YYYY'))
tablespace tbs_fact1_data
)
/