分区练习-分区语法

--创建范围分区
create table t_partition_range(id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace pc_dat,
partition t_range_p2 values less than (20) tablespace pc_dat,
partition t_range_p3 values less than (30) tablespace pc_dat,
partition t_range_pmax values less than (maxvalue) tablespace pc_dat);

--查询创建分区的信息
select * from user_part_tables t where t.table_name ='T_PARTITION_RANGE';

select * from user_tab_partitions t where t.table_name = 'T_PARTITION_RANGE';

--创建global索引range分区
create index idx_parti_range_id on t_partition_range(id)
global partition by range(id)(
partition i_range_p1 values less than(10) tablespace pc_dat,
partition i_range_p2 values less than(40) tablespace pc_dat,
partition i_range_pmax values less than(maxvalue) tablespace pc_dat)

--查询索引的分区信息
select * from user_part_indexes t where t.index_name = 'IDX_PARTI_RANGE_ID';
select * from user_ind_partitions t where t.index_name = 'IDX_PARTI_RANGE_ID';

--创建local分区索引
drop index IDX_PARTI_RANGE_ID;
create index IDX_PARTI_RANGE_ID on t_partition_range(id)local;

 

--创建Hash分区
create table t_partition_hash(id number,name varchar2(50))
partition by  hash(id)(
partition t_hash_p1 tablespace pc_dat,
partition t_hash_p2 tablespace pc_dat,
partition t_hash_p3 tablespace pc_dat);

--创建list分区
create table t_partition_list(id number,name varchar2(50))
partition by list(id)(
partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace pc_dat,
partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace pc_dat,
partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace pc_dat,
partition t_list_pd values (default) tablespace pc_dat);

--创建range-hash组合分区
create table t_partition_rh(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
subpartitions 4 store in(pc_dat,pc_dat,pc_dat,pc_dat)(
partition t_r_p1 values less than (10) tablespace pc_dat,
partition t_r_p2 values less than (20) tablespace pc_dat,
partition t_r_p3 values less than (30) tablespace pc_dat,
partition t_r_p4 values less than (maxvalue) tablespace pc_dat);

--查询分区表情况
select * from user_part_tables t where t.table_name = 'T_PARTITION_RH'
select * from user_tab_partitions t where t.table_name =  'T_PARTITION_RH'
select * from user_tab_subpartitions t where t.table_name =  'T_PARTITION_RH'

--对某个分区创建hash分区
create table t_partition_rh2(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)(
partition t_r_p1 values less than (10) tablespace pc_dat,
partition t_r_p2 values less than (20) tablespace pc_dat,
partition t_r_p3 values less than (30) tablespace pc_dat
(subpartition t_r_p3_h1 tablespace pc_dat,
subpartition t_r_p3_h2 tablespace pc_dat,
subpartition t_r_p3_h3 tablespace pc_dat),
partition t_r_p4 values less than (maxvalue) tablespace pc_dat)

--对各个分区创建不同的hash子区
create table t_partition_rh3(id number,name varchar2(50))
partition by range(id) subpartition by hash(name)(
partition t_r_p1 values less than (10) tablespace pc_dat,
partition t_r_p2 values less than (20) tablespace pc_dat
(subpartition t_r_p2_h1 tablespace pc_dat,
subpartition t_r_p2_h2 tablespace pc_dat),
partition t_r_p3 values less than (30) tablespace pc_dat
(subpartition t_r_p3_h1 tablespace pc_dat,
subpartition t_r_p3_h2 tablespace pc_dat,
subpartition t_r_p3_h3 tablespace pc_dat),
partition t_r_p4 values less than (maxvalue) tablespace pc_dat
(subpartition t_r_p4_h1 tablespace pc_dat,
subpartition t_r_p4_h2 tablespace pc_dat,
subpartition t_r_p4_h3 tablespace pc_dat))

--创建range-list组合分区
create table t_partition_rl(id number,name varchar2(50))
partition by range(id) subpartition by list(name)
subpartition template(
   subpartition l1 values ('aa') tablespace pc_dat,
   subpartition l2 values ('bb') tablespace pc_dat,
   subpartition l3 values ('cc') tablespace pc_dat,
   subpartition l4 values ('dd') tablespace pc_dat)(
 partition t_r_p1 values less than (10) tablespace pc_dat,
 partition t_r_p2 values less than (20) tablespace pc_dat,
 partition t_r_p3 values less than (30) tablespace pc_dat,
 partition t_r_pd values less than (maxvalue) tablespace pc_dat)