ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120223]full outer join.txt

[20120223]full outer join.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-23 11:33:45 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2326
  • 访问量
    6055418