Oracle 分区表设计

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

---

1. 概述

分区表是大表管理的关键[1]：

优势：

• 可管理性
• 性能
• 可用性

详细见：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')),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

2.2 List

CREATE TABLE customers (
  id NUMBER,
  region VARCHAR2(20)
)
PARTITION BY LIST (region) (
  PARTITION p_east VALUES ('EAST', 'NORTHEAST'),
  PARTITION p_west VALUES ('WEST', 'SOUTHWEST'),
  PARTITION p_default VALUES (DEFAULT)
);

2.3 Hash

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

2.4 Composite

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE (
    SUBPARTITION p_east VALUES ('EAST'),
    SUBPARTITION p_west VALUES ('WEST'),
    SUBPARTITION p_other VALUES (DEFAULT)
  )
(
  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'))
);

2.5 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('2026-01-01', 'YYYY-MM-DD'))
);

---

3. 分区键选择

3.1 原则

• 查询过滤条件
• 数据分布均匀
• 业务查询模式

3.2 推荐

• 日期：Range
• 区域：List
• ID：Hash

3.3 避免

• 多列频繁
• 函数
• 频繁更新

---

4. 分区操作

4.1 添加

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

4.2 删除

ALTER TABLE sales DROP PARTITION p2024;

4.3 截断

ALTER TABLE sales TRUNCATE PARTITION p2024;

4.4 合并

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

4.5 拆分

ALTER TABLE sales SPLIT PARTITION p2025 AT (TO_DATE('2025-07-01', 'YYYY-MM-DD'))
  INTO (PARTITION p2025_h1, PARTITION p2025_h2);

4.6 交换

ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE sales_2024;

4.7 移动

ALTER TABLE sales MOVE PARTITION p2024 TABLESPACE users COMPRESS;

---

5. 分区索引

5.1 本地索引

CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 本地前缀
CREATE INDEX idx_sales_local ON sales(sale_date, id) LOCAL;

-- 本地非前缀
CREATE INDEX idx_sales_amount ON sales(amount) LOCAL;

5.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;

5.3 选择

类型	维护	性能
本地	简单	分区裁剪
全局	复杂	跨分区

---

6. 分区裁剪

6.1 静态

SELECT * FROM sales WHERE sale_date = DATE '2026-07-21';
-- 仅扫 p2026

6.2 动态

SELECT * FROM sales WHERE sale_date BETWEEN :start AND :end;
-- 运行时裁剪

6.3 验证

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

---

7. 智能分区连接

7.1 Partition-Wise Join

-- 两表相同分区键
SELECT /*+ PQ_DISTRIBUTE(s, PARTITION) */ *
FROM sales s, customers c
WHERE s.customer_id = c.id;

7.2 优势

• 减少数据传输
• 并行执行

---

8. 分区维护

8.1 统计

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'SALES',
  partname => 'P2026',
  granularity => 'ALL'
);

8.2 压缩

ALTER TABLE sales MOVE PARTITION p2024 
  TABLESPACE users COMPRESS FOR OLTP;

详细见：Oracle 表压缩技术。

8.3 归档

-- 1. 交换到独立表
ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE sales_archive_2024;

-- 2. 压缩归档
ALTER TABLE sales_archive_2024 MOVE COMPRESS HIGH;

-- 3. 备份
-- 4. 删除

---

9. 11g+ 特性

9.1 Interval

-- 自动创建分区
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

9.2 Reference

-- 子表跟随父表分区
CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  customer_id NUMBER,
  CONSTRAINT fk_order_cust FOREIGN KEY (customer_id) REFERENCES customers(id)
)
PARTITION BY REFERENCE (fk_order_cust);

9.3 Virtual Column

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  sale_month VARCHAR2(7) GENERATED ALWAYS AS (TO_CHAR(sale_date, 'YYYY-MM')) VIRTUAL
)
PARTITION BY LIST (sale_month) (
  PARTITION p202601 VALUES ('2026-01'),
  PARTITION p202602 VALUES ('2026-02')
);

---

10. 12c+ 特性

10.1 Partial Index

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

CREATE INDEX idx_sales ON sales(id) INDEXING PARTIAL;

10.2 Multi-Column List

-- 12.2+
PARTITION BY LIST (region, country) (
  PARTITION p_us VALUES (('AMERICAS', 'US')),
  ...
);

---

11. 23ai 特性

11.1 Hybrid Partitioned

-- 内部 + 外部分区
CREATE TABLE sales (...)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 VALUES LESS THAN (...) EXTERNAL DEFAULT DIRECTORY ...,
  PARTITION p2025 VALUES LESS THAN (...),
  PARTITION p2026 VALUES LESS THAN (...)
);

---

12. 监控

12.1 分区信息

SELECT 
  table_name, partition_name, partition_position, num_rows, blocks
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

12.2 大小

SELECT 
  segment_name, partition_name, bytes / 1024 / 1024 AS mb
FROM user_segments
WHERE segment_name = 'SALES';

---

13. 常见坑与排错

13.1 全局索引失效

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

-- UPDATE INDEXES
ALTER TABLE sales DROP PARTITION p2024 UPDATE INDEXES;
-- 或重建
ALTER INDEX idx_sales REBUILD;

13.2 热点分区

-- Hash 改善
-- 或子分区

13.3 分区过大

-- SPLIT
ALTER TABLE sales SPLIT PARTITION p2026 AT (...) INTO (...);

---

14. 最佳实践

1. 范围分区日期：典型
2. List 分类：离散
3. Hash 均匀：ID
4. Interval 自动：11g+
5. 本地索引优先：维护
6. 分区裁剪：性能
7. 定期归档：管理
8. 统计信息：准确
9. 压缩历史：空间
10. 文档化：设计

---

15. 参考资料

[1] Oracle Database VLDB and Partitioning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/