Oracle 分區表完整指南：範圍、哈希、列表、複合分區實戰

分區表是大數據量場景下提升查詢效能、降低維護成本的核心技術。本文涵蓋四大分區策略語法、查詢分區元數據、統計分區大小與記錄數的完整實操案例。

四大分區類型語法速查

1. 範圍分區——按時間/數值區間

CREATE TABLE sales2 (
  product_id VARCHAR2(5),
  sales_date DATE NOT NULL,
  sales_cost NUMBER(10)
) PARTITION BY RANGE (sales_date) (
  PARTITION p1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('2004-01-01','YYYY-MM-DD')),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

2. 哈希分區——均勻分佈，適合無明顯範圍鍵

-- 指定分區數，系統自動命名
CREATE TABLE employee (
  emp_id NUMBER(4), emp_name VARCHAR2(14), emp_address VARCHAR2(15), department VARCHAR2(10)
) PARTITION BY HASH (department) PARTITIONS 4;

-- 自定義分區名
CREATE TABLE my_emp (
  empno NUMBER(4), ename VARCHAR2(15)
) PARTITION BY HASH (empno) (
  PARTITION part_1, PARTITION part_2
);

3. 複合分區——範圍+哈希二維劃分

CREATE TABLE sales3 (
  product_id VARCHAR2(5), sales_date DATE NOT NULL, sales_cost NUMBER(10)
) PARTITION BY RANGE (sales_date)
SUBPARTITION BY HASH (product_id) SUBPARTITIONS 5 (
  PARTITION p1 VALUES LESS THAN (DATE '2001-04-01'),
  PARTITION p2 VALUES LESS THAN (DATE '2001-07-01'),
  PARTITION p3 VALUES LESS THAN (DATE '2001-09-01'),
  PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

4. 列表分區——離散值精確映射

CREATE TABLE employer (
  emp_id NUMBER(4), emp_name VARCHAR2(14), emp_address VARCHAR2(15), department VARCHAR2(10)
) PARTITION BY LIST (emp_address) (
  PARTITION north VALUES ('芝加哥'),
  PARTITION west  VALUES ('舊金山','洛杉磯'),
  PARTITION south VALUES ('亞特蘭大','達拉斯','休斯頓'),
  PARTITION east  VALUES ('紐約','波士頓')
);

分區管理常用查詢

查看表分區屬性

SELECT a.table_name, a.partitioned, b.partition_count, c.column_name
FROM user_tables a, user_part_tables b, user_part_key_columns c
WHERE a.table_name = 'EMPLOYEE'
  AND b.table_name = 'EMPLOYEE'
  AND c.name = 'EMPLOYEE';

查看各分區段大小

SELECT segment_name, partition_name
FROM user_segments
WHERE segment_name = 'EMPLOYEE'
ORDER BY 2 DESC;

統計表總大小與記錄數

SELECT segment_name, SUM(bytes)/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = 'EMPLOYEE'
GROUP BY segment_name;

SELECT COUNT(*) FROM employee;

合理選擇分區鍵與策略，配合分區剪枝，可實現數量級效能提升。生產環境建議結合 INTERVAL 自動分區、分區級索引、分區交換載入等進階特性進一步優化運維效率。