Tuning Table Joins
Oracle Tips by Burleson Consulting
Table joins are the heart of SQL tuning and
are one of the most complex areas of SQL tuning. When evaluating a
table join, we must determine the optimal table join order and the
most appropriate table join methods, all while ensuring that the
query performs as fast as possible.
This chapter will cover the following topics
relating to table joining:
- The table join types
- The basic table join methods
- Determining the optimal table join order
- Tuning distributed SQL joins
We will begin with a review of the basic SQL
join types, move on to look at Oracle’s implementation of join
methods, and then take a look at how Oracle evaluates the table join
order for multi-table joins. We will then cover distributed table
joins in a net8 environment. Let’s begin with a review of the basic
table join types.
The Table Join Types
Before we dive into the complex tuning of
Oracle table joins, let’s begin with a brief review of the different
types of table joins within ANSI standard SQL. We need to make the
distinction between the theoretical types of SQL joins and the
Oracle implementation of the join. In most cases they are somewhat
similar, bit in several cases the theoretical join type does not
have a parallel within Oracle join methods. For example, an outer
join has a very clear differentiation from an equi-join, but within
Oracle, the nested loops table access plan can be used to drive
either of these join types.
Here are the basic SQL join types. Once you
understand these theoretical join types, we will move to look at
Oracle implementation of these join structures.
- Equi-join This is a standard join
that pairs the rows between two tables by matching the values in a
common column between the two tables. The Oracle table access plan
for equi-joins may include NESTED LOOPS, HASH JOIN, or MERGE.
- Outer join This is a join that
preserves incomplete rows where a matching condition is not found
in both tables. Oracle returns all rows that meet the join
condition. Oracle also returns all rows from the table without the
outer join operator for which there are no matching rows in the
table with the outer join operator. Just like an equi-join, the
outer join may invoke a NESTED LOOPS Oracle table access.
- Self-join This is a special case
where a table is joined against itself. This is a common feature
in bill-of-materials tables and time-based tables where values are
compared over different time periods.
- Anti-join This is the type of join
we normally see when we use a subquery with a NOT IN or a
NOT EXISTS clause. The anti-join is normally
evidenced by the TABLE ACCESS FULL table access method.
- Semi-join A semi-join
returns rows that match a query with an exists clause,
without duplicating rows from the left side of the predicate when
multiple rows on the right side satisfy the criteria of the
subquery. The table access method most commonly associated with a
semi-join is TABLE ACCESS FULL against the driving table and an
INDEX RANGE SCAN against the joined table.
Now let’s take a look at the details for each of these
conceptual join methods. During our discussion, I will show some of
the Oracle table access plans that relate to each method.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.