Oracle 12.2 OR 扩展（OR-Expansion）原理与实战：从 CONCATENATION 到 UNION-ALL 的进化

1. 背景：为什么需要 OR 扩展？

在 SQL 查询里，只要出现 WHERE A OR B，优化器会把整个谓词当成一个整体来处理。常见副作用：

• 无法分别利用 A、B 上的索引
• 只能走全表扫描或昂贵的笛卡儿积
• 大表场景性能急剧下降

Oracle 的解决方案就是 OR 扩展（OR-Expansion）：把一条带 OR 的查询改写成多条子查询，再用 UNION ALL 拼回去。只要改写后的成本更低，CBO 就会选择它。

---

2. 演进史：CONCATENATION → UNION-ALL

版本	实现方式	备注
≤ 12.1 R1	CONCATENATION 操作符	内部实现，非标准 SQL，无法共享游标、无法做复杂变换
≥ 12.2	UNION-ALL 操作符	标准 SQL、支持子游标共享、可与其他转换交互、支持各种搜索策略

12.2 引入的新框架带来了 5 个核心增强：

1. 与 子查询解嵌套（subquery unnesting）、视图合并（view merging） 等其他转换自由组合
2. 避免共享查询结构，减少内存占用
3. 支持多种搜索策略（深度优先 / 广度优先 / 贪婪等）
4. 成本注释重用，减少重复计算
5. 直接生成 标准 SQL 语法，便于审计与调优

---

3. 实战演练：一条慢 SQL 的重生

3.1 构造实验数据

以 HR Schema 为例，先加唯一约束再灌 100K 行数据：

-- 1. 唯一约束
ALTER TABLE hr.departments
  ADD CONSTRAINT department_name_uk UNIQUE (department_name);
​
-- 2. 造 100K 条员工记录
DELETE FROM hr.employees WHERE employee_id > 999;
​
DECLARE
  v_cnt NUMBER := 1000;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    INSERT INTO hr.employees
      (employee_id, first_name, last_name, email, hire_date,
       job_id, department_id)
    VALUES
      (v_cnt, 'Doe', 'Doe' || v_cnt, 'doe' || v_cnt || '@example.com',
       DATE '2002-06-07', 'AC_ACCOUNT', 50);
    v_cnt := v_cnt + 1;
  END LOOP;
  COMMIT;
END;
/
​
-- 3. 收集统计信息
EXEC DBMS_STATS.gather_table_stats('hr','employees');

3.2 原始查询

SELECT *
FROM   employees e, departments d
WHERE  (e.email = 'SSTILES' OR d.department_name = 'Treasury')
AND    e.department_id = d.department_id;

未做 OR 扩展时，执行计划只能走 全表扫描 + 哈希连接，成本极高。

3.3 OR 扩展后的等价改写

SELECT *
FROM   employees e, departments d
WHERE  e.email = 'SSTILES'
AND    e.department_id = d.department_id
UNION ALL
SELECT *
FROM   employees e, departments d
WHERE  d.department_name = 'Treasury'
AND    e.department_id = d.department_id;

3.4 新执行计划亮点

-----------------------------------------------------------------------------------------------
| Id| Operation                              | Name             |Rows|Cost(%CPU)|
-----------------------------------------------------------------------------------------------
| 1 |  VIEW                                  | VW_ORE_19FF4E3E  |9K | 122  (5)|
| 2 |   UNION-ALL                            |                  |   |         |
| 3 |    NESTED LOOPS                        |                  | 1 |   4  (0)|
| 4 |     TABLE ACCESS BY INDEX ROWID        | EMPLOYEES        | 1 |   3  (0)|
|*5 |      INDEX UNIQUE SCAN                 | EMP_EMAIL_UK     | 1 |   2  (0)|
| 6 |     TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS      | 1 |   1  (0)|
|*7 |      INDEX UNIQUE SCAN                 | DEPT_ID_PK       | 1 |   0  (0)|
| 8 |    NESTED LOOPS                        |                  |9K | 118  (5)|
| 9 |     TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS      | 1 |   1  (0)|
|*10|      INDEX UNIQUE SCAN                 |DEPARTMENT_NAME_UK| 1 |   0  (0)|
|*11|     TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |9K | 117  (5)|
|*12|      INDEX RANGE SCAN                  |EMP_DEPARTMENT_IX |9K |  35  (6)|
-----------------------------------------------------------------------------------------------

关键改进：

• 分支 1：利用 EMP_EMAIL_UK 唯一索引，一步定位到 1 行，再回表 + 主键索引连接，成本仅 4。
• 分支 2：利用 DEPARTMENT_NAME_UK 唯一索引定位到 Treasury 部门，再通过外键索引 EMP_DEPARTMENT_IX 批量回表，避免全表扫描。
• LNNVL 过滤（ID 11）确保两分支结果不重复。

---

4. 如何确认是否发生了 OR 扩展？

1. 执行计划关键字：出现 UNION-ALL 或视图名 VW_ORE%
2. 10053 trace：搜索 ORE: Using OR Expansion
3. hint 控制
   • 强制启用：USE_CONCAT / OR_EXPAND
   • 强制禁用：NO_USE_CONCAT / NO_OR_EXPAND

---

5. 对比

维度	12.1 及以前	12.2 及以后
实现	内部 CONCATENATION	标准 UNION-ALL
扩展性	差，几乎独立	好，可与其他变换组合
共享	无法共享游标	可共享子游标
语法	非标准	标准 SQL，易读易调优

Oracle 12.2 的 OR 扩展把过去“黑盒”式的 CONCATENATION 改成了白盒、可扩展、可互动的 UNION-ALL 框架，让带 OR 的复杂查询也能飞起来！

---