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

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

 

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

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

 

110.2.0.1版本

210.2.0.4版本

310.2.0.5版本

410.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.

 

 

110.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

 

下面是测试unnestno_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

 

410.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/2014/05/05/%e5%ad%90%e6%9f%a5%e8%af%a2%e4%b8%ad%e6%9c%89%e4%b8%bb%e8%a1%a8%e8%bf%87%e6%bb%a4%e6%9d%a1%e4%bb%b6%ef%bc%8c%e5%ad%90%e6%9f%a5%e8%af%a2%e6%98%af%e5%90%a6unnest.html | 认真就输

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