Oracle-SQL-优化：hint 提示：HASH_AJ 深度解析

1 HASH_AJ 深度应用

HASH_AJ 提示强制优化器使用 ​​Hash Anti-Join​​ 算法处理 NOT EXISTS/NOT IN 子查询

1.1. 执行计划对比

EXPLAIN PLAN FOR SELECT /*+ HASH_AJ */ * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id ); 

-- 执行计划输出 

----------------------------------------- 

| Id | Operation       | Name   | 

----------------------------------------- 

| 0 | SELECT STATEMENT |        | 

| 1 | HASH JOIN ANTI  |        | <-- 强制Hash Anti-Join 

| 2 | TABLE ACCESS FULL| CUSTOMERS | 

| 3 | TABLE ACCESS FULL| ORDERS   | 

-----------------------------------------

1.2. 改写后的SQL

SELECT 
FROM

    customers c
LEFT JOIN

    orders o

ON

    o.cust_id = c.cust_id
WHERE o.cust_id IS NULL; -- 反连接核心条件

1.3. 性能对比（百万级数据测试）

算法	响应时间	内存消耗	CPU消耗
Nested Loop	12.7s	35MB	98%
Hash Anti-Join	0.8s	152MB	45%
Merge Anti-Join	1.2s	78MB	60%

1.4. 联合使用技巧

-- 组合使用HASH_AJ和UNNEST 

SELECT /*+ UNNEST HASH_AJ */ * FROM products p WHERE p.product_id NOT IN ( SELECT product_id FROM discontinued_items ); 

-- 特定子查询提示 

SELECT /*+ NO_UNNEST(@"SEL$2") HASH_AJ(@"SEL$1") */ * FROM employees e WHERE e.employee_id NOT IN ( SELECT /*+ QB_NAME(sub) */ manager_id FROM departments );

2 实战应用场景

场景1：海量数据排除

/* 排除近3个月有购买记录的客户 */

SELECT /*+ HASH_AJ FULL(c) PARALLEL(4) */ c.customer_id, c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date > ADD_MONTHS(SYSDATE, -3) )

 

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. 实时监控 

ALTER 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. ​​渐进优化​​：
   原始执行计划 → UNNEST/NO_UNNEST → HASH_AJ/MERGE_AJ
3. ​​版本适配​​：
   • Oracle 12c+：优先使用VECTOR_TRANSFORM和USE_HASH_AGGREGATION
   • Oracle 19c：利用自适应特性ADAPTIVE_PLAN
4. ​​文档记录​​：所有手动提示需注释原因和测试数据

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