程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> SQL條件的順序對數據庫性能的影響

SQL條件的順序對數據庫性能的影響

編輯：Oracle數據庫基礎

經常有人問到Oracle中的Where子句的條件書寫順序是否對SQL性能有影響，我的直覺是沒有影響，因為如果這個順序有影響，Oracle應該早就能夠做到自動優化，但一直沒有關於這方面的確鑿證據。在網上查到的文章，一般認為在RBO優化器模式下無影響（10G開始，缺省為RBO優化器模式），而在CBO優化器模式下有影響，主要有兩種觀點：

　　a.能使結果最少的條件放在最右邊，SQL執行是按從右到左進行結果集的篩選的；

　　b.有人試驗表明，能使結果最少的條件放在最左邊，SQL性能更高。

　　查過Oracle8到11G的在線文檔，關於SQL優化相關章節，沒有任何文檔說過where子句中的條件對SQL性能有影響，到底哪種觀點是對的，沒有一種確切的結論，只好自己來做實驗證明。結果表明，SQL條件的執行是從右到左的，但條件的順序對SQL性能沒有影響。

　　實驗一：證明了SQL的語法分析是從右到左的

　　下面的試驗在9i和10G都可以得到相同的結果：第1條語句執行不會出錯，第2條語句會提示除數不能為零。

　　1.Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2;

　　2.Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1;

　　證明了SQL的語法分析是從右到左的。

　　實驗二：證明了SQL條件的執行是從右到左的

drop table temp;
create table temp( t1 varchar2(10),t2 varchar2(10));
insert into temp values('zm','abcde');
insert into temp values('sz','1');
insert into temp values('sz','2');
commit;

1. select * from temp where to_number(t2)>1 and t1='sz';
2. select * from temp where t1='sz' and to_number(t2)>1;

　　在9i上執行, 第1條語句執行不會出錯，第2條語句會提示“無效的數字”

　　在10G上執行，兩條語句都不會出錯。

　　說明：9i上，SQL條件的執行確實是從右到左的，但是10G做了什麼調整呢？

　　實驗三：證明了在10g上SQL條件的執行是從右到左的

Create Or Replace Function F1(v_In Varchar2) Return Varchar2 Is
Begin
Dbms_Output.Put_Line('exec F1');
Return v_In;
End F1;
/
Create Or Replace Function F2(v_In Varchar2) Return Varchar2 Is
Begin
Dbms_Output.Put_Line('exec F2');
Return v_In;
End F2;
/
SQL> set serverout on;
SQL> select 1 from dual where f1('1')='1' and f2('1')='1';
1
----------
1
exec F2
exec F1
SQL> select 1 from dual where f2('1')='1' and f1('1')='1';
1
----------
1
exec F1
exec F2

　　結果表明，SQL條件的執行順序是從右到左的。

　　那麼，根據這個結果來分析，把能使結果最少的條件放在最右邊，是否會減少其它條件執行時所用的記錄數量，從而提高性能呢？

　　例如：下面的SQL條件,是否應該調整SQL條件的順序呢？

Where A.結帳id Is Not Null
And A.記錄狀態<>0
And A.記帳費用=1
And (Nvl(A.實收金額, 0)<>Nvl(A.結帳金額, 0) Or Nvl(A.結帳金額, 0)=0)
And A.病人ID=[1] And Instr([2],','||Nvl(A.主頁ID,0)||',')>0
And A.登記時間Between [3] And [4]
And A.門診標志<>1

　　實際上，從這條SQL語句的執行計劃來分析，Oracle首先會找出條件中使用索引或表間連接的條件，以此來過濾數據集，然後對這些結果數據塊所涉及的記錄逐一檢查是否符合所有條件，所以條件順序對性能幾乎沒有影響。
如果沒有索引和表間連接的情況，條件的順序是否對性能有影響呢？再來看一個實驗。

　　實驗四：證明了條件的順序對性能沒有影響。

SQL> select count(*) from診療項目目錄where操作類型='1';
COUNT(*)
----------
3251
SQL> select count(*) from診療項目目錄where類別='Z';
COUNT(*)
----------
170
SQL> select count(*) from診療項目目錄where類別='Z' and操作類型='1';
COUNT(*)
----------
1
Declare
V1 Varchar2(20);
Begin
For I In 1 .. 1000 Loop
--Select名稱Into V1 From診療項目目錄Where類別= 'Z' And操作類型= '1';
select名稱Into V1 from診療項目目錄where操作類型='1' and類別='Z';
End Loop;
End;
/

　　上面的SQL按兩種方式分別執行了1000次查詢，結果如下：

　　操作類型= '1'在最右|類別='Z'在最右

　　0.093 | 1.014

　　1.06 | 0.999

　　0.998 | 1.014

　　按理說，從右到左的順序執行，“類別='Z'”在最右邊時，先過濾得到170條記錄，再從中找符合“操作類型 = '1'”的，比較而言，“操作類型 = '1'”在最右邊時，先過濾得到3251條記錄，再從中找符合“類別='Z'”，效率應該要低些，而實際結果卻是兩者所共的時間差不多。

　　其實，從Oracle的數據訪問原理來分析，兩種順序的寫法，執行計劃都是一樣的，都是全表掃描，都要依次訪問該表的所有數據塊，對每一個數據塊中的行，逐一檢查是否同時符合兩個條件。所以，就不存在先過濾出多少條數據的問題。

　　綜上所述，Where子句中條件的順序對性能沒有影響（不管是CBO還是RBO優化器模式），注意，額外說一下，這裡只是說條件的順序，不包含表的順序。在RBO優化器模式下，表應按結果記錄數從大到小的順序從左到右來排列，因為表間連接時，最右邊的表會被放到嵌套循環的最外層。最外層的循環次數越少，效率越高。