CSDN博客

img brucelau

tip:关于range partition 用到多列的一个小案例

发表于2004/1/20 21:11:00  785人阅读

关于range partition 用到多列的一个小案例

 

作者:刘颖博

时间:2003-12-29

mailliuyingbo@126.com,请指正

 

转载请注明出处及作者

 

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列是否满足!

 

……………………………………………………………………………………

感谢 jxc@花城

 

 

 

 

0 0

相关博文

我的热门文章

img
取 消
img