1.这个大table存放在一个tablespace, 该tablespace有多个datafile, 分别放在不同磁盘.(当然这样效率很差~ )2.用partition, 这个例子中是用date类型的列来做分区. 将一个很大的table分布在tablespace p_dw01,tablespace p_dw02,tablespace p_dw03,tablespace p_dw04这四个tablespace上, 当然每个tablespace都可能包含一个或多个datafile, 每个datafile存放的磁盘可以一样,可以不一样. (但总体设计上要尽量分散到个硬盘,充分利用硬盘的个数,来增加并行度) create table F_DWH_SALE( product_id varchar2(20) not null, saler_id varchar2(20) not null, customer_id varchar2(20) not null, sale_date date not null, retail_price number(2) not null, retail_profit number(2) not null ) compress tablespace DREAM_TEST partition by range(sale_date) ( partition part_2000 values less than (to_date('2001-01-01','YYYY-MM-DD')) tablespace p_dw01, partition part_2001 values less than (to_date('2002-01-01','YYYY-MM-DD')) tablespace p_dw02, partition part_2002 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace p_dw03, partition part_2003 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace p_dw04, partition part_2004 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace p_dw05, partition part_2005 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace p_dw06, partition part_2006 values less than (to_date('2007-01-01','YYYY-MM-DD')) tablespace p_dw07 );
将一个很大的table分布在tablespace p_dw01,tablespace p_dw02,tablespace p_dw03,tablespace p_dw04这四个tablespace上, 当然每个tablespace都可能包含一个或多个datafile, 每个datafile存放的磁盘可以一样,可以不一样. (但总体设计上要尽量分散到个硬盘,充分利用硬盘的个数,来增加并行度)
create table F_DWH_SALE(
product_id varchar2(20) not null,
saler_id varchar2(20) not null,
customer_id varchar2(20) not null,
sale_date date not null,
retail_price number(2) not null,
retail_profit number(2) not null
)
compress
tablespace DREAM_TEST
partition by range(sale_date) (
partition part_2000 values less than (to_date('2001-01-01','YYYY-MM-DD')) tablespace p_dw01,
partition part_2001 values less than (to_date('2002-01-01','YYYY-MM-DD')) tablespace p_dw02,
partition part_2002 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace p_dw03,
partition part_2003 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace p_dw04,
partition part_2004 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace p_dw05,
partition part_2005 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace p_dw06,
partition part_2006 values less than (to_date('2007-01-01','YYYY-MM-DD')) tablespace p_dw07
);