当前位置: 首页 > 调优 > 正文

FILTER中,子查询执行的次数

    

 

 

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

 

本次测试来源于QQ群里聊天记录:关于sqlfilter过滤的时候,子查询执行的次数。因为我们知道子查询执行的次数,决定了我们SQL的性能

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

2,创建测试表与索引

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

 

Table created.

 

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

 

Table created.

 

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

 

 

Table created.

 

SQL>

SQL> create index scott.ind_htz3_object_owner on scott.htz3(object_id,owner);

 

Index created.

3,驱动表无重复值

SQL> select *

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id

  4      and a.object_id in (select object_id

  5                            from scott.htz3 c

  6                           where c.owner = ‘SCOTT’

  7                             and a.owner = ‘SYS’)

  8   ;

 

no rows selected

 

SQL> @plan_by_last.sql

SQL> set echo off

Enter value for sqlid:

old   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(‘&sqlid’,null),null,’ALLSTATS LAST’))

new   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(”,null),null,’ALLSTATS LAST’))

 

PLAN_TABLE_OUTPUT

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

select *    from scott.htz1 a, scott.htz2 b   where a.object_id = b.object_id     and a.object_id in (select object_id

                       from scott.htz3 c                          where c.owner = ‘SCOTT’                            and

a.owner = ‘SYS’)

 

Plan hash value: 286543034

 

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

| Id  | Operation           | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

|*  1 |  FILTER             |                       |      1 |        |      0 |00:00:00.96 |   47310 |       |       |          |

|*  2 |   HASH JOIN         |                       |      1 |  38536 |  50068 |00:00:00.60 |    1386 |  7548K|  2031K| 8473K (0)|

|   3 |    TABLE ACCESS FULL| HTZ1                  |      1 |  38535 |  50068 |00:00:00.05 |     693 |       |       |          |

|   4 |    TABLE ACCESS FULL| HTZ2                  |      1 |  50965 |  50069 |00:00:00.10 |     693 |       |       |          |

|*  5 |   FILTER            |                       |  50068 |        |      0 |00:00:00.23 |   45924 |       |       |          |

|*  6 |    INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER |  22962 |      1 |      0 |00:00:00.10 |   45924 |       |       |          |

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

 

Predicate Information (identified by operation id):

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

 

   1 – filter( IS NOT NULL)

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

   5 – filter(:B1=’SYS’)

   6 – access(“OBJECT_ID”=:B1 AND “C”.”OWNER”=’SCOTT’)

 

Note

—–

   – dynamic sampling used for this statement

 

这里filter执行了50068次,而INDEX只执行了22962次,这里是因为有主表查询,把主表的过滤列推过来了

31 rows selected.

 

SQL>

SQL> select count(*)

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id;

 

  COUNT(*)

———-

     50068

4,有重复的值

SQL> insert into scott.htz1 select * from scott.htz1;

 

50068 rows created.

 

SQL> commit;

 

Commit complete.

 

 

SQL> select count(*)

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id;

 

  COUNT(*)

———-

    100136

 

这里看到两个表连接后还回了100316行记录

SQL> select count(distinct a.object_id) count_distinct

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id;

 

COUNT_DISTINCT

————–

         50068

这里看到表还回的不同值为50068

 

SQL> @plan_by_last.sql

SQL> set echo off

Enter value for sqlid:

old   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(‘&sqlid’,null),null,’ALLSTATS LAST’))

new   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(”,null),null,’ALLSTATS LAST’))

 

PLAN_TABLE_OUTPUT

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

SQL_ID  1gktx239rcv6f, child number 0

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

select *    from scott.htz1 a, scott.htz2 b   where a.object_id = b.object_id     and a.object_id in (select object_id

              from scott.htz3 c                          where c.owner = ‘SCOTT’                            and a.owner = ‘SYS’)

 

Plan hash value: 286543034

 

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

| Id  | Operation           | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|*  1 |  FILTER             |                       |      1 |        |      0 |00:00:01.29 |   48000 |     68 |       |       |          |

|*  2 |   HASH JOIN         |                       |      1 |  38536 |    100K|00:00:00.81 |    2076 |     68 |    13M|  2031K|   17M (0)|

|   3 |    TABLE ACCESS FULL| HTZ1                  |      1 |  38535 |    100K|00:00:00.10 |    1383 |      0 |       |       |          |

|   4 |    TABLE ACCESS FULL| HTZ2                  |      1 |  50965 |  50069 |00:00:00.10 |     693 |     68 |       |       |          |

|*  5 |   FILTER            |                       |  50068 |        |      0 |00:00:00.24 |   45924 |      0 |       |       |          |

|*  6 |    INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER |  22962 |      1 |      0 |00:00:00.10 |   45924 |      0 |       |       |          |

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

 

Predicate Information (identified by operation id):

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

 

   1 – filter( IS NOT NULL)

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

   5 – filter(:B1=’SYS’)

   6 – access(“OBJECT_ID”=:B1 AND “C”.”OWNER”=’SCOTT’)

 

Note

—–

   – dynamic sampling used for this statement

 

通过执行计划,我们可以看到FILTER这里仍然50068,说明是DISCOUNT的值的总行数,而不是主表还回的行数

30 rows selected.

 

5,连接列包括多个空值的情况

SQL> update scott.htz1 set object_id=” where rownum<10000;

 

9999 rows updated.

 

SQL> commit;

 

Commit complete.

 

 

SQL> update scott.htz2 set object_id=” where rownum<10000;

 

9999 rows updated.

 

SQL> commit;

 

Commit complete.

 

 

 

SQL> select count(distinct a.object_id) count_distinct

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id;

 

COUNT_DISTINCT

————–

         40069

        

连接列还回的DISCOUNT的值

        

SQL> select *

  2     from scott.htz1 a, scott.htz2 b

  3    where a.object_id = b.object_id

  4      and a.object_id in (select object_id

  5                            from scott.htz3 c

  6                           where c.owner = ‘SCOTT’

  7                             and a.owner = ‘SYS’)

  8   ;

 

no rows selected

 

SQL> @plan_by_last.sql

SQL> set echo off

Enter value for sqlid:

old   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(‘&sqlid’,null),null,’ALLSTATS LAST’))

new   1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl(”,null),null,’ALLSTATS LAST’))

 

PLAN_TABLE_OUTPUT

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

SQL_ID  1gktx239rcv6f, child number 0

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

select *    from scott.htz1 a, scott.htz2 b   where a.object_id = b.object_id     and a.object_id in (select object_id

              from scott.htz3 c                          where c.owner = ‘SCOTT’                            and a.owner = ‘SYS’)

 

Plan hash value: 286543034

 

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

| Id  | Operation           | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|*  1 |  FILTER             |                       |      1 |        |      0 |00:00:01.15 |   34646 |     19 |       |       |          |

|*  2 |   HASH JOIN         |                       |      1 |  38536 |  80138 |00:00:00.86 |    2076 |     19 |    12M|  2029K|   15M (0)|

|   3 |    TABLE ACCESS FULL| HTZ1                  |      1 |  38535 |    100K|00:00:00.10 |    1383 |     19 |       |       |          |

|   4 |    TABLE ACCESS FULL| HTZ2                  |      1 |  50965 |  50069 |00:00:00.09 |     693 |      0 |       |       |          |

|*  5 |   FILTER            |                       |  40069 |        |      0 |00:00:00.18 |   32570 |      0 |       |       |          |

|*  6 |    INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER |  16285 |      1 |      0 |00:00:00.07 |   32570 |      0 |       |       |          |

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

 

Predicate Information (identified by operation id):

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

 

   1 – filter( IS NOT NULL)

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

   5 – filter(:B1=’SYS’)

   6 – access(“OBJECT_ID”=:B1 AND “C”.”OWNER”=’SCOTT’)

 

Note

—–

   – dynamic sampling used for this statement

 

 

30 rows selected.

5,总结

通过上面的测试,我们可以看到FILTER过滤的时候,子查询执行的次数为count(DISCOUNT  主键连接列)

 

本文固定链接: http://www.htz.pw/2014/04/28/filter%e4%b8%ad%ef%bc%8c%e5%ad%90%e6%9f%a5%e8%af%a2%e6%89%a7%e8%a1%8c%e7%9a%84%e6%ac%a1%e6%95%b0.html | 认真就输

该日志由 huangtingzhong 于2014年04月28日发表在 调优 分类下,
原创文章转载请注明: FILTER中,子查询执行的次数 | 认真就输

报歉!评论已关闭.