DB / Oracle / 性能诊断

Oracle 分区表性能优化

2026.09.06

Oracle 分区表性能优化

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

1. 概述

分区表性能优化关键[1]：

  • 分区裁剪
  • 智能 JOIN
  • 并行
  • 索引

2. 分区裁剪

2.1 静态裁剪

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

2.2 验证

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Partition Start/Stop 表示裁剪

2.3 动态裁剪

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

-- 查看 V$SQL_PLAN

3. 分区智能 JOIN

3.1 Partition-Wise JOIN

-- 同分区方式 JOIN
SELECT /*+ PQ_DISTRIBUTE(s p, PARTITION) */ 
  s.product_id, p.product_name, SUM(s.amount)
FROM sales s, products p
WHERE s.product_id = p.id
GROUP BY s.product_id, p.product_name;

3.2 完整 vs 部分

  • 完整：两表相同分区
  • 部分：一表分区

4. 并行查询

4.1 表级并行

ALTER TABLE sales PARALLEL 8;

SELECT /*+ PARALLEL(s 8) */ * FROM sales s WHERE ...;

4.2 分区并行

-- 每分区并行
SELECT /*+ PARALLEL(s 4) */ * FROM sales s PARTITION(p2026) WHERE ...;

5. 分区索引

5.1 本地索引

CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;
-- 与分区对应
-- 分区裁剪自动应用

5.2 全局索引

CREATE INDEX idx_sales_customer ON sales(customer_id) GLOBAL;
-- 整体索引
-- 分区操作需维护

5.3 选择

  • 查询用分区键：本地
  • 查询不用分区键：全局

6. 分区统计

6.1 收集

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT', 'SALES',
  cascade => TRUE,
  granularity => 'ALL'
);
-- granularity: ALL/GLOBAL/PARTITION/SUBPARTITION

6.2 增量

-- 12c+：仅变化分区
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'SALES', 'INCREMENTAL', 'TRUE');

7. 分区维护

7.1 添加分区

ALTER TABLE sales ADD PARTITION p2027 VALUES LESS THAN (...);

7.2 交换分区

-- 高速加载
ALTER TABLE sales EXCHANGE PARTITION p2026 
  WITH TABLE sales_stage 
  INCLUDING INDEXES;

7.3 截断

ALTER TABLE sales TRUNCATE PARTITION p2025;

7.4 删除

ALTER TABLE sales DROP PARTITION p2025 UPDATE GLOBAL INDEXES;

8. 分区裁剪场景

8.1 Range 分区

SELECT * FROM sales WHERE sale_date = '2026-07-21';
-- 单分区

8.2 List 分区

SELECT * FROM customers WHERE region = 'NY';
-- 单分区

8.3 Hash 分区

SELECT * FROM orders WHERE customer_id = 100;
-- Hash 计算，单分区

8.4 组合分区

SELECT * FROM sales 
WHERE sale_date = '2026-07-21' AND region = 'NY';
-- Range-List，子分区裁剪

9. 性能对比

9.1 全表扫描 vs 分区

全表 1 亿行：100 秒
分区裁剪 1000 万行：10 秒

9.2 索引选择

查询推荐索引
分区键等值本地索引
分区键范围本地索引
非分区键全局索引
分区+非分区复合本地

10. 常见坑与排错

10.1 不裁剪

-- 1. 检查 WHERE 条件
-- 2. 检查分区键
-- 3. 检查执行计划
-- 4. 函数阻止
WHERE EXTRACT(YEAR FROM sale_date) = 2026  -- 不裁剪
WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'  -- 裁剪

10.2 全局索引失效

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

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

10.3 跨分区查询慢

-- 1. 检查 WHERE 是否限定
-- 2. 使用并行
-- 3. 优化索引

11. 最佳实践

  1. 大表分区：> 10GB
  2. Range 日期分区：常用
  3. 本地索引优先：易维护
  4. 分区裁剪验证：性能
  5. 定期添加分区：避免失败
  6. Interval 自动：减少维护
  7. 增量统计：12c+ 高效
  8. 并行查询：大分区
  9. 交换分区加载：高效
  10. 监控分区：状态

12. 参考资料

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