为什么驱动表返回记录太多就不适合nested loops？

Yong Huang

原帖由 sqysl 于 2009-9-24 18:42 发表
1、概念是有点乱，这里驱动表是外部表（OUTER TABLE）

You're right. I re-read Oracle documentation and some notes (such as 68084.1 and 10626.1), and my own note (http://yong321.freeshell.org/computer/OracleMisnomers.txt item 10), NL join is

NESTED LOOP JOIN
  OUTER_TABLE
  INNER_TABLE

I just made the correction in my message.

But when we talk about hash joins, it's still better to call them build and probe tables, not outer and inner tables, as commented by Jonathan Lewis in his CBO book (p.308):

"The terms outer and inner are really only appropriate to nested loop joins. When talking about hash joins, you
ought to refer to the build table and probe table; and for merge joins, the terms first table and second table are
sufficient. However, you will find that the 10053 trace file always uses the terms outer and inner to identify
the first and second tables respectively in a join operation. I will revisit this point in the relevant chapters."

Yong Huang

seman18

我大概明白了，其实我就是想知道NLJ跟HJ在I/O，CPU上的花销区别，我想这才是当数据量不同的情况下选择不同的join的原因