Oracle Illustrated

Feb 21 2010   7:33AM GMT

Migrating from 9i to 11g – Hash full / outer joins

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Hash outer joins

This is a new capability of the optimizer is to use a hash algorithm to execute outer joins. Oracle suggests that using this new hash outer join could result in a 50% reduction in LIO. Full outer join would return all matching rows and the non-matching rows in the tables in question.

select
empname,
empno
from
emp full outer join dept using (deptno)
order by 1;

Execution Plan
——————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 40541 | 1187K| | 480 (1)| 00:00:06 |
| 1 | SORT ORDER BY | | 40541 | 1187K| 3192K| 480 (1)| 00:00:06 |
| 2 | VIEW | VW_FOJ_0 | 40541 | 1187K| | 145 (1)| 00:00:02 |
|* 3 | HASH JOIN FULL OUTER | | 40541 | 1464K| | 145 (1)| 00:00:02 |
| 4 | INDEX FAST FULL SCAN| DEPT_IDX | 5 | 20 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 40541 | 1306K| | 143 (1)| 00:00:02 |
——————————————————————————————–

Notice the HASH JOIN FULL OUTER step in the execution plan. Should keep in mind that there is no hint to force the optimizer to use or not to use the HASH OUTER JOIN method.

Hash Full Joins

With Oracle 11g – a new access method which produces 50% less consistent gets which is called HASH JOIN FULL OUTER

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.

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 40192 | 1256K| | 500 (1)| 00:00:06 |
| 1 | HASH GROUP BY | | 40192 | 1256K| 3184K| 500 (1)| 00:00:06 |
|* 2 | HASH JOIN RIGHT OUTER| | 40541 | 1266K| | 144 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | DEPT_IDX | 5 | 20 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 40541 | 1108K| | 143 (1)| 00:00:02 |
——————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – access(“E”.”DEPTNO”=”D”.”DEPTNO”(+))

Statistics
———————————————————-
1 recursive calls
0 db block gets
522 consistent gets
0 physical reads
0 redo size
1503026 bytes sent via SQL*Net to client
54474 bytes received via SQL*Net from client
2704 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40541 rows processed

This could be particularly useful when we want to view differences between two tables.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: