sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle 10g中distinct加强以及anti jion semi jion

时间:2016-12-07 20:49   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle 10g中distinct加强以及anti jion semi jion详细笔记。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 10g中distinct加强以及anti jion,semi jion

SQL> conn roger/roger
Connected.

SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE object_id < 30001;
TABLE created.

SQL> CREATE TABLE t2 AS SELECT * FROM dba_objects WHERE object_id < 20001;
TABLE created.

SQL> SET LINES 160
SQL> SET pagesize 50
SQL> SET pagesize 50

SQL> analyze TABLE t1 compute statistics;
TABLE analyzed.

SQL> analyze TABLE t2 compute statistics;
TABLE analyzed.

SQL> SET autot traceonly
SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
  2  (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');

7 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459

------------------------------------------------------------------------------
| Id  | Operation             | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     8 |   193   (2)| 00:00:03 |
|   1 |  HASH UNIQUE          |      |     1 |     8 |   193   (2)| 00:00:03 |
|*  2 |   HASH JOIN RIGHT ANTI|      | 17022 |   132K|   192   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL  | T1   |  3682 | 14728 |   115   (0)| 00:00:02 |
|   4 |    TABLE ACCESS FULL  | T2   | 19454 | 77816 |    76   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("B"."OWNER"="A"."OWNER")
   3 - FILTER("B"."OWNER"='SYS')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        670  consistent gets
          0  physical reads
          0  redo SIZE
        516  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          7  ROWS processed

我们可以看到此时逻辑读之和为671,没有排序消耗。下面修改一个隐含参数继续比较:

SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled"=FALSE;
SESSION altered.

SQL> SELECT DISTINCT a.owner FROM t2 a WHERE NOT EXISTS
  2  (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');

7 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1130070542

------------------------------------------------------------------------------
| Id  | Operation             | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     8 |   193   (2)| 00:00:03 |
|   1 |  SORT UNIQUE          |      |     1 |     8 |   193   (2)| 00:00:03 |
|*  2 |   HASH JOIN RIGHT ANTI|      | 17022 |   132K|   192   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL  | T1   |  3682 | 14728 |   115   (0)| 00:00:02 |
|   4 |    TABLE ACCESS FULL  | T2   | 19454 | 77816 |    76   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("B"."OWNER"="A"."OWNER")
   3 - FILTER("B"."OWNER"='SYS')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        670  consistent gets
          0  physical reads
          0  redo SIZE
        516  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          7  ROWS processed

我们发现,此时虽然逻辑读是一样的,但是多了1个memory的排序操作,可见10.2里面跟以前版本在distinct 排序操作上有更进一步的优化了。

我们继续来看上面的执行计划,发现是HASH JOIN RIGHT ANTI,也就是anti jion。

我们知道oracle 在处理exist和in时,是使用的semi jion,然而在处理not exists和not in时是用的anti jion,下面我来验证一下:

SQL> SELECT DISTINCT a.owner FROM t2 a WHERE  EXISTS
  2  (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS')
  3  /

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1951262108

----------------------------------------------------------------------------
| Id  | Operation           | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   193   (2)| 00:00:03 |
|   1 |  SORT UNIQUE NOSORT |      |     1 |     8 |   193   (2)| 00:00:03 |
|*  2 |   HASH JOIN SEMI    |      |  2432 | 19456 |   192   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T2   |  2432 |  9728 |    76   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |  3682 | 14728 |   115   (0)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("B"."OWNER"="A"."OWNER")
   3 - FILTER("A"."OWNER"='SYS')
   4 - FILTER("B"."OWNER"='SYS')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        268  consistent gets
          0  physical reads
          0  redo SIZE
        409  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
可以看到此时是semi jion了。

下面来测试下not in的情况:

SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner  NOT IN
  2  (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');

7 ROWS selected.

Execution Plan
---------------------------------------------Oracleо-------------
Plan hash VALUE: 2122336124

-----------------------------------------------------------------------------
| Id  | Operation            | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     8 |    32 |  1000   (1)| 00:00:13 |
|   1 |  HASH UNIQUE         |      |     8 |    32 |  1000   (1)| 00:00:13 |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | T2   | 19454 | 77816 |    76   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T1   |   460 |  1840 |   115   (0)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE :B1='SYS' AND
              "B"."OWNER"=:B2 AND LNNVL("B"."OWNER":B3)))
   4 - FILTER(:B1='SYS')
   5 - FILTER("B"."OWNER"=:B1 AND LNNVL("B"."OWNER":B2))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        268  consistent gets
          0  physical reads
          0  redo SIZE
        516  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          7  ROWS processed

可以看到这里非常奇怪,居然走filter了。

SQL> ALTER TABLE t1 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.

SQL> ALTER TABLE t2 MODIFY (owner VARCHAR2(30) NOT NULL);
TABLE altered.

SQL> SELECT DISTINCT a.owner FROM t2 a WHERE a.owner  NOT IN
  2  (SELECT b.owner FROM t1 b WHERE b.owner=a.owner AND b.owner='SYS');

7 ROWS selected.

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4203366459

------------------------------------------------------------------------------
| Id  | Operation             | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     8 |   193   (2)| 00:00:03 |
|   1 |  HASH UNIQUE          |      |     1 |     8 |   193   (2)| 00:00:03 |
|*  2 |   HASH JOIN RIGHT ANTI|      | 17022 |   132K|   192   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL  | T1   |  3682 | 14728 |   115   (0)| 00:00:02 |
|   4 |    TABLE ACCESS FULL  | T2   | 19454 | 77816 |    76   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("B"."OWNER"="A"."OWNER")
   3 - FILTER("B"."OWNER"='SYS')

Statistics
----------------------------------------------------------
        349  recursive calls
          0  db block gets
        715  consistent gets
          1  physical reads
          0  redo SIZE
        516  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          8  sorts (memory)
          0  sorts (disk)
          7  ROWS processed

可以看到,目前走hash jion了,问兔子,他是这样说的:not in如果没有约束或语句避免null问题,内部会使用lnnvl函数,11g有null aware优化,比10g好点,因此not in写法特别注意null,lnnvl是undocument函数.

所以我这里将自动owner修改为not null,下面来看看11g中是不是这样的,我这里以11gR2为例:

——10.2.0.5

SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id  NOT IN
  2  (SELECT b.object_id FROM t1 b);

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3142026835

------------------------------------------------------------------------------------
| Id  | Operation           | Name | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 19453 | 77812 |       |  1122K  (1)| 03:44:29 |
|   1 |  HASH UNIQUE        |      | 19453 | 77812 |   240K|  1122K  (1)| 03:44:29 |
|*  2 |   FILTER            |      |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T2   | 19454 | 77816 |       |    76   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |     1 |     4 |       |   115   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - FILTER( NOT EXISTS (SELECT 0 FROM "T1" "B" WHERE
              LNNVL("B"."OBJECT_ID":B1)))
   4 - FILTER(LNNVL("B"."OBJECT_ID":B1))
——-11.2.0.2

SQL>  SELECT * FROM v$version WHERE rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production

SQL>
SQL> SET autot traceonly EXP
SQL> SELECT DISTINCT a.object_id FROM t2 a WHERE a.object_id  NOT IN
  2  (SELECT b.object_id FROM t1 b);

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1298667172

----------------------------------------------------------------------------
| Id  | Operation           | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   195 |  1560 |   196   (2)| 00:00:03 |
|   1 |  HASH UNIQUE        |      |   195 |  1560 |   196   (2)| 00:00:03 |
|*  2 |   HASH JOIN ANTI NA |      |   195 |  1560 |   195   (1)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| T2   | 19490 | 77960 |    77   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 29490 |   115K|   117   (0)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

可以看到11g中,即使object_id字段默认允许为空,sql仍然可以走hash anti jion,而不是去像10g一样走filter。 这里的NA表示null aware.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 10g中distinct加强以及anti jion semi jion

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1375.html

Oracle研究中心

关键词:

anti jion

semi jion

Oracle 10g中distinct加强