多表使用use_hash hint，你写对了吗？

oracle的online document里面，对use_hash的hint语法是这样描述的：

/*+ 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(v t u 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。

这个模拟的SQL展示的就是真实案例出现异常的情况。其中一个步骤使用了nested loops，大概要执行4分钟左右才能完（测试时可以cancel），全部hash的执行计划不到1秒。

当前hint生成的执行计划：

这个执行计划出现了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）。