BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter