oracle中Where子句的條件順序?qū)π阅艿挠绊?/H2>經(jīng)常有人問到oracle中的Where子句的條件書寫順序是否對SQL性能有影響，我的直覺是沒有影響，因為如果這個順序有影響，Oracle應(yīng)該早就能夠做到自動優(yōu)化，但一直沒有關(guān)于這方面的確鑿證據(jù)。在網(wǎng)上查到的文章，一般認為在RBO優(yōu)化器模式下無影響(10G開始，缺省為CBO優(yōu)化器模式)，而在CBO優(yōu)化器模式下有影響，主要有兩種觀點： a.能使結(jié)果最少的條件放在最右邊，SQL執(zhí)行是按從右到左進行結(jié)果集的篩選的; b.有人試驗表明，能使結(jié)果最少的條件放在最左邊，SQL性能更高。 查過oracle8到11G的在線文檔，關(guān)于SQL優(yōu)化相關(guān)章節(jié)，沒有任何文檔說過where子句中的條件對SQL性能有影響，到底哪種觀點是對的，沒有一種確切的結(jié)論，只好自己來做實驗證明。結(jié)果表明，SQL條件的執(zhí)行是從右到左的，但條件的順序?qū)QL性能沒有影響。 實驗一：證明了SQL的語法分析是從右到左的 下面的試驗在9i和10G都可以得到相同的結(jié)果: 第1條語句執(zhí)行不會出錯，第2條語句會提示除數(shù)不能為零。 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條件的執(zhí)行是從右到左的 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上執(zhí)行, 第1條語句執(zhí)行不會出錯，第2條語句會提示“無效的數(shù)字” 在10G上執(zhí)行，兩條語句都不會出錯。 說明：9i上，SQL條件的執(zhí)行確實是從右到左的，但是10G做了什么調(diào)整呢? 實驗三：證明了在10g上SQL條件的執(zhí)行是從右到左的 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 結(jié)果表明，SQL條件的執(zhí)行順序是從右到左的。 那么，根據(jù)這個結(jié)果來分析，把能使結(jié)果最少的條件放在最右邊，是否會減少其它條件執(zhí)行時所用的記錄數(shù)量，從而提高性能呢? 例如：下面的SQL條件,是否應(yīng)該調(diào)整SQL條件的順序呢? Where A.結(jié)帳id Is Not Null And A.記錄狀態(tài)<>0 And A.記帳費用=1 And (Nvl(A.實收金額, 0)<>Nvl(A.結(jié)帳金額, 0) Or Nvl(A.結(jié)帳金額, 0)=0) And A.病人ID=[1] And Instr([2],','||Nvl(A.主頁ID,0)||',')>0 And A.登記時間Between [3] And [4] And A.門診標志<>1 實際上，從這條SQL語句的執(zhí)行計劃來分析，Oracle首先會找出條件中使用索引或表間連接的條件，以此來過濾數(shù)據(jù)集，然后對這些結(jié)果數(shù)據(jù)塊所涉及的記錄逐一檢查是否符合所有條件，所以條件順序?qū)π阅軒缀鯖]有影響。 如果沒有索引和表間連接的情況，條件的順序是否對性能有影響呢?再來看一個實驗。 實驗四：證明了條件的順序?qū)π阅軟]有影響。 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按兩種方式分別執(zhí)行了1000次查詢，結(jié)果如下： 操作類型= '1'在最右|類別='Z'在最右 0.093 | 1.014 1.06 | 0.999 0.998 | 1.014 按理說，從右到左的順序執(zhí)行，“類別='Z'”在最右邊時，先過濾得到170條記錄，再從中找符合“操作類型 = '1'”的，比較而言，“操作類型 = '1'”在最右邊時，先過濾得到3251條記錄，再從中找符合“類別='Z'”，效率應(yīng)該要低些，而實際結(jié)果卻是兩者所共的時間差不多。 其實，從Oracle的數(shù)據(jù)訪問原理來分析，兩種順序的寫法，執(zhí)行計劃都是一樣的，都是全表掃描，都要依次訪問該表的所有數(shù)據(jù)塊，對每一個數(shù)據(jù)塊中的行，逐一檢查是否同時符合兩個條件。所以，就不存在先過濾出多少條數(shù)據(jù)的問題。 綜上所述，Where子句中條件的順序?qū)π阅軟]有影響(不管是CBO還是RBO優(yōu)化器模式)，注意，額外說一下，這里只是說條件的順序，不包含表的順序。在RBO優(yōu)化器模式下，表應(yīng)按結(jié)果記錄數(shù)從大到小的順序從左到右來排列，因為表間連接時，最右邊的表會被放到嵌套循環(huán)的最外層。最外層的循環(huán)次數(shù)越少，效率越高 |
|
|
