oracle库 整形范围,Oracle数据库表范围分区策略测试过程

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

create tablespace test01 datafile 'D:oracleoradatamyoratest01.dbf'size 50m;

create tablespace test02 datafile 'D:oracleoradatamyoratest02.dbf'size 50m;

create tablespace test03 datafile 'D:oracleoradatamyoratest03.dbf'size 50m;

create tablespace

test_idx01 datafile 'D:oracleoradatamyoratest_idx01.dbf'size 20m;

create tablespace

test_idx02 datafile 'D:oracleoradatamyoratest_idx02.dbf'size 20m;

create tablespace

test_idx03 datafile 'D:oracleoradatamyoratest_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:test01.dmp buffer=8192000 tables=test:part_01 rows=Y

exp test/test@myora file=e:test02.dmp buffer=8192000 tables=test:part_02 rows=Y

exp test/test@myora file=e:test03.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:test03.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);

通过这个测试，创建好的分区可以直接使用分区拆分功能对现有的分区进行分割，分割时可以指定表空间。因此，在这里建议以后创建分区的命名和

表空间的命名时可以从第二位或者第三位开始命名：如part_01,part_02,part_03.....，这样可以为以后的分区拆分留下扩展空间

(可以按照PB中的DataWindow的思想做)。