本实验测试的是子查询中包括主表的过滤条件时，子查询是否会unnest，通常情况下，unnest的效率会更高，但是如果遇到子查询只还回几行的时候，其实我们可以把子查询当成一个查询结果用于做驱动表，从10.2.0.5以后的版本，子查询中包括 主表的过滤条件时，" />
当前位置： 首页 > 调优 > 正文

子查询中有主表过滤条件，子查询是否unnest

May052014
作者：huangtingzhong   发布：2014-05-05 22:26   分类：调优   阅读：200 views   抢沙发  

 

欢迎大家加入ORACLE超级群：
17115662 免费解决各种ORACLE问题

本实验测试的是子查询中包括主表的过滤条件时，子查询是否会unnest，通常情况下，unnest的效率会更高，但是如果遇到子查询只还回几行的时候，其实我们可以把子查询当成一个查询结果用于做驱动表，从10.2.0.5以后的版本，子查询中包括 主表的过滤条件时，也会自动unnset。下面测试的版本，包括 10.2.0.1/10.2.0.4/10.2.0.5，最后测试了如果不自动unnest，怎么优化这样的SQL

 

1，10.2.0.1版本

2，10.2.0.4版本

3，10.2.0.5版本

4，10.2.0.1版本的优化技巧

 

 

创建测试表

SQL> drop table scott.test;

Table dropped.

SQL> drop table scott.test1;

Table dropped.

SQL> create table scott.test as select * from dba_objects;

Table created.

SQL> create table scott.test1 as select * from dba_objects where rownum<1000;

Table created.

 

 

1，10.2.0.1版本

 

SQL> select * from v$version;

 

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi

PL/SQL Release 10.2.0.4.0 – Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

 

SQL> set lines 200

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

 

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 | 85477   (1)| 00:17:06 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 44716 |  4192K|   158   (1)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE

              :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))

   3 – filter(:B1=’SYS’)

   4 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

     339145  consistent gets

          0  physical reads

          0  redo size

      29653  bytes sent via SQL*Net to client

       1152  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

这里发现，子查询根本没有unnest

 

下面通过提示要强制unnest

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select /*+ unnest */ 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

 

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 |   114K  (1)| 00:22:49 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 58924 |  5524K|   157   (2)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

仍然无效，说明根本不能进行unnest

 

2,10.2.0.4版本

 

SQL> select * from v$version;

 

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi

PL/SQL Release 10.2.0.4.0 – Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

 

 

SQL> set lines 200

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

 

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 | 85477   (1)| 00:17:06 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 44716 |  4192K|   158   (1)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE

              :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))

   3 – filter(:B1=’SYS’)

   4 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

     339145  consistent gets

          0  physical reads

          0  redo size

      29653  bytes sent via SQL*Net to client

       1152  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

 

下面通过提示来强制

 

SQL> set autotrace traceonly;

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select /*+ unnest */ 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

 

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 | 85477   (1)| 00:17:06 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 44716 |  4192K|   158   (1)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter( EXISTS (SELECT /*+ UNNEST */ 0 FROM “SCOTT”.”TEST1″ “B”

              WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))

   3 – filter(:B1=’SYS’)

   4 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

提示仍然无效

 

3.10.2.0.5

SQL> select * from v$version;

 

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi

PL/SQL Release 10.2.0.5.0 – Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 – Production

NLSRTL Version 10.2.0.5.0 – Production

 

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

 

Execution Plan

———————————————————-

Plan hash value: 2133522026

 

——————————————————————————

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

——————————————————————————

|   0 | SELECT STATEMENT     |       |   999 |   106K|   165   (2)| 00:00:02 |

|*  1 |  HASH JOIN RIGHT SEMI|       |   999 |   106K|   165   (2)| 00:00:02 |

|   2 |   TABLE ACCESS FULL  | TEST1 |   999 | 12987 |     5   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL  | TEST  | 26856 |  2517K|   159   (2)| 00:00:02 |

——————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

   3 – filter(“A”.”OWNER”=’SYS’)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

        528  recursive calls

          0  db block gets

        923  consistent gets

        712  physical reads

        332  redo size

      29656  bytes sent via SQL*Net to client

       1152  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

        913  rows processed

 

 

这里发生了Unnest

 

下面是测试unnest与no_unnest的效率

 

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

———————————————————-

Plan hash value: 2133522026

 

——————————————————————————

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

——————————————————————————

|   0 | SELECT STATEMENT     |       |   999 |   106K|   165   (2)| 00:00:02 |

|*  1 |  HASH JOIN RIGHT SEMI|       |   999 |   106K|   165   (2)| 00:00:02 |

|   2 |   TABLE ACCESS FULL  | TEST1 |   999 | 12987 |     5   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL  | TEST  | 26856 |  2517K|   159   (2)| 00:00:02 |

——————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

   3 – filter(“A”.”OWNER”=’SYS’)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

        770  consistent gets

          0  physical reads

          0  redo size

      29656  bytes sent via SQL*Net to client

       1152  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

 

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select /*+ no_unnest */ 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

Elapsed: 00:00:00.94

 

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 |   107K  (1)| 00:21:28 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 55572 |  5209K|   159   (2)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “SCOTT”.”TEST1″

              “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))

   3 – filter(:B1=’SYS’)

   4 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

         15  recursive calls

          0  db block gets

     340705  consistent gets

          0  physical reads

          0  redo size

      29656  bytes sent via SQL*Net to client

       1152  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

 

4，10.2.0.1版本中怎么优化

SQL> set timing on;

SQL> select a.object_name, owner

  2    from scott.test a

  3   where exists (select 1

  4            from scott.test1 b

  5           where a.object_id = b.object_id

  6             and a.owner = ‘SYS’);

 

913 rows selected.

 

Elapsed: 00:00:00.91

这里发现使用时间是00.91

Execution Plan

———————————————————-

Plan hash value: 353051353

 

—————————————————————————–

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

—————————————————————————–

|   0 | SELECT STATEMENT    |       |     1 |    96 |   114K  (1)| 00:22:49 |

|*  1 |  FILTER             |       |       |       |            |          |

|   2 |   TABLE ACCESS FULL | TEST  | 58924 |  5524K|   157   (2)| 00:00:02 |

|*  3 |   FILTER            |       |       |       |            |          |

|*  4 |    TABLE ACCESS FULL| TEST1 |    10 |   130 |     2   (0)| 00:00:01 |

—————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE

              :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2))

   3 – filter(:B1=’SYS’)

   4 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

     338788  consistent gets

          0  physical reads

          0  redo size

      29654  bytes sent via SQL*Net to client

       1129  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

 

下面是优化后的效果

SQL> with b as

  2   (select /*+ materialize */

  3     object_id

  4      from scott.test1)

  5  select a.object_name, a.owner

  6    from scott.test a

  7   where exists (select 1

  8            from b

  9           where a.object_id = b.object_id

 10             and a.owner = ‘SYS’);

 

913 rows selected.

 

Elapsed: 00:00:00.82

这里的效果，时间减少不是很多，因为子查询中的表本来就不大，

Execution Plan

———————————————————-

Plan hash value: 1200970861

 

——————————————————————————————————-

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

——————————————————————————————————-

|   0 | SELECT STATEMENT           |                          |     1 |    96 |   114K  (1)| 00:22:59 |

|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |

|   2 |   LOAD AS SELECT           | TEST                     |       |       |            |          |

|   3 |    TABLE ACCESS FULL       | TEST1                    |   999 | 12987 |     5   (0)| 00:00:01 |

|*  4 |   FILTER                   |                          |       |       |            |          |

|   5 |    TABLE ACCESS FULL       | TEST                     | 58924 |  5524K|   157   (2)| 00:00:02 |

|*  6 |    FILTER                  |                          |       |       |            |          |

|*  7 |     VIEW                   |                          |   999 | 12987 |     2   (0)| 00:00:01 |

|   8 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6604_876E8 |   999 | 12987 |     2   (0)| 00:00:01 |

——————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   4 – filter( EXISTS (SELECT /*+ */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE (“T1”) */ “C0”

              “OBJECT_ID” FROM “SYS”.”SYS_TEMP_0FD9D6604_876E8″ “T1”) “B” WHERE :B1=’SYS’ AND

              “B”.”OBJECT_ID”=:B2))

   6 – filter(:B1=’SYS’)

   7 – filter(“B”.”OBJECT_ID”=:B1)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          2  recursive calls

          9  db block gets

      91726  consistent gets  这里发现逻辑读减少很多的。

          2  physical reads

        604  redo size

      29654  bytes sent via SQL*Net to client

       1129  bytes received via SQL*Net from client

         62  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        913  rows processed

本文固定链接: http://www.htz.pw/?p=375 | 认真就输

该日志由 huangtingzhong 于2014年05月05日发表在 调优 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 子查询中有主表过滤条件，子查询是否unnest | 认真就输
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！