Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Native Full Outer Join Officially Available in 10.2.0.5

4 May 2010 7 Comments Written by Christian Antognini

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")
10gR2, Query Optimizer
Exadata Storage Server and the Query Optimizer – Part 2
Exadata Storage Server and the Query Optimizer – Part 3

7 Comments

4 Pings/Trackbacks

  1. Timur Akhmadeev Timur Akhmadeev
    4 May 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)

    Reply
  2. Christian Antognini Christian Antognini
    4 May 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

    Reply
  3. Log Buffer #189, A Carnival of the Vanities for DBAs « So Many Oracle Manuals, So Little Time Log Buffer #189, A Carnival of the Vanities for DBAs « So Many Oracle Manuals, So Little Time
    14 May 2010    

    […] Christian Antognini pored over the 10.2.0.5 liner notes and discovered that it comes with a native implementation of FULL OUTER JOIN that does not require an inefficient UNION operation. Is that one more reason to stick with Oracle […]

  4. Plan Stability Through Upgrade-Why is my plan changed?-new optimizer parameters « Coskan’s Approach to Oracle Plan Stability Through Upgrade-Why is my plan changed?-new optimizer parameters « Coskan’s Approach to Oracle
    17 February 2011    

    […] again (another “invalid dba exception”, should have done before ) and it reminded me Christian Antognini’s post about native full outer join support (Being a blog reader is good thing !!!) then I checked the parameter and immediatelly realized that […]

  5. Alberto Dell'Era Alberto Dell'Era
    1 July 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

    Reply
  6. There’s Always Another Bug Hiding Just Around the Corner | Just Another Day of Apps DBA's Life There’s Always Another Bug Hiding Just Around the Corner | Just Another Day of Apps DBA's Life
    15 August 2013    

    […] Before that, it was implemented using the UNION ALL operation. (Cristian Antognini explains it here and gives some […]

  7. Outer Join Operator (+) Restrictions in 12.1.0.2? | Philipp Salvisberg's Blog Outer Join Operator (+) Restrictions in 12.1.0.2? | Philipp Salvisberg's Blog
    14 December 2015    

    […] In the most current Oracle version no relevant limitations exist regarding the Oracle join syntax. Hence choosing ANSI join syntax just because in the past some limitations existed is doing the right for the wrong reasons… I favor the ANSI join syntax because filter and join conditions are clearly separated. For full outer joins, there is simply no better performance option than to use ANSI join syntax. See also also Chris Antognini’s post about native full outer join. […]

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

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.