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.