 |
|
SQL Join Operations
Oracle Tips by Burleson Consulting |
While we will be exploring SQL tuning with
joins in Chapter 14, we should have a brief review so that you
understand how the type of join operation affects the execution plan
for the SQL. While we will investigate each of these join techniques
in detail in Chapter 16, let’s take a quick survey of the most
common join techniques.
Nested Loops Join
A nested loop join is an operation
that has two tables, a smaller inner table and an outer table.
Oracle compares each row of the inner set with each row of the outer
set and returns those rows that satisfy a condition (see Figure
3-7). A nested loop join is commonly seen in conditions where an
index exists on the inner table. If we use nested loop joins, we
need to make sure that the proper driving table and the proper
driving set are used by the query.
Figure 7: A nested
loop join
The nested loop join has the fastest response
time in many cases (especially with small intermediate result row
sets), but the hash join often offers the best overall throughput
and faster performance where the intermediate row set is very large.
Hash Join
A hash join is an operation that
performs a full-table scans on the smaller of the two tables (the
driving table) and then builds a hash table in RAM memory. The hash
table is then used to retrieve the rows in the larger table (see
Figure 3-8). There are several types of hash joins, including the
hash anti-join for cases of SQL that contains a not in clause
followed by a subquery, and a hash semi-join. A hash join is a
special case of a join that joins the table in RAM memory. In a hash
join, both tables are read via a full-table scan (normally using
multiblock reads and parallel query), and the result set is joined
in RAM. This procedure can sometimes be faster than a traditional
join operation.
Figure 8: A hash join
operation
Oracle provides the
hash_multiblock_io_count initialization parameter to determine
the number of multiblock reads that are performed by hash joins. We
also have the hash anti-join. In the case of a hash anti-join, the
SQL optimizer uses a nested loops algorithm for not in
subqueries by default, unless the initialization parameter
always_anti_join is set to merge or hash and
various required conditions are met that allow the transformation of
the not in subquery into a sort-merge or hash anti-join. You
can place a merge_aj or hash_aj hint in the not in
subquery to specify which algorithm the optimizer should use.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
