Oracle 表分区策略详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

表分区策略决定分区设计[1]：

详细见：Oracle 分区表设计、Oracle 分区表设计详解。

---

2. 分区策略

2.1 时间分区

-- Range
CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
  PARTITION p2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'))
);

-- Interval（11g+）自动
CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
  PARTITION p0 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

2.2 区域分区

CREATE TABLE customers (
  id NUMBER,
  region VARCHAR2(20)
)
PARTITION BY LIST (region) (
  PARTITION p_east VALUES ('NY', 'Boston', 'Miami'),
  PARTITION p_west VALUES ('LA', 'SF', 'Seattle'),
  PARTITION p_central VALUES ('Chicago', 'Dallas'),
  PARTITION p_other VALUES (DEFAULT)
);

2.3 哈希分区

CREATE TABLE orders (
  id NUMBER,
  customer_id NUMBER
)
PARTITION BY HASH (customer_id) PARTITIONS 8;

2.4 复合

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  region VARCHAR2(20)
)
PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST (region)
    SUBPARTITION TEMPLATE (
      SUBPARTITION east VALUES ('NY', 'Boston'),
      SUBPARTITION west VALUES ('LA', 'SF'),
      SUBPARTITION other VALUES (DEFAULT)
    ) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

2.5 Reference

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  customer_id NUMBER
)
PARTITION BY HASH (customer_id) PARTITIONS 4;

CREATE TABLE order_items (
  item_id NUMBER,
  order_id NUMBER,
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders
)
PARTITION BY REFERENCE (fk_oi_order);

---

3. 选择策略

3.1 时间

- Range / Interval
- 日志 / 销售 / 事件
- 自动归档

3.2 区域

- List
- 业务隔离
- 区域统计

3.3 均匀

- Hash
- 任意键
- 负载均衡

3.4 多维

- Composite
- Range + List / Hash
- 灵活

---

4. 分区键选择

4.1 原则

- 查询常用
- 等值 / 范围
- 高基数（Hash）
- 业务相关

4.2 限制

- 单列或多列（30 列）
- 不能是 LONG
- 不能是 ROWID
- 不能是对象类型

---

5. 分区数

5.1 数量

- 单分区：10-50GB
- 太多：管理开销
- 太少：无益

5.2 增长

- Interval 自动
- List 手动添加
- Hash 2 的幂

---

6. 索引

6.1 本地

-- 本地（推荐）
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 分区索引
CREATE INDEX idx_sales_local ON sales(sale_date, amount) LOCAL
  STORE IN (users, users2);

6.2 全局

-- 全局
CREATE INDEX idx_sales_cust ON sales(customer_id) GLOBAL;

-- 分区
CREATE INDEX idx_sales_cust ON sales(customer_id) GLOBAL
  PARTITION BY HASH (customer_id) PARTITIONS 4;

6.3 选择

- 本地：维护成本低，分区独立
- 全局：非分区键查询

详细见：Oracle 索引优化策略详解。

---

7. 操作

7.1 添加

ALTER TABLE sales ADD PARTITION p2027 VALUES LESS THAN (TO_DATE('2028-01-01', 'YYYY-MM-DD'));

7.2 删除

ALTER TABLE sales DROP PARTITION p2020;

7.3 截断

ALTER TABLE sales TRUNCATE PARTITION p2020;

7.4 合并

ALTER TABLE sales MERGE PARTITIONS p2024, p2025 INTO PARTITION p2024_2025;

7.5 拆分

ALTER TABLE sales SPLIT PARTITION pmax AT (TO_DATE('2028-01-01', 'YYYY-MM-DD'))
  INTO (PARTITION p2027, PARTITION pmax);

7.6 交换

CREATE TABLE sales_2024 AS SELECT * FROM sales WHERE 1=0;
ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE sales_2024;

7.7 移动

ALTER TABLE sales MOVE PARTITION p2024 TABLESPACE users COMPRESS ONLINE;

---

8. 分区裁剪

8.1 静态

SELECT * FROM sales WHERE sale_date = DATE '2025-07-21';
-- 仅扫 p2025

8.2 动态

SELECT * FROM sales WHERE sale_date = :date;
-- 运行时裁剪

8.3 验证

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));
-- PARTITION RANGE SINGLE

详细见：Oracle 执行计划详解。

---

9. Partition Wise Join

SELECT /*+ PQ_DISTRIBUTE(s d NONE) */ *
FROM sales s, dim_date d
WHERE s.sale_date = d.date_value;
-- 分区连接

---

10. 统计

10.1 收集

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'SALES', cascade => TRUE);

-- 分区
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'SALES', 
  partname => 'P2025', cascade => TRUE);

-- 增量
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'SALES', 'INCREMENTAL', 'TRUE');

---

11. 归档

11.1 交换

-- 历史分区归档
ALTER TABLE sales EXCHANGE PARTITION p2020 WITH TABLE sales_2020_archive;
ALTER TABLE sales DROP PARTITION p2020;

11.2 压缩

ALTER TABLE sales 
  MOVE PARTITION p2020 
  COMPRESS FOR ARCHIVE HIGH 
  ONLINE;

详细见：Oracle 表压缩技术详解。

---

12. 12c+ 新特性

12.1 Partial Index

CREATE TABLE sales (...) PARTITION BY RANGE (sale_date) (
  PARTITION p2024 ... INDEXING OFF,
  PARTITION p2025 ... INDEXING ON
);

CREATE INDEX idx_sales ON sales (...) LOCAL INDEXING PARTIAL;

12.2 Online

ALTER TABLE sales MOVE PARTITION p2024 ONLINE;
ALTER TABLE sales SPLIT PARTITION pmax AT (...) ONLINE;

12.3 Multi-Column

PARTITION BY RANGE (a, b) (...)

---

13. 监控

13.1 表

SELECT table_name, partitioning_type, partition_count, def_tablespace_name
FROM user_part_tables;

13.2 分区

SELECT table_name, partition_name, tablespace_name, num_rows, last_analyzed
FROM user_tab_partitions;

13.3 子分区

SELECT * FROM user_tab_subpartitions;

---

14. 常见坑与排错

14.1 全局索引失效

ALTER TABLE sales DROP PARTITION p2020;
-- 全局索引失效

ALTER INDEX idx_sales_cust REBUILD;
-- 或
ALTER TABLE sales DROP PARTITION p2020 UPDATE INDEXES;

14.2 分区键不当

- 无裁剪
- 性能差
- 重新设计

14.3 分区数过多

- 管理开销
- 限制
- 合并

---

15. 最佳实践

1. 大表分区：> 10GB
2. 时间 Range/Interval：自动
3. Hash 均匀：负载
4. 本地索引：维护
5. 分区裁剪：查询
6. 增量统计：性能
7. 归档压缩：空间
8. Online 操作：业务
9. Partial Index：12c+
10. 监控：使用

---

16. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Partitioning” https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/