Oracle資料庫表範圍分割槽策略測試過程

fengzj發表於2008-12-28
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的思想做）。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-523083/，如需轉載，請註明出處，否則將追究法律責任。

相關文章