Database Reference
In-Depth Information
Chapter 14
Optimizing Joins
When a SQL statement references several tables, the query optimizer has to determine, in addition to the access path
for each table, which order the tables are joined in and which join methods are used. The goal of the query optimizer
is to minimize the amount of processing by filtering out unneeded data as soon as possible.
This chapter starts by defining key terms and explains how the three basic join methods (nested loops join,
merge join, and hash join) work. Some advice follows on how to choose the join methods. Finally, the chapter
describes optimization techniques such as partition-wise joins and star transformation.
■
In this chapter, several SQL statements contain hints. This is done not only to show you which hint leads to
which execution plan but also to show you examples of their utilization. In any case, neither real references nor full
syntaxes are provided. You can find these in Chapter 2 of the
Oracle Database SQL Reference
manual.
Note
Definitions
To avoid misunderstandings, the following sections define some terms and concepts used through this chapter.
Specifically, I cover the different types of join trees, the difference between restrictions and join conditions, and the
different types of joins.
Join Trees
All join methods supported by the database engine process only two sets of data at a time. These are called
left
input
and
right input
. They're named in this way because when a graphical representation (see Figure
14-1
) is used,
one of the inputs is placed on the left of the join (
T1
) and the other on the right (
T2
). Note that in this graphical
representation, the node on the left is executed before the node on the right.
