New efficient HASH FULL OUTER join in 11g

Purpose:
--
Oracle 11g query optimizer introduced a new HASH JOIN FULL OUTER execution method.

Starting with Oracle Database 11g Release 1 (11.1), Oracle Database automatically uses a native execution method based on a hash join for executing full outer joins whenever possible. When the new method is used to execute a full outer join, the execution plan for the query contains HASH JOIN FULL OUTER.

To instruct the optimizer not to consider using the hash full outer join execution method, apply the NO_NATIVE_FULL_OUTER_JOIN hint. The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and an anti-join.

Result
--
The new HASH JOIN FULL OUTER execution method logical reads(Buffers in execution plan output) cost equals too two base table FULL scan logical I/O adds up, nothing more.
The old full outer join cost 4 times more LIO on table scan.


Benchmark
---------

drop table t1 purge;
drop table t2 purge;

create table t1
as
select
 rownum + 5 id,
 rpad('x',500,'x') c1
from dual
connect by level <= 5000;

create table t2
as
select
 rownum + 15 id,
 rpad('x',500,'x') c1
from dual
connect by level <= 5000;

set serveroutput off
set linesize 170
set pagesize 0

select max(t1c1), max(t2c1)
from (
select /*+ gather_plan_statistics */ t1.id, t2.id, t1.c1 t1c1, t2.c1 t2c1
from t1 full outer join t2
on t1.id = t2.id
);

-- 10g way
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last'));

-- 11g way
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'iostats memstats last partition'));

-----------------------------------------------------------------------------------------
| Id  | Operation               | Name | A-Rows | Buffers | Reads  | Writes| A-Time     |
-----------------------------------------------------------------------------------------
|   1 | SORT AGGREGATE          |      |      1 |     732 |    101 |     0 |00:00:00.15 |
|   2 |  VIEW                   |      |   5010 |     732 |    101 |     0 |00:00:00.06 |
|   3 |   UNION-ALL             |      |   5010 |     732 |    101 |     0 |00:00:00.05 |
|*  4 |    HASH JOIN RIGHT OUTER|      |   5000 |     366 |    101 |     0 |00:00:00.04 |
|   5 |     TABLE ACCESS FULL   | T2   |   5000 |     183 |     56 |     0 |00:00:00.01 |
|   6 |     TABLE ACCESS FULL   | T1   |   5000 |     183 |     45 |     0 |00:00:00.01 |
|*  7 |    HASH JOIN RIGHT ANTI |      |     10 |     366 |      0 |     0 |00:00:00.01 |
|   8 |     TABLE ACCESS FULL   | T1   |   5000 |     183 |      0 |     0 |00:00:00.01 |
|   9 |     TABLE ACCESS FULL   | T2   |   5000 |     183 |      0 |     0 |00:00:00.01 |
-----------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |00:00:00.09 |     184 |       |       |          |
|   1 |  SORT AGGREGATE        |          |      1 |00:00:00.09 |     184 |       |       |          |
|   2 |   VIEW                 | VW_FOJ_0 |   5010 |00:00:00.03 |     184 |       |       |          |
|*  3 |    HASH JOIN FULL OUTER|          |   5010 |00:00:00.03 |     184 |  3239K|  1058K| 3539K (0)|
|   4 |     TABLE ACCESS FULL  | T2       |   5000 |00:00:00.01 |      92 |       |       |          |
|   5 |     TABLE ACCESS FULL  | T1       |   5000 |00:00:00.01 |      92 |       |       |          |
------------------------------------------------------------------------------------------------------

Reference
--
Oracle® Database Performance Tuning Guide 11g Release 1 (11.1)
- 11 The Query Optimizer, 11.6.7 Outer Joins
OracleQueryOptimizer11g_20081202.pdf by Christian Antognini