 |
|
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:
select
last_name,
department_name
from
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
---------------------------------------- ------
clark
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
------------------------- -----------------------------
clark
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(+)
union
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"(+))
filter("O"."CUSTOMER_ID"(+)>0)
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);
SQL> insert into
t1(id) values (1);
SQL> insert into
t1(id) values (2);
SQL> create table
t2(id number);
SQL> insert into
t2(id) values (1);
SQL> insert into
t2(id) values (3);
4 where t1.id is
null or t2.id is null;
ID ID
---------- ----------
2
3
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. |
|
and include the URL for the page.
