请教大虾 NESTED LOOP 和 HASH JOIN 的耗时区别为什么那么大

Question

我在用一个小表去left join 一个大表，耗时大概20分钟，感觉数据库抽风了，以前这么做关联大概也就1~2分钟（是不是什么设置改动，导致这样的结果？）；
同样的两个表，我把left join 改为 inner join 之后，却只耗时1.5分钟；
请大虾帮忙看一下，这是怎么回事啊？
执行计划如截图，但是刚开始接触执行计划，有点看不懂，请大虾帮忙解答下。 

Answer 1

竟然把表建到sys用户下...
或者重新采集两张表的统计信息试试

Answer 2

多谢~~啊！我们库里的基础宽表都是建立在sys用户下的。有一点没搞懂的是，我之前做这样的连接操作是不需要耗时很长的，现在搞不懂怎么连接20w的数据都要20多分钟，是因为数据库的什么设置被改了吗？
ps：重新采集两张表的统计信息？这个怎么做。求指导

Answer 3

首先说一下，看执行计划，不要用pl/sql developer看，用autotrace或者explain看。
left join那个执行计划，用了嵌套循环，且a表为驱动表，然后驱动b表，但a表是通过全表扫描，返回数据量过大，每返回一行，就要驱动一次B表，然后B表再回表，这样会导致a表有多少记录，B表的索引就会被扫描多少次。肯定很慢。
估计是统计信息过期了。你可以到我的博客www.savedba.com里面看一下统计信息部分和表关联部分。就理解为什么这么慢了。

Answer 4

原因就是数据量变大了吧，left join 要扫表。

Answer 5

可能的原因，你使用left join时，固定了表连接的执行顺序。例如，对于nested loop连接方式，a left join b ，这种情况下a表必须作为驱动表！你改为inner join后，两个表都可以作为驱动表，oracle的优化器对于执行计划有了更多的选择。因此最后执行时间肯定也天壤之别

Answer 6

驱动表为大表，nested loop 循环遍历代价肯定大于 做hash join。这在cpu，io上花费都很大。