Oracle中where子句中條件的物理執行順序

經常有人問到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;

【11g的話，有可能優化器不同，導致上面兩個sql語句都不會報錯，個人猜測是因為有一個條件為假，因此優化器忽略後面的錯誤】

證明了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，11g的話，有可能優化器不同，導致上面兩個sql語句都不會錯，個人猜測上述語句依然是從右至左執行，但是如果右邊出錯的話，則執行次右邊的條件進行篩選】

實驗三：證明了在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優化器模式下，表應按結果記錄數從大到小的順序從左到右來排列，因為表間連線時，最右邊的表會被放到巢狀迴圈的最外層。最外層的迴圈次數越少，效率越高