多表使用use_hash hint
oracle的online document里面，对use_hash的hint语法是这样描述的：
代码语言：javascript
复制
/*+ USE_HASH ( [ @ queryblock ] tablespec [ tablespec ]... ) */而大部分的开发人员也确实是这样写的: use_hash(a b) ，这个确实没问题。
当关联的表超过2个的时候，写成use_hash(a b c d)有没有问题呢？
我们先来看一个test case，这个案例根据客户真实案例改编，模拟的是在表关联条件复杂的情况下，优化器对表关联后的结果集估值过小，可能使用错误的执行计划，希望通过增加use_hash hint来优化SQL。
--创建4个表
create table tv as select rownum as id,a.* from dba_objects a;
create table tt as select * from tv;
create table tw as select * from tv;
create table tu as select * from tv;
--收集统计信息
exec dbms_stats.gather_table_stats(user,'tw');
exec dbms_stats.gather_table_stats(user,'tt');
exec dbms_stats.gather_table_stats(user,'tu');
exec dbms_stats.gather_table_stats(user,'tv');
SQL：
select /*+ use_hash(u v t w) */
count(*) from tv v,tu u,tw w,tt t
where
t.id=v.id and t.object_name=upper(v.object_name) and
w.id=u.id and
v.created between t.created and t.last_ddl_time and
v.created between u.created and u.last_ddl_time and
t.object_id=w.object_id and w.created=v.created;
真实案例的情况是：SQL正常执行时间3.4分钟，某天TV表delete一些记录后，执行了将近20分钟还没有完成，而其中最重要的变化就是执行计划其中的一个hash join变成了nested loops，虽然hint中已经指定全部表要use_hash。----19c 直接就是NL
这个模拟的SQL展示的就是真实案例出现异常的情况。其中一个步骤使用了nested loops，大概要执行4分钟左右才能完（测试时可以cancel），全部hash的执行计划不到1秒。
当前hint生成的执行计划：
SQL Plan Monitoring Details (Plan Hash Value=1666341298)
=============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
=============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 576 | +557 | 1 | 1 | | | | | . | . | | |
| 1 | SORT AGGREGATE | | 1 | | 3 | +1130 | 1 | 1 | | | | | . | . | | |
| 2 | HASH JOIN | | 1 | 2077 | 1129 | +4 | 1 | 28841 | 14366 | 2GB | 14366 | 2GB | 35MB | 2GB | | |
| 3 | NESTED LOOPS | | 662 | 1674 | 1127 | +4 | 1 | 64M | | | | | . | . | | |
| 4 | HASH JOIN | | 1 | 1271 | 1127 | +4 | 1 | 28841 | | | | | 8MB | . | | |
| 5 | TABLE ACCESS FULL | TV | 73614 | 403 | 1 | +4 | 1 | 73614 | 740 | 12MB | | | . | . | | |
| 6 | TABLE ACCESS FULL | TT | 73614 | 403 | 1127 | +4 | 1 | 73614 | 740 | 12MB | | | . | . | | |
| 7 | TABLE ACCESS FULL | TU | 6374 | 403 | 1130 | +1 | 28841 | 64M | 21M | 325GB | | | . | . | | |
| 8 | TABLE ACCESS FULL | TW | 73614 | 403 | 1 | +1130 | 1 | 73614 | 740 | 12MB | | | . | . | | |
=============================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"."ID"="U"."ID" AND "T"."OBJECT_ID"="W"."OBJECT_ID" AND
"W"."CREATED"="V"."CREATED")
4 - access("T"."ID"="V"."ID" AND "T"."OBJECT_NAME"=UPPER("V"."OBJECT_NAME")
)
filter(("V"."CREATED"<="T"."LAST_DDL_TIME" AND
"V"."CREATED">="T"."CREATED"))
7 - filter(("V"."CREATED"<="U"."LAST_DDL_TIME" AND
"V"."CREATED">="U"."CREATED"))
Oracle认为hash join 后结果为0行，所以U V之间NL join也是0行，但实际不是0行，所以导致NL重复扫描U表
这个执行计划出现了nested loops的情况，没有按照hint的指示全部使用hash_join，说明这种hint的写法确实是有问题的。
那么，正确的写法是怎样的呢？
问题的关键在于：
多表的use_hash，一定要配合leading的hint使用。
根据sql的关联条件，我们增加leading的hint再测试一下：
select /*+ leading(v t w u) use_hash(u v t w) */
count(*) from tv v,tu u,tw w,tt t
where
t.id=v.id and t.object_name=upper(v.object_name) and
w.id=u.id and
v.created between t.created and t.last_ddl_time and
v.created between u.created and u.last_ddl_time and
t.object_id=w.object_id and w.created=v.created;
这次，SQL只需要不到1秒时间就能跑出结果了，执行计划也正是我们需要的全部hash join：
在优化器内部生成的标准执行计划outline data中，上面的hint最终被转化成这样：
（有没有注意到，其中leading的第一个表没有做use_hash(V)？
这是因为，有第二个表的use_hash(t)的存在，t表做use_hash（t），跟谁做？当然是和第一个表V）。
结论：
我们在写多表use_hash(use_nl也一样）hint的时候，use_hash的括号里面是可以放多个表（顺序无关），但是一定要结合leading 的hint，才能保证优化器不使用其他的join方式。 leading里面表的顺序非常关键哦，搞错了会带你去见笛卡尔（cartesian join）（因为按顺序做join的两个表可能没有join 条件，只有过滤条件。）
-------这个leading也不生效啊--------------------应该是u和上面没有join条件 只能nest loop 笛卡尔积
SQL> select * from table(gv$(cursor(select * from table(dbms_xplan.display_cursor('',null))))) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8n1jg8r84b3mz, child number 0
-------------------------------------
select /*+ leading(v t u w) use_hash(u v t w) */ /*x+ monitor
opt_estimate(join, (t,v), rows=1110 ) */ /*x+ parallel(6)
opt_estimate(join, (t,v), rows=0 )*/ count(*) from tv v,tu u,tw w,tt t
where t.id=v.id and t.object_name=upper(v.object_name) and w.id=u.id
and v.created between t.created and t.last_ddl_time and v.created
between u.created and u.last_ddl_time and t.object_id=w.object_id and
w.created=v.created
Plan hash value: 1666341298
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | | | | 2077 (100)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
| 1 | SORT AGGREGATE | | 1 | 148 | | |
|
|* 2 | HASH JOIN | | 1 | 148 | | 2077 (1)| 00:0
0:01 |
| 3 | NESTED LOOPS | | 662 | 86060 | | 1674 (1)| 00:0
0:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | HASH JOIN | | 1 | 109 | 4320K| 1271 (1)| 00:0
0:01 |
| 5 | TABLE ACCESS FULL| TV | 73614 | 3450K| | 403 (1)| 00:0
0:01 |
| 6 | TABLE ACCESS FULL| TT | 73614 | 4385K| | 403 (1)| 00:0
0:01 |
|* 7 | TABLE ACCESS FULL | TU | 6374 | 130K| | 403 (1)| 00:0
0:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 8 | TABLE ACCESS FULL | TW | 73614 | 1293K| | 403 (1)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("W"."ID"="U"."ID" AND "T"."OBJECT_ID"="W"."OBJECT_ID" AND
"W"."CREATED"="V"."CREATED")
4 - access("T"."ID"="V"."ID" AND "T"."OBJECT_NAME"=UPPER("V"."OBJECT_NAME")
)
filter(("V"."CREATED"<="T"."LAST_DDL_TIME" AND
"V"."CREATED">="T"."CREATED"))
7 - filter(("V"."CREATED"<="U"."LAST_DDL_TIME" AND
"V"."CREATED">="U"."CREATED"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
5 - SEL$1 / V@SEL$1
U - use_hash(u v t w)
7 - SEL$1 / U@SEL$1
U - use_hash(u v t w)
48 rows selected.
SQL>