經常有人問到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優化器模式下表應按結果記錄數從大到小的順序從左到右來排列因為表間連接時最右邊的表會被放到嵌套循環的最外層最外層的循環次數越少效率越高
From:http://tw.wingwit.com/Article/program/Oracle/201311/18112.html
