時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(2)
??? 經(jīng)常有人問到oracle中的Where子句的條件書寫順序是否對SQL性能有影響，我的直覺是沒有影響，因為如果這個順序有影響，Oracle應(yīng)該早就能夠做到自動優(yōu)化，但一直沒有關(guān)于這方面的確鑿證據(jù)。在網(wǎng)上查到的文章，一般認(rèn)為在RBO優(yōu)化器模式下無影響（10G開始，缺省為RBO優(yōu)化器模式），而在CBO優(yōu)化器模式下有影響，主要有兩種觀點：
??? a.能使結(jié)果最少的條件放在最右邊，SQL執(zhí)行是按從右到左進(jìn)行結(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í)行順序是從右到左的。
#p#副標(biāo)題#e#
??? 那么，根據(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.門診標(biāo)志<>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ù)越少，效率越高。
關(guān)鍵詞標(biāo)簽：SQL,oracle
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法