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

 

 


 

 

 

 

 

 

 

The Equi-Join

Oracle Tips by Burleson Consulting

An equi-join is any SQL statement that references two or more tables, with an equality predicate in the where clause to specify the join condition for the tables (Figure 16-1).

Figure 1: The SQL equi-join

The equi-join is the most common of all of the join types and therefore deserves a closer inspection. For example, what follows is an equi-join to display all employees and their bonuses:

select
   emp.ename,
   emp.deptno
   bonus.comm
from
   emp,
   bonus
where
   emp.ename = bonus.ename
;

Here we see the output from this query. Note that the equi-join key (ename in this example) does not need to be displayed in the result set to serve as the join key.

ENAME          DEPTNO       COMM
---------- ---------- ----------
ALLEN              30        300
WARD               30        500
MARTIN             30       1400

The equi-join is the most straightforward of all of the relational join operators, and Oracle offers three join methods for equi-joins, the nested loop join method, the hash join method, and the sort merge join method.

The Outer Join

An outer join is a special case of a table join where unmatched columns from a table are still displayed in the output of the query (Figure 16-2).

Figure 2: An outer join

The outer join is implemented by placing the plus-sign (+) operator in the equality predicate of the where clause. In the next example, we want to display all employees, not just those who received a bonus. Hence, we place the (+) outer join directive on the side of the equality that references the bonus table to indicate that we also want the non-matching rows.

select
   emp.ename,
   emp.deptno,
   bonus.comm
from
   emp,
   bonus
where
   emp.ename = bonus.ename(+)
;

Let’s examine the output from this query. As you can see, the (+) directive made the Oracle SQL include emp rows, even where there was no matching row in the bonus table.

ENAME          DEPTNO       COMM
---------- ---------- ----------
ALLEN              30        300
WARD               30        500
MARTIN             30       1400
FORD               20
SCOTT              20
JAMES              30
KING               10
BLAKE              30
MILLER             10
TURNER             30
CLARK              10
JONES              20
ADAMS              20
SMITH              20

This is first_rows execution plan for this query. Note the use of the NESTED LOOPS OUTER access method. We must also note that the SQL optimizer understands that we want to see all of the rows in the emp table, and it has wisely chosen a full-table scan because all of the rows are required.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
  NESTED LOOPS
OUTER                                                                1
    TABLE ACCESS
FULL                           EMP                                   1

    TABLE ACCESS
BY INDEX ROWID                 BONUS                                 2
      INDEX
RANGE SCAN                     BONUS_ENAME                           1

In this case of an outer join, the RBO and the CBO will always generate an identical table access method. The only difference is that the position of the tables in the where clause will affect the choice of the driving table in the RBO, while the CBO will generally use the table with the smallest value for num_rows as the driving table.


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