程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 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優化器模式下,表應按結果記錄數從大到小的順序從左到右來排列,因為表間連接時,最右邊的表會被放到嵌套循環的最外層。最外層的循環次數越少,效率越高。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved