1.实践

首先建分区表

create table simng_part (

latn_id number(4) not null,

pname char(200),

mon_id number(2) not null

)

partition by range(latn_id,mon_id)

(

partition part_a01 values less than (2000, 2) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_a02 values less than (2000, 3) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_a12 values less than (2000,13) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b01 values less than (2003, 2) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b02 values less than (2003, 3) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b12 values less than (2003,13) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_d12 values less than (2020,13) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000)

)

/

 
insert into simng_part values(2000,a,1);

insert into simng_part values(2000,a,2);

insert into simng_part values(2000,a,3);

insert into simng_part values(2002,a,1);

insert into simng_part values(2002,a,3);

insert into simng_part values(2002,a,9);

insert into simng_part values(2019,a,1);


commit;


create table simng_part1 (

latn_id number(4) not null,

pname char(200),

mon_id number(2) not null

)

partition by range(mon_id,latn_id)

(

partition part_a01 values less than ( 2,2000) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b01 values less than ( 2,2003) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_a02 values less than ( 3,2000) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b02 values less than ( 3,2003) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_a12 values less than (13,2000) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_b12 values less than (13,2003) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000),

partition part_d12 values less than (13,2020) tablespace tbs_data pctfree 10 storage(initial 1024k next 1024k maxextents 2000)

)

/

 

insert into simng_part1 values(2000,a,1);

insert into simng_part1 values(2000,a,2);

insert into simng_part1 values(2000,a,3);

insert into simng_part1 values(2002,a,1);

insert into simng_part1 values(2002,a,3);

insert into simng_part1 values(2002,a,9);

insert into simng_part1 values(2019,a,1);

commit;

 

2.问题

先思考两个问题:

试问表simng_part :part_b01分区中有几条记录。

试问表simng_part1 :part_a01分区中有几条记录。

 


答案是:

latn_id name mon_id

第一个

2002 a 3

2002 a 1

2002 a 9

第二个

2000 a 1

2002 a 1

2019 a 1

 

查询如下:


sql> select * from simng_part partition(part_b01);

 
latn_id pname                mon_id

------- ------------- ----------------

   2002 a                         1

   2002 a                         3

   2002 a                         9


sql> select * from simng_part1 partition(part_a01);


latn_id pname                mon_id

------- ------------- ----------------

   2000 a                         1

   2002 a                         1

   2019 a                         1


3.结论


关于range partition分区,

首先注意的是,分区不包含上限
 

同时

对于存在多个列来进行range partition

遵循这个原则:

只要满足第n列条件,就放在这个分区,而不管第n+1列是否满足!