同一个SQL的三个执行计划PARTITION HASH/ITERATOR/SUBQUERY

在日常 Oracle 性能优化中，我们经常会遇到一个看似“诡异”的现象：同一条 SQL，在不同情况下会产生完全不同的执行计划。

尤其是在分区表（特别是复合分区：RANGE + HASH）场景下，常见如下三种执行方式：

PARTITION HASH ALL
PARTITION HASH ITERATOR
PARTITION HASH SUBQUERY

更关键的是：这三种执行方式的性能差异可能是数量级的。

本文将从执行机制、优化器决策逻辑以及实战优化角度，系统讲清楚这三种执行计划的本质区别，以及如何稳定命中最优执行路径。

案例

某客户Oracle 环境，同一个SQL因有个表的数据是动态加载，另一个表的传的是变量，有三个执行计划，最下面的最快。除了nl join和hash join，还有关于分区的访问。

执行计划关于分区访问分别如下：

PARTITION RANGE SINGLE
  └── PARTITION HASH ALL
PARTITION RANGE SINGLE
  └── PARTITION HASH ITERATOR
PARTITION RANGE SINGLE
  └── PARTITION HASH SUBQUERY

❗ 真正决定性能差异的，是下面那一层 HASH 子分区访问策略。

执行计划应该这样理解：

第一层：RANGE 分区（大分区）
第二层：HASH 子分区（小分区）

所以结构本质是：

一个 RANGE 分区
  └── 多个 HASH 子分区

真正拉开差距的地方在这里也就是：

类型	含义	性能
`HASH ALL`	这个 RANGE 分区下的所有 HASH 子分区全扫	❌ 慢
`HASH ITERATOR`	扫多个 HASH 子分区（循环）	⚠️ 中
`HASH SUBQUERY`	精准命中少量 HASH 子分区	✅ 快

三种执行方式本质区别

1️⃣ `PARTITION HASH ALL`

👉 最差的一种（你图上第一段）

含义：扫描所有分区
典型场景：
- 分区键没有参与过滤条件
- 或优化器无法推导分区键
结果：
- I/O 最大
- 即使数据很少，也要扫所有分区

📌 类似：

select * from t where non_partition_col = 100;

2️⃣ `PARTITION HASH ITERATOR`

👉 中等（你图中第二段）

含义：逐个分区迭代访问
优化器知道“要访问部分分区”，但：
- 不能一次性确定全部分区
- 需要在执行过程中逐步计算

📌 特点：

比 ALL 好（减少扫描）
但仍然有“循环访问分区”的开销

3️⃣ `PARTITION HASH SUBQUERY`（关键）

👉 最快（你图最下面）

含义： 通过子查询结果，精确计算出要访问的分区

📌 核心能力：

✅ 动态分区裁剪（Dynamic Partition Pruning）

为什么 SUBQUERY 最快

✅ 1. 精确分区裁剪（只访问必要分区）

执行逻辑类似：

where partition_key in (subquery结果)

优化器做了：

👉 先执行子查询 👉 得到具体值 👉 只访问对应 hash 分区

PARTITION HASH SUBQUERY 通常出现在：

IN (subquery)
EXISTS
半连接（semi join）

优化器会：

👉 把子查询结果“物化/缓存” 👉 用结果直接定位分区

当然有时还会使用bloom filter. (_bloom_pruning_enabled true)，子查询view化, 执行计划中看到“PART JOIN FILTER CREATE” + view的字样。

触发 SUBQUERY 的关键条件

分区键来自子查询
子查询结果集较小，“先算子查询，再裁剪分区” 更划算
使用等值匹配，无函数和隐式转换
子查询可物化

优化方法

方法1：改写成 IN 子查询
方法2: 控制子查询返回行数
方法3: 使用SQL hint

相关的参数

      NUM N_HEX      NAME                                                     VALUE                          DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      3728   E90      _unnest_subquery                                         TRUE                           enables unnesting of complex subqueries
      3729   E91      _optimizer_unnest_all_subqueries                         TRUE                           enables unnesting of every type of subquery
      3843   F03      _subquery_pruning_cost_factor                            20                             subquery pruning cost factor
      3844   F04      _subquery_pruning_reduction                              50                             subquery pruning reduction factor
      3845   F05      _subquery_pruning_enabled                                TRUE                           enable the use of subquery predicates to perform pruning
      3846   F06      _subquery_pruning_mv_enabled                             FALSE                          enable the use of subquery predicates with MVs to perform pruning

— over —

案例