a我考网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 393|回复: 2

[考试辅导] Oracle认证辅导:SQL条件的顺序对性能的影响

[复制链接]
发表于 2012-8-4 14:06:19 | 显示全部楼层 |阅读模式
经常有人问到oracle中的Where子句的条件书写顺序是否对SQL性能有影响,我的直觉是没有影响,因为如果这个顺序有影响,Oracle应该早就能够做到自动优化,但一直没有关于这方面的确凿证据。在网上查到的文章,一般认为在RBO优化器模式下无影响(10G开始,缺省为RBO优化器模式),而在CBO优化器模式下有影响,主要有两种观点:  a.能使结果最少的条件放在最右边,SQL执行是按从右到左进行结果集的筛选的;
: u0 _: u" u: J5 c. F% V  b.有人试验表明,能使结果最少的条件放在最左边,SQL性能更高。
2 b* P) N) a6 u3 J5 f  查过oracle8到11G的在线文档,关于SQL优化相关章节,没有任何文档说过where子句中的条件对SQL性能有影响,到底哪种观点是对的,没有一种确切的结论,只好自己来做实验证明。结果表明,SQL条件的执行是从右到左的,但条件的顺序对SQL性能没有影响。
% a' U  y& t/ F2 [5 _" W9 x: {  实验一:证明了SQL的语法分析是从右到左的
. h8 `! |3 C/ H9 P9 s' o  下面的试验在9i和10G都可以得到相同的结果: 第1条语句执行不会出错,第2条语句会提示除数不能为零。2 K* R* l" ^% o, }: |
  1.Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2;, o, u5 J9 ^& _# B: |0 _
  2.Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1;
0 h( r' j2 Q3 ^6 A) W2 I* A( ?1 F" d  证明了SQL的语法分析是从右到左的。1 L7 F0 @$ C1 D5 q9 P2 c( I
  实验二:证明了SQL条件的执行是从右到左的
' S- [% `( l8 y3 w! @. L1 d1 E& u. ?  drop table temp;
8 i6 R, I+ ^* w  U6 }; f  create table temp( t1 varchar2(10),t2 varchar2(10));
6 p0 Y' T3 `4 r  insert into temp values('zm','abcde');
) y9 k; @1 {' b  insert into temp values('sz','1');
+ `# \2 a2 x' Q2 _+ w  insert into temp values('sz','2');
3 _8 ~! W2 ]; W; ~8 {; I  commit;! u1 ]7 A" c6 w4 x2 ~6 H5 h
  1. select * from temp where to_number(t2)>1 and t1='sz';& a' B% J1 L1 a2 B. z5 t8 H( k
  2. select * from temp where t1='sz' and to_number(t2)>1;
0 C, O8 E# |* [  在9i上执行, 第1条语句执行不会出错,第2条语句会提示“无效的数字”
  n+ E7 w3 {: v  E% o7 Y  在10G上执行,两条语句都不会出错。
5 b) g0 Z) ^% e7 L+ }8 z/ P$ D0 W4 i  说明:9i上,SQL条件的执行确实是从右到左的,但是10G做了什么调整呢?5 b& ]# s  _5 J5 n* q
  实验三:证明了在10g上SQL条件的执行是从右到左的
! A8 W: o, z8 K0 M! [" L; C  Create Or Replace Function F1(v_In Varchar2) Return Varchar2 Is
# a7 v0 Y: b) E* C7 Y: l  Begin" M+ ?: L' V: P' N0 }
  Dbms_Output.Put_Line('exec F1');* D% k; f: M# G2 l9 _4 G
  Return v_In;' g* Z1 q: x# Y. e
  End F1;
4 C5 f) s- S8 }& B; Q  /, H$ @+ l0 J& _  D% w! [; D( g* c
  Create Or Replace Function F2(v_In Varchar2) Return Varchar2 Is
4 X2 U* y- r! G2 c8 K  Begin" B2 O  @6 i# r# I7 G6 T
  Dbms_Output.Put_Line('exec F2');
7 j* x3 v. V& x7 s$ |2 y0 `( r  Return v_In;
9 N* K2 I; D; {7 ~& @$ e6 \  End F2;$ _4 C$ O5 |' J+ R
  /
回复

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:20 | 显示全部楼层

Oracle认证辅导:SQL条件的顺序对性能的影响

 SQL> set serverout on;  SQL> select 1 from dual where f1('1')='1' and f2('1')='1';
( ~( g& s" w$ d5 e& E, M  1
& |: I* K/ \0 |$ B' ?  ----------
, W1 ?5 f7 ]7 J! I. L+ a) q  1  i7 y) R$ d% U7 M% f1 q; V7 O
  exec F2' d% g% V* J2 f
  exec F13 i0 s! v2 {2 [4 s9 J
  SQL> select 1 from dual where f2('1')='1' and f1('1')='1';
6 S  E/ y# d$ w9 z9 ?  1
! `- M: E+ B+ a0 V* G! y: a- l8 I  ----------
2 ~# ?6 c$ ?) q4 @4 H9 h  1
: E+ x: \! L% }, I4 e  exec F1. d( E) Y1 R0 a9 I, N9 Z
  exec F2, U. B! ]. O% h" o
  结果表明,SQL条件的执行顺序是从右到左的。9 ]- l( G, R3 i
  那么,根据这个结果来分析,把能使结果最少的条件放在最右边,是否会减少其它条件执行时所用的记录数量,从而提高性能呢?$ C9 G- M" k1 i! ~% K5 h
  例如:下面的SQL条件,是否应该调整SQL条件的顺序呢?
$ Z( E0 K7 l, p  Where A.结帐id Is Not Null
6 ^1 X; P  W  [, H3 o  And A.记录状态0& ?& |+ r7 L9 k; ~9 i2 Y
  And A.记帐费用=1! U: e/ r0 o6 j/ e$ z
  And (Nvl(A.实收金额, 0)Nvl(A.结帐金额, 0) Or Nvl(A.结帐金额, 0)=0)
6 J! b3 h7 @% R1 H+ y2 l  And A.病人ID=[1] And Instr([2],','||Nvl(A.主页ID,0)||',')>0
. U9 O4 `- o  n4 \1 J5 @9 F9 F1 b  And A.登记时间Between [3] And [4]
. a+ M3 w' T! A5 n$ V  And A.门诊标志1
6 _- y0 ~: I: M9 n. a/ R  实际上,从这条SQL语句的执行计划来分析,Oracle首先会找出条件中使用索引或表间连接的条件,以此来过滤数据集,然后对这些结果数据块所涉及的记录逐一检查是否符合所有条件,所以条件顺序对性能几乎没有影响。
, q  s6 B  c6 C$ K; I3 n  如果没有索引和表间连接的情况,条件的顺序是否对性能有影响呢?再来看一个实验。
9 _7 _* t( V- f  实验四:证明了条件的顺序对性能没有影响。
* u% u6 }* i) a- \. z* t8 }  SQL> select count(*) from诊疗项目目录where操作类型='1';
1 C6 _$ H8 L2 {% }. F. x5 d  COUNT(*)' x& R$ @0 |: |: E" H% b9 {
  ----------
+ @0 y3 s2 A- |2 ^8 w' f  3251" i( r, E; J% O# r
  SQL> select count(*) from诊疗项目目录where类别='Z';$ M' M$ D3 }  k
  COUNT(*)
6 M1 |; L' n% s. g1 l5 Z  ----------
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:21 | 显示全部楼层

Oracle认证辅导:SQL条件的顺序对性能的影响

170  SQL> select count(*) from诊疗项目目录where类别='Z' and操作类型='1';- V0 {/ x7 {- F' F
  COUNT(*)
* Q/ _3 Y9 Y/ X4 K0 r  ----------* J0 |, [- o) h& L: x
  1& x' j! R( Q6 E9 L
  Declare" l, `! J5 I. v' ]$ W% N( a: |  G
  V1 Varchar2(20);8 m# d' R6 B! Z5 ]/ @
  Begin
; g: g3 h" A' Z4 ~7 Q  For I In 1 .. 1000 Loop
, l: c9 f8 @+ F; Q  --Select名称Into V1 From诊疗项目目录Where类别= 'Z' And操作类型= '1';0 E( P% e2 ?  b  W3 _
  select名称Into V1 from诊疗项目目录where操作类型='1' and类别='Z';9 y" y/ k! v% h) z4 n6 g
  End Loop;
2 k9 J. |- d  d) C: k  End;
+ ^* F2 y. y5 I8 \2 k( b; E  /$ V5 n7 w' X% h& [0 V
  上面的SQL按两种方式分别执行了1000次查询,结果如下:
$ y9 U5 ^! B. r  @  操作类型= '1'在最右|类别='Z'在最右% m3 q, H  Y% w6 S& {
  0.093                          |    1.014/ i  @( q: j4 y
  1.06                            |    0.999
6 v! X5 X4 A& u2 w9 Z  0.998                          |    1.014
& a/ I2 p& I" b  V3 T  按理说,从右到左的顺序执行,“类别='Z'”在最右边时,先过滤得到170条记录,再从中找符合“操作类型 = '1'”的,比较而言,“操作类型 = '1'”在最右边时,先过滤得到3251条记录,再从中找符合“类别='Z'”,效率应该要低些,而实际结果却是两者所共的时间差不多。- w( V& j) P! Q/ `
  其实,从Oracle的数据访问原理来分析,两种顺序的写法,执行计划都是一样的,都是全表扫描,都要依次访问该表的所有数据块,对每一个数据块中的行,逐一检查是否同时符合两个条件。所以,就不存在先过滤出多少条数据的问题。$ M! @0 G8 B( m0 l& g8 s* ^" b
  综上所述,Where子句中条件的顺序对性能没有影响(不管是CBO还是RBO优化器模式),注意,额外说一下,这里只是说条件的顺序,不包含表的顺序。在RBO优化器模式下,表应按结果记录数从大到小的顺序从左到右来排列,因为表间连接时,最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少,效率越高。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|Woexam.Com ( 湘ICP备18023104号 )

GMT+8, 2024-3-29 08:27 , Processed in 0.466100 second(s), 25 queries .

Powered by Discuz! X3.4 Licensed

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表