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










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.

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