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

文章作者 100test 发表时间 2007:03:14 13:28:28
来源 100Test.Com百考试题网

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

create tablespace test01 datafile D:\oracle\oradata\myora\test01.dbfsize 50m. create tablespace test02 datafile D:\oracle\oradata\myora\test02.dbfsize 50m. create tablespace test03 datafile D:\oracle\oradata\myora\test03.dbfsize 50m. create tablespace test_idx01 datafile D:\oracle\oradata\myora\test_idx01.dbfsize 20m. create tablespace test_idx02 datafile D:\oracle\oradata\myora\test_idx02.dbfsize 20m. create tablespace test_idx03 datafile D:\oracle\oradata\myora\test_idx03.dbfsize 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). 0select Min(callid),Max(callid),Count(*) from test partition(part_01). 0select Min(callid),Max(callid),Count(*) from test partition(part_02). 0select Min(callid),Max(callid),Count(*) from test partition(part_03). 0select Min(callid),Max(callid),Count(*) from test Where callid < 10000. 0select Min(callid),Max(callid),Count(*) from test Where callid > 10000 And callid < 30000. 0select 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 0drop partition part03 . 0select Min(aa),Max(aa),Count(*) from test partition(part_01). 0select Min(aa),Max(aa),Count(*) from test partition(part_02). 0select Min(aa),Max(aa),Count(*) from test partition(part_03). --本条将报错，提示无此分区 0select Min(aa),Max(aa),Count(*) from test Where aa < 3000000. 0select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000. 0select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000. --将无数据显示 alter table test add partition part03 values less than(maxvalue) tablespace test03. 0select Min(aa),Max(aa),Count(*) from test partition(part_01). 0select Min(aa),Max(aa),Count(*) from test partition(part_02). 0select Min(aa),Max(aa),Count(*) from test partition(part_03). --将无数据 0select Min(aa),Max(aa),Count(*) from test Where aa < 3000000. 0select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000. 0select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000. --将无数据显示 imp test/test@myora FILE=e:\test03.dmp TABLES = (cdr:part_03) IGNORE=y 0select Min(aa),Max(aa),Count(*) from test partition(part_01). 0select Min(aa),Max(aa),Count(*) from test partition(part_02). 0select Min(aa),Max(aa),Count(*) from test partition(part_03). 0select Min(aa),Max(aa),Count(*) from test Where aa < 3000000. 0select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000. 0select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000. 0delete from test partition(part_02). commit. 0select Min(aa),Max(aa),Count(*) from test partition(part_01). 0select Min(aa),Max(aa),Count(*) from test partition(part_02). --将无数据显示 0select Min(aa),Max(aa),Count(*) from test partition(part_03). 0select Min(aa),Max(aa),Count(*) from test Where aa < 3000000. 0select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000. --将无数据显示 0select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000. insert into test values(40000,test). commit. 0select Min(aa),Max(aa),Count(*) from test partition(part_01). 0select Min(aa),Max(aa),Count(*) from test partition(part_02). --将显示1条数据 0select Min(aa),Max(aa),Count(*) from test partition(part_03). 0select Min(aa),Max(aa),Count(*) from test Where aa < 3000000. 0select Min(aa),Max(aa),Count(*) from test Where aa > 3000000 And aa < 6000000. --将显示1条数据 0select Min(aa),Max(aa),Count(*) from test Where aa >= 6000000. 0select count(*) from test where aa > 1000001. --将有大量数据统计出 0select 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).