ITPub博客

首页 > Linux操作系统 > Linux操作系统 > no_unnest与unnest

no_unnest与unnest

Linux操作系统 作者:luckyfriends 时间:2014-03-14 16:23:05 0 删除 编辑

看了pub上一篇关于no_unnest的文章后学习了一番,照着做了一下测试,加深理解:

unnest我们称为对子查询展开,顾名思义,就是别让子查询孤单地嵌套(nest)在里面。
所以un_unnest双重否定代表肯定,即让子查询不展开,让它嵌套(nest)在里面。

SQL> create table t1 tablespace cbo as select * from all_objects;

Table created.

SQL> create table t2 tablespace cbo as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select t1.object_id from t1 where exists
  2  (select 1 from t2 where t1.object_id=t2.object_id*10);

1061 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 40198852

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    18 |    81   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI     |         |     1 |    18 |    81   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T1      | 10943 | 54715 |    40   (0)| 00:00:01 |
|   3 |   VIEW              | VW_SQ_1 | 10944 |   138K|    40   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2      | 10944 | 54720 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="VW_COL_1")


Statistics
----------------------------------------------------------
        281  recursive calls
          0  db block gets
        381  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
       1061  rows processed

如果把unnest禁止,不让子查询自动展开,在子查询中加入提示:

SQL> select t1.object_id from t1 where exists
  2  (select /*+ no_unnest */ 1 from t2 where t1.object_id=t2.object_id*10);

1061 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 | 16491   (1)| 00:03:18 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10943 | 54715 |    40   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |    10 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"*10=:B1))
   3 - filter("T2"."OBJECT_ID"*10=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1404928  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1061  rows processed

这一次,逻辑读居然搞到140万,显然,如果子查询不展开,采用了一个filter的操作,对于每一个t1里的object_id,子查询都要执行一次来检查是否有符合条件的值,也就是说类似于nest loop join,但其效率似乎比nest loop join要好一点

如果加提示,采用nest loop join的方式:
SQL> select /*+ use_nl(t1 t2) */ t1.object_id from t1,t2 where t1.object_id=t2.object_id*10;

1061 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10944 |   106K|   429K  (1)| 01:25:57 |
|   1 |  NESTED LOOPS      |      | 10944 |   106K|   429K  (1)| 01:25:57 |
|   2 |   TABLE ACCESS FULL| T1   | 10943 | 54715 |    40   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     5 |    39   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID"*10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1543243  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1061  rows processed

逻辑读达到了154万!!!

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

下一篇: OIFCFG命令
请登录后发表评论 登录
全部评论
个人博客网 www.leosunny 长期从事数据库相关工作,涉及oracle,mysql, sqlsever,db2 , OGG, SSIS以及非关系数据库和大数据平台等。 希望一直分享一些个人案例和知识,和广大技术宅们共同进步。

注册时间:2009-06-06

  • 博文量
    468
  • 访问量
    3161009