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 range(aa)(
  Partition part_01 values less than(10000) Tablespace test01,
  Partition part_02 values less than(30000) Tablespace test02,
  Partition part_03 values less than(maxvalue) Tablespace test03
);

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 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(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 < 10000;
select Min(aa),Max(aa),Count(*) from test Where aa > 10000 And aa < 30000;
select Min(aa),Max(aa),Count(*) from test Where aa >= 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 test drop partition part_03;

-- 新增分區
alter table test add partition part_03 values less than(maxvalue) tablespace test03;

-- 匯入資料恢復 part_03
imp test/test@myora FILE=e:\test03.dmp TABLES=(test:part_03) IGNORE=y

分區資料刪除與重新插入

delete from test partition(part_02);
commit;
insert into test values(40000,'test');
commit;

分區分割測試

alter table test split partition part_02 at (50000) into (
  partition part_02,
  partition part_06 tablespace test01p2
);

建議與心得

• 分區命名建議預留擴充空間，如 part_01, part_02, part_03...，方便後續 SPLIT 操作插入新分區
• 分區表操作需注意 MAXVALUE 分區必須為最後一個
• 本地索引與表分區對齊，維護較簡單