经常有人问到oracle中的Where子句的条件书写顺序是否对SQL性能有影响我的直觉是没有影响因为如果这个顺序有影响Oracle应该早就能够做到自动优化但一直没有关于这方面的确凿证据在网上查到的文章一般认为在RBO优化器模式下无影响(G开始缺省为RBO优化器模式)而在CBO优化器模式下有影响主要有两种观点
a能使结果最少的条件放在最右边SQL执行是按从右到左进行结果集的筛选的;
b有人试验表明能使结果最少的条件放在最左边SQL性能更高
查过oracle到G的在线文档关于SQL优化相关章节没有任何文档说过where子句中的条件对SQL性能有影响到底哪种观点是对的没有一种确切的结论只好自己来做实验证明结果表明SQL条件的执行是从右到左的但条件的顺序对SQL性能没有影响
实验一证明了SQL的语法分析是从右到左的
下面的试验在i和G都可以得到相同的结果 第条语句执行不会出错第条语句会提示除数不能为零
Select ok From Dual Where / = And = ;
Select ok From Dual Where = And / = ;
证明了SQL的语法分析是从右到左的
实验二证明了SQL条件的执行是从右到左的
drop table temp;
create table temp( t varchar()t varchar());
insert into temp values(zmabcde);
insert into temp values(sz);
insert into temp values(sz);
commit;
select * from temp where to_number(t)> and t=sz;
select * from temp where t=sz and to_number(t)>;
在i上执行 第条语句执行不会出错第条语句会提示无效的数字
在G上执行两条语句都不会出错
说明i上SQL条件的执行确实是从右到左的但是G做了什么调整呢?
实验三证明了在g上SQL条件的执行是从右到左的
Create Or Replace Function F(v_In Varchar) Return Varchar Is
Begin
Dbms_OutputPut_Line(exec F);
Return v_In;
End F;
/
Create Or Replace Function F(v_In Varchar) Return Varchar Is
Begin
Dbms_OutputPut_Line(exec F);
Return v_In;
End F;
/
SQL> set serverout on;
SQL> select from dual where f()= and f()=;
exec F
exec F
SQL> select from dual where f()= and f()=;
exec F
exec F
结果表明SQL条件的执行顺序是从右到左的
那么根据这个结果来分析把能使结果最少的条件放在最右边是否会减少其它条件执行时所用的记录数量从而提高性能呢?
例如下面的SQL条件是否应该调整SQL条件的顺序呢?
Where A结帐id Is Not Null
And A记录状态<>
And A记帐费用=
And (Nvl(A实收金额 )<>Nvl(A结帐金额 ) Or Nvl(A结帐金额 )=)
And A病人ID=[] And Instr([]||Nvl(A主页ID)||)>
And A登记时间Between [] And []
And A门诊标志<>
实际上从这条SQL语句的执行计划来分析Oracle首先会找出条件中使用索引或表间连接的条件以此来过滤数据集然后对这些结果数据块所涉及的记录逐一检查是否符合所有条件所以条件顺序对性能几乎没有影响
如果没有索引和表间连接的情况条件的顺序是否对性能有影响呢?再来看一个实验
实验四证明了条件的顺序对性能没有影响
SQL> select count(*) from诊疗项目目录where操作类型=;COUNT(*)
SQL> select count(*) from诊疗项目目录where类别=Z;COUNT(*)
SQL> select count(*) from诊疗项目目录where类别=Z and操作类型=;COUNT(*)
Declare
V Varchar();
Begin
For I In Loop
Select名称Into V From诊疗项目目录Where类别= Z And操作类型= ;
select名称Into V from诊疗项目目录where操作类型= and类别=Z;
End Loop;
End;
/
上面的SQL按两种方式分别执行了次查询结果如下
类型= 在最右 | 类别=Z在最右
|
|
|
按理说从右到左的顺序执行类别=Z在最右边时先过滤得到条记录再从中找符合操作类型 = 的比较而言操作类型 = 在最右边时先过滤得到条记录再从中找符合类别=Z效率应该要低些而实际结果却是两者所共的时间差不多
其实从Oracle的数据访问原理来分析两种顺序的写法执行计划都是一样的都是全表扫描都要依次访问该表的所有数据块对每一个数据块中的行逐一检查是否同时符合两个条件所以就不存在先过滤出多少条数据的问题
综上所述Where子句中条件的顺序对性能没有影响(不管是CBO还是RBO优化器模式)注意额外说一下这里只是说条件的顺序不包含表的顺序在RBO优化器模式下表应按结果记录数从大到小的顺序从左到右来排列因为表间连接时最右边的表会被放到嵌套循环的最外层最外层的循环次数越少效率越高
