Database Reference
In-Depth Information
From a conceptual point of view, a SQL statement containing join conditions and restrictions is executed in the
following way:
•
The two sets of data are joined based on the join conditions.
•
The restrictions are applied to the result set returned by the join.
In other words, a join condition is specified to avoid a cross join while joining two sets of data. It isn't intended
to filter out the result set. Instead, a restriction is specified to filter the result set returned by a previous operation
(for example, a join). For instance, in the following query, the join condition is
emp.deptno = dept.deptno
, and the
restriction is
dept.loc = 'DALLAS'
:
SELECT emp.ename
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.loc = 'DALLAS'
From an implementation point of view, it's not unusual that the query optimizer takes advantage of both
restrictions and join conditions. On the one hand, join conditions might be used to filter out data. On the other hand,
restrictions might be evaluated before join conditions to minimize the amount of data to be joined. For example, the
previous query might be executed with the following execution plan. Notice how the
dept.loc = 'DALLAS'
restriction
(operation 2) is applied before the
emp.deptno = dept.deptno
join condition (operation 1):
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL| DEPT |
| 3 | TABLE ACCESS FULL| EMP |
-----------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
2 - filter("DEPT"."LOC"='DALLAS')
Nested Loops Joins
The following sections describe how
nested loops joins
work. I describe their general behavior and then give some
examples of two-table and four-table joins. Finally, I describe some optimization techniques. All examples are based
on the
nested_loops_join.sql
script.
Concept
The two sets of data processed by a nested loops join are called
outer loop
(also known as
driving row source
) and
inner loop
. The outer loop is the left input, and the inner loop is the right input. As illustrated in Figure
14-6
, whereas
the outer loop is executed once, the inner loop is executed once for each row returned by the outer loop.
