当心外部连接中的ON子句

       在SQL tuning中，不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右此即
为一例。通过修正该SQL的写法之后，过高的逻辑读呈数量级下降以及SQL语句执行时间也大幅下降。下面给出一个列子来演示该情形。

一、创建演示环境

-->当前数据库版本 SQL> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod -->创建演示表并插入记录 SQL> create table t as select empno,ename,job,sal,deptno 2 from emp where 1=2; SQL> insert into t select empno,ename,job,sal,deptno 2 from emp e where empno=(select max(empno) from emp where deptno=e.deptno); SQL> insert into t(empno,ename,job,sal) values(8888,'ROBINSON','DBA',2000); SQL> insert into t(empno,ename,job,sal) values(9999,'JACKSON','CLERK',2500); SQL> commit; SQL> analyze table t compute statistics; SQL> select * from t; EMPNO ENAME JOB SAL DEPTNO ---------- ---------- --------- ---------- ---------- 7934 MILLER CLERK 1300 10 7902 FORD ANALYST 3000 20 7900 JAMES CLERK 950 30 8888 ROBINSON DBA 2000 9999 JACKSON CLERK 2500 -->使用left join连接查看数据，此时表t中所有记录被返回 SQL> select empno,ename,sal,dname from t left join dept d on t.deptno=d.deptno; EMPNO ENAME SAL DNAME ---------- ---------- ---------- -------------- 7934 MILLER 1300 ACCOUNTING 7902 FORD 3000 RESEARCH 7900 JAMES 950 SALES 8888 ROBINSON 2000 9999 JACKSON 2500 -->下面同样是使用left join连接，但在on子句中增加了过滤条件t.sal>=2000 -->从下面的返回结果可知，t.sal>=2000子句并没有过滤掉sal小于2000的记录 SQL> select empno,ename,sal,dname from t left join dept d -->简称语句A 2 on(t.deptno=d.deptno and t.sal>=2000); EMPNO ENAME SAL DNAME ---------- ---------- ---------- -------------- 7934 MILLER 1300 7902 FORD 3000 RESEARCH 7900 JAMES 950 8888 ROBINSON 2000 9999 JACKSON 2500 -->使用left join连接，将过滤条件放到where 子句中 -->此时仅仅t.sal>=2000且符合t.deptno=d.deptno的记录被返回(结果与所期望一致) SQL> select empno,ename,sal,dname from t left join dept d -->简称语句B 2 on t.deptno=d.deptno where t.sal>=2000; EMPNO ENAME SAL DNAME ---------- ---------- ---------- -------------- 7902 FORD 3000 RESEARCH 8888 ROBINSON 2000 9999 JACKSON 2500 -->查看执行计划 SQL> set autotrace traceonly exp; -->语句A(过滤条件位于on 子句中的情形)的执行计划 SQL> select empno,ename,sal,dname from t left join dept d 2 (on t.deptno=d.deptno and t.sal>=2000); Execution Plan ---------------------------------------------------------- Plan hash value: 2195752858 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 120 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 5 | 120 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | T | 5 | 70 | 3 (0)| 00:00:01 | | 3 | VIEW | | 1 | 10 | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T"."DEPTNO"="D"."DEPTNO") -->重点关注这里的谓词信息,两个过滤条件合在一起 filter("T"."DEPTNO" IS NOT NULL AND "T"."SAL">=2000) -->从执行计划来看位于第5步为INDEX UNIQUE SCAN Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11 consistent gets -->此时的逻辑读为11 0 physical reads 0 redo size 696 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed -->语句B(将谓词信息置于到where子句中的情形)的执行计划 SQL> select empno,ename,sal,dname from t left join dept d 2 on t.deptno=d.deptno where t.sal>=2000; Execution Plan ---------------------------------------------------------- Plan hash value: 832694258 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 81 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 3 | 81 | 4 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL | T | 3 | 42 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."SAL">=2000) -->此时的谓词信息分为两部分，"T"."SAL">=2000位于第二步 4 - access("T"."DEPTNO"="D"."DEPTNO"(+)) -->此条谓词信息用于实现表连接 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10 consistent gets -->此时的逻辑读为10，由于2 - filter("T"."SAL">=2000)过滤后，内部循环少执行了一次 0 physical reads 0 redo size 658 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed /**************************************************/ /* Author: Robinson Cheng */ /* Blog: http://blog.csdn.net/robinson_0612 */ /* MSN: robinson_0612@hotmail.com */ /* QQ: 645746311 */ /**************************************************/ -->从上面的观察中发现上述两条SQL语句执行计划并非最佳，存在改良的余地 -->由于是nested loops outer,因此考虑在表t的谓词列增加索引以快速过滤记录 SQL> create index i_t_sal on t(sal); SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true); -->增加索引后两个语句的执行情况 -->语句A的执行计划以及统计信息没有发生任何变化 SQL> select empno,ename,sal,dname from t left join dept d 2 on (t.deptno=d.deptno and t.sal>=2000); Execution Plan ---------------------------------------------------------- Plan hash value: 2195752858 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 140 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 5 | 140 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | T | 5 | 90 | 3 (0)| 00:00:01 | | 3 | VIEW | | 1 | 10 | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T"."DEPTNO"="D"."DEPTNO") filter("T"."DEPTNO" IS NOT NULL AND "T"."SAL">=2000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 696 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed -->语句B的执行计划发生变化，原来的全表扫描变为索引扫描 SQL> select empno,ename,sal,dname from t left join dept d 2 on t.deptno=d.deptno where t.sal>=2000; Execution Plan ---------------------------------------------------------- Plan hash value: 2452308905 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 93 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 3 | 93 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T | 3 | 54 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I_T_SAL | 3 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T"."SAL">=2000) 5 - access("T"."DEPTNO"="D"."DEPTNO"(+)) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets -->逻辑读也由10下降到6 0 physical reads 0 redo size 658 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed

二、总结
  1、尽可能避免SQL不良写法导致的不良后果
  2、此例中由于将谓词信息放到ON子句中，在数据量庞大的表(百万行)连接中，则该写法导致过多的物理和逻辑I/O,使得中间结果集庞大
  3、谓词信息放到ON子句中同时也导致索引失效
  4、尽可能的在满足需求的情况下减小中间结果集

三、更多参考

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标  

Oracle ROWID  

收缩表段(shrink space)

临时表空间的管理与受损恢复  

O racle 彻底 kill session  

Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化(一)

Buffer cache 的调整与优化(二)  

Oracle 表缓存(caching table)的使用

PGA的设置与调整

 

当心外部连接中的ON子句