子分区和子分区模板

复合分区可以是：范围－列表，范围－HASH 

11g加：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST

1.子分区创建(范围－列表)

CREATE TABLE mylong

(MYDATE DATE,ATYPE NUMBER,ABC NUMBER)

PARTITION BY RANGE(MYDATE)

SUBPARTITION BY list(ATYPE)

SUBPARTITION TEMPLATE

(SUBPARTITION "A" VALUES(1) TABLESPACE users,

SUBPARTITION "B" VALUES(3) TABLESPACE users)

(

PARTITION MY_20111117 VALUES less than (TO_DATE('20111118','yyyymmdd')),

PARTITION MY_20111118 VALUES less than (TO_DATE('20111119','yyyymmdd')),

PARTITION MY_20111119 VALUES less than (TO_DATE('20111120','yyyymmdd'))

);

2.子分区查询

select * from MYLONG SUBPARTITION(MY_20111117_A);

3.子分区信息

select table_name,partition_name,SUBPARTITION_NAME from user_tab_subpartitions;

4.11g下可以(范围-范围)

CREATE TABLE mylong( MYDATE DATE,ATYPE NUMBER,ABC NUMBER)

PARTITION BY RANGE(MYDATE)

SUBPARTITION BY RANGE(ATYPE)

SUBPARTITION TEMPLATE

(SUBPARTITION "2" VALUES less than(2) TABLESPACE users,

SUBPARTITION "max" VALUES less than(MAXVALUE) TABLESPACE users

)

(

PARTITION MY_20111117 VALUES less than (TO_DATE('20111118','yyyymmdd')),

PARTITION MY_20111118 VALUES less than (TO_DATE('20111119','yyyymmdd')),

PARTITION MY_20111119 VALUES less than (TO_DATE('20111120','yyyymmdd'))

);