Re: Does "Nested Loops" not always join on a join key?

"Marcel Kraupp" <Marcel.Kraupp_at_gmx.ch> wrote :

To answer the thread subject:
Yes - if you do not specify a join criteria between two tables, then it will result in a cartesian join and not an nested loop join.

No - if you imply with "join key" that an index must be used, that is not the case with a nested loop join as full table scan (ouch!) can also be used (not sure if this is default Oracle behaviour, but it can be forced via query hints I think).

> The Nested Loop above indicates, that Oracle joins DT_INFO and
> FUND_SHR_SET. But here's my problem: how can Oracle join these
> table: the don't have a join key in common.

Either you have an incorrectly formatted the explain plan output (which makes it seems like those two tables are joined), or there are more to it what meets the eye...

Instead of trying to explain how your query works, let us rather look at how a nested loop join works.

The easiest is to explain it using two table, T1 and T2. A row found that matches the query criteria for T1, is joined to T2, by performing a scan on T2.

So if:
SELECT
  /*+ ordered use_nl(t2) */
  count(*)
FROM t1, t2
WHERE t1.key1 = t2.key1
AND t1.col1 = 1234
AND t1.dat1 BETWEEN sysdate AND (sysdate-10)

T1 is scanned for rows where COL1 equals 1234 and DAT1 is within the specified date range.If a T1 row is found to match this criteria, T2 is scanned using t1.key1's value, looking for a row with the same KEY1 value.

What makes nested loop great is when you are dealing with two tables of significantly different sizes. If T1 is a small table, and T2 is a very large table, the nested loop join works very well. You are hitting a small table (minimal processing time) before hitting the massive table (with an index). The assumption of course being that when the large table is accessed, it is done via an index scan and not an index range scan (and obviously not via a full table scan).

If you use multiple nested loop joins, then the inner tables are not always directly joined to the outer table - as you described in your posting.

Let's say we add T3 to the query above, with a nested loop join from T2 to T3. After the query process found a T2 row that matches T1 (the first nested loop join), it will then use that partially constructed result/cursor row to perform a nested loop join on T3.

Yes, it is not possible for the query engine to go directly from T1 to T3 if T1 has not been directly joined with T3 (i.e. no direct join criteria exist between these two tables).

However, as T2 serves as the link between the T1 and T3, it is possible to indirectly join T1 with T3 via T2.

Back to your explain plan. Either you have formatted it incorrectly so that it seems that T1 is directly joined with T3, or you are missing the fact that an intermediate T2 is used as part of the process of joining a T1 row with a T3 row.

Redo the query. Start with a two table join first. Explain plan. Then add the 3rd table. Explain plan. Then the 4th. Explain plan. Etc.

This will clearly show what the query engine will be doing to process that query. And make sure that the explain plan output is correctly formatted.

--
Billy