Oracle-SQL-优化：hint 提示：UNNEST、NO_UNNEST 深度解析

Oracle 优化器提示：UNNEST、NO_UNNEST 

1 UNNEST 与 NO_UNNEST

UNNEST（子查询展开）是 Oracle 优化器处理子查询的核心技术，相关提示用于干预优化器的自动选择。

1.1. 子查询展开原理

-- 未展开的子查询

SELECT * FROM employees e WHERE e.department_id IN ( SELECT d.department_id FROM departments d WHERE d.location_id = 1700 );

-- 展开后等价于

SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700;

1.2. 使用场景对比

​​提示​​	执行计划	适用场景	风险
UNNEST	嵌套循环/Hash Join	1. 大表关联小表<br>2. 子查询结果集小<br>3. 关联条件选择性高	可能导致笛卡尔积爆炸
NO_UNNEST	Filter操作	1. 子查询非常复杂<br>2. 关联条件无索引<br>3. 执行路径已最优	可能造成性能瓶颈

1.3. 使用示例

-- 强制不展开

SELECT /*+ NO_UNNEST */ * FROM orders o WHERE EXISTS ( SELECT 1 FROM order_items i WHERE i.order_id = o.order_id AND i.quantity > 100 );

-- 强制展开

SELECT /*+ UNNEST */ e.* FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees );

1.4. 嵌套类型转换

Oracle 支持三种嵌套方式：

-- 1. 相关子查询（需手动UNNEST）

SELECT /*+ UNNEST(@subq) */ * FROM employees e WHERE salary > (SELECT /*+ QB_NAME(subq) */ AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id)

-- 2. 非相关子查询（默认自动UNNEST）

SELECT * FROM products WHERE category IN (SELECT category FROM premium_categories)

-- 3. EXISTS子查询

SELECT /*+ NO_UNNEST */ * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)

2 实战应用场景

场景1：多层嵌套优化

/* 优化多层嵌套 */

SELECT /*+ UNNEST(@sub1) NO_UNNEST(@sub2) */ * FROM main_table m WHERE m.id IN ( SELECT /*+ QB_NAME(sub1) */ id FROM ( SELECT /*+ QB_NAME(sub2) MATERIALIZE */ DISTINCT col FROM detail_table ) )

 

场景2：参数化查询

VAR dept_id NUMBER = 80;

SELECT /*+ NO_UNNEST */ e.* FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department_id = :dept_id )

3 使用禁忌

1. ​​NOT IN + NULL 警告​​

   -- 危险：NOT IN子查询包含NULL时返回空结果 SELECT * FROM tableA WHERE col NOT IN (SELECT col FROM tableB) -- tableB.col有NULL时无结果 -- 安全方案 SELECT * FROM tableA a WHERE NOT EXISTS ( SELECT 1 FROM tableB b WHERE b.col = a.col )

    

2. ​​基数估计陷阱​​

   /* 错误使用导致性能下降 */ SELECT /*+ HASH_AJ */ * FROM tiny_table t WHERE NOT EXISTS ( SELECT 1 FROM huge_table h WHERE h.id = t.id -- 小表关联大表 );

    

    

4 诊断工具

-- 1. 查询执行计划

EXPLAIN PLAN FOR SELECT /*+ HASH_AJ */ ... ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 实时监控 A

LTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ MONITOR HASH_AJ */ ... FROM ...

-- 查看监控结果

SELECT * FROM V$SQL_MONITOR WHERE sql_text LIKE '%HASH_AJ%';

 

5 最佳实践原则

1. ​​测试驱动​​：所有提示需通过SQL性能分析器（SPA）验证
2. ​​渐进优化​​：
    

   text

   复制

   原始执行计划 → UNNEST/NO_UNNEST → HASH_AJ/MERGE_AJ

    

    
3. ​​版本适配​​：
   • Oracle 12c+：优先使用VECTOR_TRANSFORM和USE_HASH_AGGREGATION
   • Oracle 19c：利用自适应特性ADAPTIVE_PLAN
4. ​​文档记录​​：所有手动提示需注释原因和测试数据

通过合理组合这些提示，可在TP场景下提升20-300倍性能，但需结合业务数据特征谨慎使用。