Oracle数据库表范围分区策略测试过程

核心提示：/*该试验针对按照递增列作为分区条件的情况，按照时间作为分区条件的等下次测试*/create tablespace test01 datafile 'D:oracleoradatamyoraest01.dbf'size 50m;create tablespace test02 datafile '

/*该试验针对按照递增列作为分区条件的情况，按照时间作为分区条件的等下次测试*/

create tablespace test01 datafile 'D:oracleoradatamyora　　est01.dbf'size 50m;
create tablespace test02 datafile 'D:oracleoradatamyora　　est02.dbf'size 50m;
create tablespace test03 datafile 'D:oracleoradatamyora　　est03.dbf'size 50m;
create tablespace test_idx01 datafile 'D:oracleoradatamyora　　est_idx01.dbf'size 20m;
create tablespace test_idx02 datafile 'D:oracleoradatamyora　　est_idx02.dbf'size 20m;
create tablespace test_idx03 datafile 'D:oracleoradatamyora　　est_idx03.dbf'size 20m;
Create table test (
　aa integer,
　bb varchar2(100)
)
partition by rang(aa)(
Partition part_01 values less than(10000) Tablespace test_idx01,
Partition part_02 values less than(30000) Tablespace test_idx02,
Partition part_03 values less than(maxvalue)Tablespace test_idx03
);
Create Index idx_test_aa On test(aa)Local
(
Partition part_01 Tablespace test_idx01,
Partition part_02 Tablespace test_idx02,
Partition part_03 Tablespace test_idx03
);
-- Create sequence
create sequence seq_test_aa
minvalue 1
maxvalue 60000
start with 1
increment by 1
cache 10;
insert into test values(seq_test_aa.nextval,'test');
select Min(callid),Max(callid),Count(*) from test partition(part_01);
select Min(callid),Max(callid),Count(*) from test partition(part_02);
select Min(callid),Max(callid),Count(*) from test partition(part_03);
select Min(callid),Max(callid),Count(*) from test Where callid < 10000;
select Min(callid),Max(callid),Count(*) from test Where callid > 10000 And callid < 30000;
select Min(callid),Max(callid),Count(*) from test Where callid >= 30000;
exp test/test@myora file=e:　　est01.dmp buffer=8192000 tables=test:part_01 rows=Y
exp test/test@myora file=e:　　est02.dmp buffer=8192000 tables=test:part_02 rows=Y
exp test/test@myora file=e:　　est03.dmp buffer=8192000 tables=test:part_03 rows=Y
/*注意：这里删除的分区要从后面删，因为oracle不允许向前添加分区，只能向后添加分区*/
alter table cdr drop partition part03 ;
select Min(aa),Max(aa),Count(*) from test partition(part_01);
select Min(aa),Max(aa),Count(*) from test partition(part_02);
select Min(aa),Max(aa),Count(*) from test partition(part_03);　 --本条将报错，提示无此分区
select Min(aa),Max(aa),Count(*) from test Where aa < 3000000;
select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000;
select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;　--将无数据显示
alter table test add partition part03 values less than(maxvalue) tablespace test03;
select Min(aa),Max(aa),Count(*) from test partition(part_01);
select Min(aa),Max(aa),Count(*) from test partition(part_02);
select Min(aa),Max(aa),Count(*) from test partition(part_03);　--将无数据
select Min(aa),Max(aa),Count(*) from test Where aa < 3000000;
select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000;
select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;　 --将无数据显示
imp test/test@myora FILE=e:　　est03.dmp TABLES = (cdr:part_03) IGNORE=y
select Min(aa),Max(aa),Count(*) from test partition(part_01);
select Min(aa),Max(aa),Count(*) from test partition(part_02);
select Min(aa),Max(aa),Count(*) from test partition(part_03);　
select Min(aa),Max(aa),Count(*) from test Where aa < 3000000;
select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000;
select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;　
delete from test partition(part_02);
commit;
select Min(aa),Max(aa),Count(*) from test partition(part_01);
select Min(aa),Max(aa),Count(*) from test partition(part_02);　--将无数据显示
select Min(aa),Max(aa),Count(*) from test partition(part_03);
select Min(aa),Max(aa),Count(*) from test Where aa < 3000000;
select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000;　--将无数据显示
select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;
insert into test values(40000,'test');
commit;
select Min(aa),Max(aa),Count(*) from test partition(part_01);
select Min(aa),Max(aa),Count(*) from test partition(part_02);　--将显示1条数据
select Min(aa),Max(aa),Count(*) from test partition(part_03);
select Min(aa),Max(aa),Count(*) from test Where aa < 3000000;
select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000;　--将显示1条数据
select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000;
select count(*) from test where aa > 1000001; --将有大量数据统计出
select count(*) from test partition(part_02) where aa > 1000001; --结果为0条
alter table cdr split partition part_02　at (50000) into(partition part_02,partition part_06 tablespace test01p2);