Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle 11g full hash join

Oracle11g Tips by Burleson Consulting
October 25, 2007

Oracle guru Laurent Schneider notes that the new Oracle 11g "hash join full" execution plan can result in less than 50% of the logical I/O (consistent gets) than a traditional join.

A full outer join is used to join two tables together and include non-matching rows from each table.  For more on the 11g "hash join full outer" execution plan, we note that the full outer join was introduced in Oracle9i with the SQL99 standard.

The full outer join has no direct equivalent in Oracle8i, but it is very handy to find missing rows in both tables being joined.  In the example below, we include employees with departments as well as departments without employees:





   employees e

full outer join

   departments d

on e.department_id = d.department_id;

John Garmany notes that there is no standard Oracle format for a full outer join in Oracle8i and earlier.  You must union a left and right outer join to get the same results.

SQL> select
  2    author_last_name,
  3    book_key
  4  from
  5    author,
  6    book_author
  7  where
  8    author.author_key = book_author.author_key(+)
  9  union
  10  select
  11    author_last_name,
  12    book_key
  13  from
  14    author,
  15    book_author
  16  where
  17    author.author_key(+) = book_author.author_key
  18  order by author_last_name;

AUTHOR_LAST_NAME                         BOOK_K
---------------------------------------- ------
hester                                   B101
hester                                   B109
hester                                   B116
jeckle                                   B102

Notice that the union removed duplicate rows, and I only ordered the results set once at the end of the query.

Using the ANSI format, you can also outer join multiple tables with multiple outer joins.  That was not allowed in the standard Oracle format.

SQL> select
  2    author_last_name c1,
  3    book_title       c2
  4  from
  5    author full outer join book_author using (author_key)
  6           full outer join book using (book_key)
  7  order by author_last_name;

Author                    Title
------------------------- -----------------------------
hester                    windows success
hester                    pay no taxes and go to jail
hester                    oracle9i sql tuning
jeckle                    piano greats

In the example above, I joined three tables using full outer joins. This allowed me to include both the books without authors and the authors without books in my report.

Laurent Schneider notes this on the full outer join and how it is performed in earlier releases of Oracle:

A wrong try to write a full outer join in 8i would be:

9i> Select * from a full join b on (a.x=b.x)

8i> select * from a, b where a.x=b.x(+)
select * from a, b where a.x(+)=b.x

Because it would remove duplicates in a or in b.

The correct equivalence for a full outer join would be:

8i> select * from a, b where a.x=b.x(+)
union all
select * from a, b where a.x(+)=b.x and a.x is null

In 11g, there is a new access method which produces 50% less consistent gets which is called HASH JOIN FULL OUTER

The Oracle 11g documentation has these notes on the HASH JOIN FULL OUTER execution plan:

The optimizer uses hash joins for processing an outer join if the data volume is high enough to make the hash join method efficient or if it is not possible to drive from the outer table to inner table.

The order of tables is determined by the cost. The outer table, including preserved rows, may be used to build the hash table, or it may be used to probe one. . .

Example 11-8 Hash Join Outer Joins

SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
  FROM customers c, orders o
 WHERE c.credit_limit > 1000
   AND c.customer_id = o.customer_id(+)
 GROUP BY cust_last_name;

| Id  | Operation            |  Name           | Rows  | Bytes | Cost (%CPU)|
|   0 | SELECT STATEMENT     |                 |   168 |  3192 |     6  (17)|
|   1 |  HASH GROUP BY       |                 |   168 |  3192 |     6  (17)|
|*  2 |   NESTED LOOPS OUTER |                 |   260 |  4940 |     5  (0) |
|*  3 |    TABLE ACCESS FULL | CUSTOMERS       |   260 |  3900 |     5  (0) |
|*  4 |    INDEX RANGE SCAN  | ORD_CUSTOMER_IX |   105 |   420 |     0  (0) |

Predicate Information (identified by operation id):

   3 - filter("C"."CREDIT_LIMIT">1000)
   4 - access("C"."CUSTOMER_ID"="0"."CUSTOMER_ID"(+))

Laurent Schneider also notes that the full outer join syntax can be very usable in the real world when you want to see the differences between two tables:

I have a table T1(id) and a table T2(id). I want to spot the differences.

SQL> create table t1(id number);
Table created.
SQL> insert into t1(id) values (1);
1 row created.
SQL> insert into t1(id) values (2);
1 row created.
SQL> create table t2(id number);
Table created.
SQL> insert into t2(id) values (1);
1 row created.
SQL> insert into t2(id) values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select,
 2   from t1 full join t2
 3   on (
 4   where is null or is null;
        ID         ID
---------- ----------
This is very usable in real world, is not it?

For more on Oracle 11g full outer hash joins, see the book "Advanced Oracle SQL Programming" and "Oracle 11g New Features" by Rampant TechPress.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational