Native Full Outer Join Officially Available in 10.2.0.5

Today I installed for the first time the patchset 10.2.0.5. While reading the README file, I noticed the following piece of information.

To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:

_optimizer_native_full_outer_join =force

You can set this parameter for the system or for a specific session.

Besides dramatically improving the performance of a full outer join, the new implementation fixes a variety of issues, for examples a variety of ORA-942 (table or view doesn’t exists) and ORA-4331 (unable to allocate string bytes of shared memory) errors.

This issue is tracked with Oracle bug 6322672.

Great! At last we can officially take advantage of native full outer join also in 10.2 (the feature was officially introduced in 11.1, but was already “available” in 10.2.0.3).

Here is an example:

  • By default native full outer joins are disabled (notice the implementation with the UNION ALL operation):
SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 2291915024

---------------------------------------------
| Id  | Operation            | Name         |
---------------------------------------------
|   0 | SELECT STATEMENT     |              |
|   1 |  VIEW                |              |
|   2 |   UNION-ALL          |              |
|*  3 |    HASH JOIN OUTER   |              |
|   4 |     TABLE ACCESS FULL| EMP          |
|   5 |     TABLE ACCESS FULL| DEPT         |
|   6 |    NESTED LOOPS ANTI |              |
|   7 |     TABLE ACCESS FULL| DEPT         |
|*  8 |     INDEX RANGE SCAN | EMP_DEPTNO_I |
---------------------------------------------

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

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
   8 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
  • As suggested by the README file, the feature can be enabled at the session level:
SQL> ALTER SESSION SET "_optimizer_native_full_outer_join" = force;

SQL> SELECT * FROM emp FULL OUTER JOIN dept USING (deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 51889263

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  VIEW                 | VW_FOJ_0 |
|*  2 |   HASH JOIN FULL OUTER|          |
|   3 |    TABLE ACCESS FULL  | DEPT     |
|   4 |    TABLE ACCESS FULL  | EMP      |
------------------------------------------

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

   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

3 Comments

  1. May 4, 2010    

    Hi Christian,
    >the feature was officially introduced in 11.1, but was already “available” in 10.2.0.4
    It was 10.2.0.3 (can be confirmed using v$sql_hint in 11g)

  2. May 4, 2010    

    Hi Timur

    Thank you for the feedback. For some reasons I was sure that it was 10.2.0.4… Anyway, I modified the post.

    Cheers,
    Chris

  3. July 1, 2013    

    Hi Chris,

    it’s also interesting to note that the feature is supported (and not simply available) since (at least) 10.2.0.4: note 1383952.1, paragraph 15.15, explicitly suggests to set _optimizer_native_full_outer_join =force. Actually, the wording seems to strongly encourage users in doing so…

    Thanks for this post – with this setting, today an outer join completed in less than a second instead of an hour.
    Al

  1. Log Buffer #189, A Carnival of the Vanities for DBAs « So Many Oracle Manuals, So Little Time on May 14, 2010 at 15:30
  2. Plan Stability Through Upgrade-Why is my plan changed?-new optimizer parameters « Coskan’s Approach to Oracle on February 17, 2011 at 21:09
  3. There’s Always Another Bug Hiding Just Around the Corner | Just Another Day of Apps DBA's Life on August 15, 2013 at 06:02

Leave a Reply

Your email address will not be published. Required fields are marked *