Remote DBA Plans
Remote DBA Service

Oracle Table Join Methods

Oracle Tips by Burleson Consulting

Now that you understand the conceptual join methods, let�s look at how Oracle joins the tables together. As you will recall from previous chapters, Oracle provides the following methods for joining tables:

Nested loop
Sort merge joins
Hash joins
Star joins

Oracle also provides parallel partition join methods that are fully described in Chapter 21. The relative performance of these join types as a function of the number of rows in the tables is represented in Figure 16-6.

Figure 6: The relative speeds of different Oracle join methods

Of course, Figure 16-6 is a bit misleading because under certain circumstances, each of these join methods may be the fastest for your query. For example, a join where one table is very small will generally be fastest with a hash join, while a join of two very large tables is generally fastest with a nested loop join. Let�s take a close look at each method and understand how they work.

Nested Loop Joins

The nested loop join is the oldest and most basic of the table join methods. In a nested loop join, rows are accessed from a driving table (using either TABLE ACCESS FULL or INDEX RANGE SCAN), and then this result set is nested within a probe of the second table, normally using an INDEX RANGE SCAN method.

In Oracle6, there were only two table join methods, the nested loops and the sort merge. Now in Oracle8i we have added a hash join and star joins to our list of possible table join mechanisms. The nested loops method works by comparing each key in the outer table to each key in the inner table. There are several subtypes of nested loop joins.

Let�s begin by looking at a case where only one of the joined tables possesses an index. This may dramatically increase the overall cost because of the exponential growth of the nested loops strategy. In fact, this path will be considered only if at least one support index is present in the joining tables.

Another permutation of nested loops works when both table possess a selective index. If multiple indexes are present, Oracle will choose the index access for the table (inner table) with the most selective index.

TIP: When performing a nested loop join with incomplete indexes, the SQL optimizer will always make the driving table as the table that does not possess an index on the join key.

Under the CBO, the nested loops method takes advantage of differences in table size. The smaller table will generally be chosen as the driving table for the nested loop join. If you are invoking nested loops with the RBO, then you will want to place the smallest table as the last table in the from clause to ensure that the smallest table is used to drive the nested loop query.

To start, consider this query that performs a standard equi-join against two tables.

select /*+ rule */
   ename,
   dname
from
   dept,
   emp
where
   emp.deptno = dept.deptno;

Here we see that the query will select all employees and we have a rule hint, so we have properly specified the emp table as the last table in the from clause to make emp the driving table. When reading the execution plan for a nested loop join, the driving table will be the first table specified after the NESTED LOOPS table access method.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
NESTED LOOPS
                                                                     1
    TABLE ACCESS
FULL                           EMP                                   1
    TABLE ACCESS
BY INDEX ROWID                 DEPT                                  2
      INDEX
RANGE SCAN                     DEPT_DEPTNO                           1

As we expected, the nested loop join uses the emp table as the driving table.

Now, let�s change the query to add a constraint on the emp rows, only selecting those employees in department 10.

select /*+ rule */
   ename,
   dname
from
   dept,
   emp
where
   emp.deptno = dept.deptno
and
   emp.deptno = 10
;

Here we see the change to the table access using the RBO with an index on deptno on both tables. We see that the nested loops access each table with an index range scan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
NESTED LOOPS
                                                                     1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
      INDEX
RANGE SCAN                     EMP_DEPTNO                            1
    TABLE ACCESS
BY INDEX ROWID                 DEPT                                  2
      INDEX
RANGE SCAN                     DEPT_DEPTNO                           1

As you see, we are using rule-based optimization for this equi-join and the nested loops method takes full advantage of the available indexes.

Next, let�s see how we can force a nested loop join with a hint.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.