 |
|
11g Hash Outer Joins
Oracle 11g New Features Tips by Burleson
Consulting
June 27, 2008 |
Oracle 11g SQL New Features Tips
A new capability of the optimizer is to use a
hash algorithm to execute outer joins. Using this new hash
outer join can result in a 50% reduction in logical IO. It
must be kept in mind that an outer join returns the rows that match,
plus the rows that do not match from one or both tables. Using
the PUBS schema, the following example shows the HASH OUTER JOIN at
work.
Below, a FULL OUTER JOIN is used with the author
and book_author tables.
This will return all matching rows and the non-matching rows in both
the author table, of authors that have no books, and the book table,
which are planned books that do not have authors.
select
author_last_name,
book_key
from
author full outer join book_author using (author_key)
order by 1;
---------------------------------
| Id
|
Operation
|
---------------------------------
| 0 | SELECT STATEMENT
|
| 1 | SORT ORDER BY
|
| 2 | VIEW
|
|* 3 | HASH JOIN FULL OUTER |
| 4 | TABLE ACCESS FULL
|
| 5 | INDEX FAST FULL SCAN|
---------------------------------
Notice the HASH JOIN FULL OUTER step in the
execution plan. Remote DBAs must keep in mind that there is no hint
to force the optimizer to use or not to use the HASH OUTER JOIN
method.
Conclusion
In this chapter, the enhancement to SQL
introduced in 11g have been explored. First, the PIVOT and
UNPIVOT operators are used to shift data in a result set from rows
to columns and columns to rows. Regular Expressions were also
covered, which allow Remote DBAs to pattern match both a pattern and a sub
pattern. Additionally, the
regexp_count function returns the number of matches found,
and the result_cache hint
caches a query result set in the SGA for use by multiple sessions.
These provide a significant performance boost to query execution
time and resources. Also in 11g, the new HASH OUTER JOIN
optimizer access method can significantly reduce logical IO. As has
been exhibited, all of these new SQL features enhance the
developer’s capability to efficiently interact with the database.
New features of 11g SQL covered in this chapter
were:
-
Pivot and Unpivot
-
Regular expressions
-
Hash outer joins
 |
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off. |
