Oracle 表分區完整指南：範圍、雜湊、列表、複合分區與維護操作

Oracle 表分區完整指南：範圍、雜湊、列表、複合分區與維護操作

分區將大表切割為較小、可獨立管理的分區，提升查詢效能 (分區修剪)、簡化維護 (歸檔/清除)、分散 I/O。本文涵蓋四大基本分區法、複合分區、分區維護 (新增/截斷/合併) 完整語法與範例。

分區類型總覽

1. 範圍分區

CREATE TABLE drawlist (
  draw_dt DATE NOT NULL
)
PARTITION BY RANGE (draw_dt) (
  PARTITION part_1 VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')),
  PARTITION part_2 VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD')),
  PARTITION part_3 VALUES LESS THAN (MAXVALUE)
);

• VALUES LESS THAN 不包含邊界值。
• MAXVALUE 必須為最後一個分區。

2. 雜湊分區

CREATE TABLE hash_table (
  id NUMBER,
  data VARCHAR2(100)
)
PARTITION BY HASH (id) PARTITIONS 4; -- 自動建立 4 個分區

3. 列表分區

CREATE TABLE area (
  code INTEGER,
  name VARCHAR2(10)
)
PARTITION BY LIST (code) (
  PARTITION part_north VALUES (102200, 102202, 102203),
  PARTITION part_south VALUES (164300, 164302, 164303)
);

4. 複合分區：範圍-列表 (Range-List)

CREATE TABLE range_list (
  dt_date DATE,
  code INTEGER
)
PARTITION BY RANGE (dt_date)
SUBPARTITION BY LIST (code) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')) (
    SUBPARTITION p_2023_north VALUES (102200),
    SUBPARTITION p_2023_south VALUES (164300)
  ),
  PARTITION p_2024 VALUES LESS THAN (MAXVALUE) (
    SUBPARTITION p_2024_north VALUES (102200),
    SUBPARTITION p_2024_south VALUES (164300)
  )
);

分區查詢

SELECT * FROM drawlist PARTITION (part_1);     -- 僅掃描分區 1
SELECT * FROM range_list SUBPARTITION (p_2023_north); -- 僅掃描子分區

分區維護操作

新增分區 (需高於現有最高邊界)

ALTER TABLE dlist ADD PARTITION part_4 VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD'));
-- 若 MAXVALUE 分區已存在，需先 DROP 再 ADD

截斷分區 (保留結構，清除資料，釋放空間)

ALTER TABLE dlist TRUNCATE PARTITION part_1;

合併分區 (相鄰、低邊界合併到高邊界)

ALTER TABLE dlist MERGE PARTITIONS part_2, part_4 INTO PARTITION part_4;
-- part_2 消失，資料合併至 part_4

• 不可將高邊界合併到低邊界。
• 合併後需重建本地索引 (UNUSABLE 狀態)。

刪除分區 (連資料一併刪除)

ALTER TABLE dlist DROP PARTITION part_3;

分區修剪驗證

EXPLAIN PLAN FOR SELECT * FROM drawlist WHERE draw_dt >= DATE '2010-01-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 查看 PSTART/PSTOP 非 1/1048575 即為分區修剪生效