建立如下表create table test_partition2 (
year_ID NUMBER(4) not null,
name char(200),
MON_ID NUMBER(2) not null
)
PARTITION BY RANGE(mon_ID,year_ID)
(
PARTITION PART_1 VALUES LESS THAN ( 2,2000) TABLESPACE TBS_DATA,
PARTITION PART_2 VALUES LESS THAN ( 2,2003) TABLESPACE TBS_DATA ,
PARTITION PART_3 VALUES LESS THAN ( 3,2000) TABLESPACE TBS_DATA,
PARTITION PART_4 VALUES LESS THAN ( 3,2003) TABLESPACE TBS_DATA ,
PARTITION PART_5 VALUES LESS THAN (13,2000) TABLESPACE TBS_DATA ,
PARTITION PART_6 VALUES LESS THAN (13,2003) TABLESPACE TBS_DATA,
PARTITION PART_7 VALUES LESS THAN (13,2020) TABLESPACE TBS_DATA
);
插入这些语句insert into test_partition2 values(2000,'a',1);
insert into test_partition2 values(2000,'a',2);
insert into test_partition2 values(2000,'a',3);
insert into test_partition2 values(2002,'a',1);
insert into test_partition2 values(2002,'a',3);
insert into test_partition2 values(2003,'a',9);
insert into test_partition2 values(2019,'a',1);
commit;然后按每个分区查询
select * from test_partition2 partition(part_1);
结果是:2000 a 1
2002 a 1
2019 a 1
是否可以这样理解:mon_id<2 ,就不用管year_id,把这三条记录放到part_1分区里面?第二个问题:
select * from test_partition2 partition(part_2)时
结果是:2000 a 2
这时候数据库是怎么把这条记录插到这个分区里面的呢?这条数据为什么没有放到part_3里面?请给我讲解一下数据库是按什么规则把数据插到各个分区里面的,谢谢啦
year_ID NUMBER(4) not null,
name char(200),
MON_ID NUMBER(2) not null
)
PARTITION BY RANGE(mon_ID,year_ID)
(
PARTITION PART_1 VALUES LESS THAN ( 2,2000) TABLESPACE TBS_DATA,
PARTITION PART_2 VALUES LESS THAN ( 2,2003) TABLESPACE TBS_DATA ,
PARTITION PART_3 VALUES LESS THAN ( 3,2000) TABLESPACE TBS_DATA,
PARTITION PART_4 VALUES LESS THAN ( 3,2003) TABLESPACE TBS_DATA ,
PARTITION PART_5 VALUES LESS THAN (13,2000) TABLESPACE TBS_DATA ,
PARTITION PART_6 VALUES LESS THAN (13,2003) TABLESPACE TBS_DATA,
PARTITION PART_7 VALUES LESS THAN (13,2020) TABLESPACE TBS_DATA
);
插入这些语句insert into test_partition2 values(2000,'a',1);
insert into test_partition2 values(2000,'a',2);
insert into test_partition2 values(2000,'a',3);
insert into test_partition2 values(2002,'a',1);
insert into test_partition2 values(2002,'a',3);
insert into test_partition2 values(2003,'a',9);
insert into test_partition2 values(2019,'a',1);
commit;然后按每个分区查询
select * from test_partition2 partition(part_1);
结果是:2000 a 1
2002 a 1
2019 a 1
是否可以这样理解:mon_id<2 ,就不用管year_id,把这三条记录放到part_1分区里面?第二个问题:
select * from test_partition2 partition(part_2)时
结果是:2000 a 2
这时候数据库是怎么把这条记录插到这个分区里面的呢?这条数据为什么没有放到part_3里面?请给我讲解一下数据库是按什么规则把数据插到各个分区里面的,谢谢啦
第二个问题:
首先mon_id <=2且year_id<2000的归到part_1;
而mon_id =2 且year_id>2000 且year_id<2003归到part_2;
同理mon_id =2且<=3 肯定归到part_3中,而mon_id =2且year_id<2003也要归到part_3中。
2,2000归到part_2;
2,2003归到part_3
你创建的时候PARTITION PART_1 VALUES LESS THAN ( 2,2000) TABLESPACE TBS_DATA,
因为1<2,所以所有1,*的都归到第一个分区