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

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

create tablespace test01 datafile �D:"oracle"oradata"myora"test01.dbf�size 50m.

        

        create tablespace test02 datafile �D:"oracle"oradata"myora"test02.dbf�size 50m.

        

        create tablespace test03 datafile �D:"oracle"oradata"myora"test03.dbf�size 50m.

        

        create tablespace 

        test_idx01 datafile �D:"oracle"oradata"myora"test_idx01.dbf�size 20m.

        

        create tablespace 

        test_idx02 datafile �D:"oracle"oradata"myora"test_idx02.dbf�size 20m.

        

        create tablespace 

        test_idx03 datafile �D:"oracle"oradata"myora"test_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�).

        

        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).