"HASH JOIN FULL OUTER" in explain plan (11g onwards)

Typically, when the full outer join condition between two tables is an equi-join, the hash full outer join execution method is possible, and Oracle Database uses it automatically.

To instruct the optimizer to consider using the hash full outer join execution method, apply the NATIVE_FULL_OUTER_JOIN hint. 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.

SQL> alter session set optimizer_features_enable='11.1.0.6';

Session altered.

SQL> explain plan for
  2  SELECT d.department_id, e.employee_id
  3  FROM employees e
  4  FULL OUTER JOIN departments d
  5  ON e.department_id = d.department_id
  6  ORDER BY d.department_id;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3770333139

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |   122 |  3172 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY          |            |   122 |  3172 |     7  (29)| 00:00:01 |
|   2 |   VIEW                  | VW_FOJ_0   |   122 |  3172 |     6  (17)| 00:00:01 |
|*  3 |    HASH JOIN FULL OUTER |            |   122 |  1342 |     6  (17)| 00:00:01 |
|   4 |     INDEX FAST FULL SCAN| DEPT_ID_PK |    27 |   108 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | EMPLOYEES  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

17 rows selected.

SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

SQL> explain plan for
  2  SELECT d.department_id, e.employee_id
  3  FROM employees e
  4  FULL OUTER JOIN departments d
  5  ON e.department_id = d.department_id
  6  ORDER BY d.department_id;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3122951222

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   124 |  4836 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY        |                   |   124 |  4836 |     5  (20)| 00:00:01 |
|   2 |   VIEW                |                   |   124 |  4836 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL          |                   |       |       |            |          |
|   4 |     NESTED LOOPS OUTER|                   |   107 |  1177 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMPLOYEES         |   107 |   749 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN| DEPT_ID_PK        |     1 |     4 |     0   (0)| 00:00:01 |
|   7 |     NESTED LOOPS ANTI |                   |    17 |   119 |     1   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN  | DEPT_ID_PK        |    27 |   108 |     1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

22 rows selected.