[20120223]full outer join.txt

full outer join 也就是包括左连接以及右连接，然后去除重复的记录。11g改进了算法，演示如下:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.建立测试例子：
SQL> create table t1 as select rownum id,lpad('a',80,'a') name from dual connect by level<=10000;
SQL> create table t2 as select rownum+1 id,lpad('a',80,'a') name from dual connect by level<=10000;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'t1');
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'t2');
SQL> select * from t1 full outer join  t2 on t1.id=t2.id;

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  44fnr5suadyjd, child number 0

-------------------------------------

select * from t1 full outer join  t2 on t1.id=t2.id

Plan hash value: 53297166

-------------------------------------------------------------------------------------------

| Id  | Operation             | Name     | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |        |    79 (100)|       |       |          |

|   1 |  VIEW                 | VW_FOJ_0 |  10000 |    79   (2)|       |       |          |

|*  2 |   HASH JOIN FULL OUTER|          |  10000 |    79   (2)|  1693K|  1042K| 2652K (0)|

|   3 |    TABLE ACCESS FULL  | T1       |  10000 |    39   (0)|       |       |          |

|   4 |    TABLE ACCESS FULL  | T2       |  10000 |    39   (0)|       |       |          |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

27 rows selected.

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        284  consistent gets

          0  physical reads

          0  redo size

     139875  bytes sent via SQL*Net to client

       1070  bytes received via SQL*Net from client

         52  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

--仅仅284个逻辑读。

2.实际上11G加入隐含参数控制这个因为:设置_optimizer_native_full_outer_join,当前仅仅支持3个选项："choose", "force" and "off". 缺省值="force",
设置其它值禁用这个特性。

SQL> alter session set "_optimizer_native_full_outer_join"=off ;

Session altered.

SQL> set autotrace traceonly ;

SQL> select * from t1 full outer join  t2 on t1.id=t2.id;

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2649631981

-------------------------------------------------------------------------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      | 10100 |  1084K|   157   (1)| 00:00:02 |

|   1 |  VIEW                  |      | 10100 |  1084K|   157   (1)| 00:00:02 |

|   2 |   UNION-ALL            |      |       |       |            |          |

|*  3 |    HASH JOIN OUTER     |      | 10000 |  1660K|    79   (2)| 00:00:01 |

|   4 |     TABLE ACCESS FULL  | T1   | 10000 |   830K|    39   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL  | T2   | 10000 |   830K|    39   (0)| 00:00:01 |

|*  6 |    HASH JOIN RIGHT ANTI|      |   100 |  8900 |    79   (2)| 00:00:01 |

|   7 |     TABLE ACCESS FULL  | T1   | 10000 | 40000 |    39   (0)| 00:00:01 |

|   8 |     TABLE ACCESS FULL  | T2   | 10000 |   830K|    39   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("T1"."ID"="T2"."ID"(+))

   6 - access("T1"."ID"="T2"."ID")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        542  consistent gets

          0  physical reads

          0  redo size

     139875  bytes sent via SQL*Net to client

       1070  bytes received via SQL*Net from client

         52  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

-- 可以发现逻辑读是542，几乎是前者的两倍。

3.再看看10g:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> @hide full_outer_join

old  10:  and a.ksppinm like '%&1%'

new  10:  and a.ksppinm like '%full_outer_join%'

NAME                               DESCRIPTION                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE

---------------------------------- -------------------------------------------------- ---------------------- ---------------------- ----------------------

_optimizer_native_full_outer_join  execute full outer join using native implementaion TRUE                   off                    off

--可以发现10.2.0.3这个参数_optimizer_native_full_outer_join缺省是off。打开测试看看，是否正常。
SQL> create table t1 as select rownum id,lpad('a',80,'a') name from dual connect by level<=10000;
SQL> create table t2 as select rownum+1 id,lpad('a',80,'a') name from dual connect by level<=10000;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'t1');
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'t2');

SQL> alter session set "_optimizer_native_full_outer_join"=force ;

Session altered.

SQL> set autot traceonly

SQL> select * from t1 full outer join  t2 on t1.id=t2.id;

10001 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 53297166

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          | 10000 |  1074K|    65   (2)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 | 10000 |  1074K|    65   (2)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          | 10000 |  1640K|    65   (2)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | T1       | 10000 |   820K|    32   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T2       | 10000 |   820K|    32   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        284  consistent gets

          0  physical reads

          0  redo size

     139691  bytes sent via SQL*Net to client

       1041  bytes received via SQL*Net from client

         52  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

--可以发现在10G缺省设置这个参数_optimizer_native_full_outer_join=off,并没有打开，或许有一些问题^_^。
--11G下这个参数_optimizer_native_full_outer_join=force;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-716995/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-716995/