Oracle数据库的连接方式之一：NESTED LOOPS join

Join Methods：
       Since the days of Oracle 6, the optimizer has used three different ways to join row sources
together: the NESTED LOOPS join, the SORT-MERGE join, and the CLUSTER join. (There is also
the favorite of the ad hoc query user, the CARTESIAN join.) In Oracle 7.3, the HASH join was
introduced, and in Oracle 8i, the INDEX join was introduced, making for a total of five primary
join methods. Each has a unique set of features and limitations. Before you attack a potential join
issue, you need to know the following:
■ Which table will drive the query (first table accessed), and when will other tables be
accessed given the path that is chosen for the query? What are the alternate driving paths?
■ What are the Oracle join possibilities (described in this section)? Remember, each join
possibility for Oracle can yield different results, depending on the join order, the
selectivity of indexes, and the available memory for sorting and/or hashing.
■ Which indexes are available, and what is the selectivity of the indexes? The selectivity
of an index cannot only cause the optimizer to use or suppress an index, but it can also
change the way the query drives and may determine the use or suppression of other
indexes in the query.
■ Which hints provide alternate paths, and which hints suppress or force an index to be
used? These hints change the driving order of the tables, and they change how Oracle
performs the join and which indexes it uses or suppresses.
■ Which version of Oracle are you using? Your choices vary, depending on the version
and release of Oracle you are using. The optimizer also works differently, depending
on the version.

NESTED LOOPS Joins：
       Suppose somebody gave you a telephone book and a list of 20 names to look up, and asked you
to write down each person’s name and corresponding telephone number. You would probably
go down the list of names, looking up each one in the telephone book one at a time. This task
would be pretty easy because the telephone book is alphabetized by name. Moreover, somebody
looking over your shoulder could begin calling the first few numbers you write down while you
are still looking up the rest. This scene describes a NESTED LOOPS join.
       In a NESTED LOOPS join, Oracle reads the first row from the first row source and then
checks the second row source for matches. All matches are then placed in the result set and
Oracle goes on to the next row from the first row source. This continues until all rows in the first
row source have been processed. The first row source is often called the outer or driving table,
whereas the second row source is called the inner table. Using a NESTED LOOPS join is one of
the fastest methods of receiving the first records back from a join.
Chapter 9: Table Joins and Other Advanced Tuning (Advanced DBA and Developer) 347
        NESTED LOOPS joins are ideal when the driving row source (the records you are looking for)
is small and the joined columns of the inner row source are uniquely indexed or have a highly
selective non-unique index. NESTED LOOPS joins have an advantage over other join methods
in that they can quickly retrieve the first few rows of the result set without having to wait for the
entire result set to be determined. This situation is ideal for query screens where an end user can
read the first few records retrieved while the rest are being fetched. NESTED LOOPS joins are
also flexible in that any two-row sources can always be joined by NESTED LOOPS—regardless
of join condition and schema definition.
          However, NESTED LOOPS joins can be very inefficient if the inner row source (second table
accessed) does not have an index on the joined columns or if the index is not highly selective.
If the driving row source (the records retrieved from the driving table) is quite large, other join
methods may be more efficient.
          Figure 9-1 illustrates the method of executing the query shown next where the dept table is
accessed first and the result is then looped through the emp table with a NESTED LOOPS join.
The type of join that is performed can be forced with a hint and will vary by different variables
on your system.

select /*+ ordered */ ename, dept.deptno
from dept, emp
where dept.deptno = emp.deptno