欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题
本次测试来源于QQ群里聊天记录：关于sql走filter过滤的时候，子查询执行的次数。因为我们知道子查询执行的次数，决定了我们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 主键连接列)
报歉！评论已关闭。