DB / Oracle / SQL 开发

Oracle 分区表（Partitioned Table）

2026.09.06

Oracle 分区表（Partitioned Table）

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

1. 概述

分区表 将大表拆分为小的管理单元[1]：

优势：

  • 提升查询性能（分区裁剪）
  • 简化维护（分区操作）
  • 提高可用性
  • 平衡 I/O

2. Range 分区

2.1 创建

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  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 p2027 VALUES LESS THAN (TO_DATE('2028-01-01', 'YYYY-MM-DD')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

2.2 特点

  • 按值范围分区
  • 适合日期/数值
  • 最常用

3. List 分区

3.1 创建

CREATE TABLE customers (
  id NUMBER,
  name VARCHAR2(100),
  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)
);

3.2 特点

  • 按枚举值分区
  • 适合地区/类别

4. Hash 分区

4.1 创建

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

4.2 特点

  • 哈希分布
  • 数据均匀
  • 适合无明显规律列

5. Composite 分区

5.1 Range-Hash

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
  SUBPARTITION BY HASH (customer_id)
  SUBPARTITIONS 4 (
    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'))
  );

5.2 Range-List

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST (region) (
    PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')) (
      SUBPARTITION p2025_east VALUES ('NY', 'Boston'),
      SUBPARTITION p2025_west VALUES ('LA', 'SF')
    ),
    PARTITION p2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD')) (
      SUBPARTITION p2026_east VALUES ('NY', 'Boston'),
      SUBPARTITION p2026_west VALUES ('LA', 'SF')
    )
  );

6. Interval 分区（11g+）

6.1 自动创建

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
  PARTITION p_initial VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);
-- 自动按月创建新分区

7. System 分区

CREATE TABLE system_part_table (
  id NUMBER,
  name VARCHAR2(100)
)
PARTITION BY SYSTEM (
  PARTITION p1,
  PARTITION p2,
  PARTITION p3
);

-- 插入必须指定分区
INSERT INTO system_part_table PARTITION(p1) VALUES (1, 'Alice');

8. Reference 分区

-- 父表
CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  order_date DATE,
  customer_id NUMBER
)
PARTITION BY RANGE (order_date) (
  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'))
);

-- 子表（继承父表分区）
CREATE TABLE order_items (
  id NUMBER,
  order_id NUMBER,
  product_id NUMBER,
  CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id)
)
PARTITION BY REFERENCE (fk_order);

9. Virtual Column 分区

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  sale_year AS (EXTRACT(YEAR FROM sale_date)),
  amount NUMBER
)
PARTITION BY RANGE (sale_year) (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);

10. 分区维护

10.1 添加分区

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

10.2 删除分区

ALTER TABLE sales DROP PARTITION p2025;

10.3 截断分区

ALTER TABLE sales TRUNCATE PARTITION p2025;

10.4 合并分区

ALTER TABLE sales MERGE PARTITIONS p2025, p2026 
  INTO PARTITION p2025_2026;

10.5 拆分分区

ALTER TABLE sales SPLIT PARTITION p2025_2026 
  AT (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
  INTO (PARTITION p2025, PARTITION p2026);

10.6 交换分区

-- 分区与表交换
CREATE TABLE sales_2025 AS SELECT * FROM sales WHERE 1=0;

ALTER TABLE sales EXCHANGE PARTITION p2025 
  WITH TABLE sales_2025;

10.7 移动分区

ALTER TABLE sales MOVE PARTITION p2025 TABLESPACE users;

10.8 重命名分区

ALTER TABLE sales RENAME PARTITION p2025 TO p_old;

11. 分区索引

11.1 本地索引

-- 与分区表对应
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 每个分区独立索引
-- 维护简单
-- 分区操作不影响其他

11.2 全局索引

-- 独立于分区
CREATE INDEX idx_sales_customer ON sales(customer_id) GLOBAL;

-- 可分区
CREATE INDEX idx_sales_customer ON sales(customer_id) GLOBAL
  PARTITION BY RANGE (customer_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
  );

11.3 选择

类型维护性能适用
本地简单分区裁剪好OLAP
全局复杂整体查询好OLTP

12. 分区裁剪

12.1 静态裁剪

-- WHERE 条件包含分区键
SELECT * FROM sales 
WHERE sale_date BETWEEN '2026-01-01' AND '2026-12-31';
-- 仅扫描 p2026 分区

12.2 动态裁剪

-- 子查询条件
SELECT * FROM sales 
WHERE sale_date IN (SELECT date_col FROM other_table);
-- 运行时裁剪

13. 查看分区

-- 表分区
SELECT table_name, partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES';

-- 分区键
SELECT name, column_name, column_position
FROM user_part_key_columns
WHERE name = 'SALES';

-- 子分区
SELECT * FROM user_tab_subpartitions WHERE table_name = 'SALES';

14. 常见坑与排错

14.1 ORA-14400: 分区不存在

-- 插入数据无对应分区
-- 1. 添加分区
-- 2. 或使用 MAXVALUE 分区
-- 3. 或使用 Interval 分区

14.2 ORA-14402: 更新分区键

-- 行跨分区移动
-- 启用 ROW MOVEMENT
ALTER TABLE sales ENABLE ROW MOVEMENT;

14.3 全局索引失效

-- 分区操作导致
ALTER TABLE sales DROP PARTITION p2025;
-- 全局索引失效

-- 修复
ALTER INDEX idx_sales_customer REBUILD;
-- 或操作时维护
ALTER TABLE sales DROP PARTITION p2025 UPDATE GLOBAL INDEXES;

14.4 ORA-14758: 不能删除最后一个分区

-- Interval 分区
-- 修复：先 MERGE 或使用其他方法

15. 最佳实践

  1. 大表分区：> 10GB
  2. Range 日期分区：常用
  3. Hash 均匀分布：无明显规律
  4. List 枚举值：地区/类别
  5. Interval 自动：减少维护
  6. 本地索引优先：维护简单
  7. 定期添加分区：避免失败
  8. 老分区归档：节省空间
  9. 使用分区裁剪：性能
  10. 统计信息及时：CBO

16. 参考资料

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