定语复合分区表的2种语法

1、在每个分区中嵌套子分区：

create table ptest(id int ,name varchar2(30),gender varchar2(1))
partition by range(id)
subpartition by list(gender)
(
partition p1 values less than(10)
(subpartition p1_1 values ('f'),
subpartition p1_2 values ('m'),
subpartition p1_3 values (default)),
partition p2 values less than (maxvalue)
(subpartition p2_1 values ('f'),
subpartition p2_2 values ('m'),
subpartition p2_3 values (default))
);

2、给出一个子分区的模板，就不用每个分区里都嵌套子分区了，简洁一些：

create   table   ptest2 
( 
  id   number(3), 
  name   varchar2(20), 
  sex   varchar2(4), 
  age   number(3) 
) 
partition   by   range   (age) 
subpartition   by   list   (sex) 
SUBPARTITION   TEMPLATE 
( 
  subpartition   p1_subp1   values( 'boy'), 
  subpartition   p1_subp2   values( 'girl') 
) 
( 
  partition   p1   values   less   than   (10), 
  partition   p2   values   less   than   (20), 
  partition   p3   values   less   than   (30)
);

3、查询子分区的内容：

  select * from ptest /*partition(p1) */subpartition(p1_2)；

4、查询分区表的一些常用视图：

select * from user_part_tables;  ---查分区表
select * from user_tab_partitions order by 3 ;  ---查分区
select * from user_tab_subpartitions order by 3 ;  ---查子分区