中國聯(lián)通北六省融合項目應(yīng)用性能與安全開發(fā)建議v1.1

1、中國聯(lián)通北六省融合項目應(yīng)用性能與安全開發(fā)建議(vi. 1)基礎(chǔ)設(shè)施組2011-5-24目錄1 oracle sql性能優(yōu)化31.1. 通過應(yīng)用綁定變量提升業(yè)務(wù)性能，降低數(shù)據(jù)庫消耗（嚴(yán)格要求）41.2. 共享sql程序提升應(yīng)用性能（建議）錯誤!未定義書簽。1.3. 釆用最有效率的表名順序加快業(yè)務(wù)查詢速度（建議）71.4. 通過條件子句中的連接順序提升性能（強(qiáng)烈建議）91.5. 阻止查詢使用'* '提升性能（嚴(yán)格要求）91.6. 減少訪問數(shù)據(jù)庫的次數(shù)（強(qiáng)烈建議）101.7. 使用decode函數(shù)來減少處理時間（建議）111. &整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問（建議）121.9

2、.刪除重復(fù)記錄（建議）131. 10.用t runcate替代d elete全表記錄（建議）131. 11.降低數(shù)據(jù)庫競爭優(yōu)化（建議）131. 1 2.用where子句替換having子句（建議）141. 13.減少對表的查詢（建議）141. 14.通過內(nèi)部函數(shù)提高sql效率（建議）161. 15 使用表的別名（al ias）（嚴(yán)格執(zhí)行）錯誤!未定義書簽。1. 16.用exists替代in（強(qiáng)烈建議）錯誤!未定義書簽。1. 17.用 not ex 1sts 替代 not in （嚴(yán)格要求）181.18 .用表連接替換exi sts （建議）191. 19.用 exists 替換 d istin

3、ct（建議）201. 20.識別'低效執(zhí)行的sql語句（自查方法）201.21. 使用t kprof i具來查詢sql性能狀態(tài)（口查方法）211.22. 用explain plan分析sql語句（自查方法）221.23. 用索引提高效率（強(qiáng)烈建議）錯誤!未定義書簽。1.24. 索引掃描的類型（自查方法）241.25. 基礎(chǔ)表的選擇（自查方法）261.26. 多個平等的索引（口查方法）271.27. 等式比較和范圍比較（自查方法）281.28. 不明確的索引等級（白查方法）281.29. 強(qiáng)制索引失效（自查方法）301.30. 避免在索引列上使用計算（嚴(yán)格要求）311.31. 自動選擇索

4、引（自查方法）311. 32.避免在索引列上使用not （嚴(yán)格要求）321.33. 用大于等于替代大于方法提升速度（建議）331.34. 用聚合方式替換或運(yùn)算提升速度（建議）341.35. 用in來替換0r （建議）381.36. 避免在索引列上使用is null和is not null （嚴(yán)格要求）錯誤!未定義書簽。1.37. 總是使用索引的第一個列（嚴(yán)格要求）錯誤!未定義書簽。1. 38.通過聯(lián)合聚合函數(shù)優(yōu)化方法（建議）401.3 9.通過用條件篩選優(yōu)化方法（建議）421.40. 避免改變索引列的類型（嚴(yán)格要求）431.41. 程序操作問題導(dǎo)致無法使用索引（嚴(yán)格要求）441 .42.cbo

5、下使用更具選擇性的索引（自查方法）461.43. 避免使用耗費(fèi)資源的操作（建議）471.44. 通過優(yōu)化group by提升應(yīng)用速度（強(qiáng)烈建議）471.4 5.變量賦值禁止使用dual表（嚴(yán)格要求）481.46. 大批量數(shù)據(jù)處理，嚴(yán)格控制事物大小（嚴(yán)格要求）481.47. 事務(wù)按照同一順序訪問數(shù)據(jù)對象，避免死鎖發(fā)生（嚴(yán)格要求）491.48. 關(guān)閉遠(yuǎn)程數(shù)據(jù)庫連接，防止遠(yuǎn)端數(shù)據(jù)庫會話資源耗盡（建議）501.49. 數(shù)據(jù)導(dǎo)入導(dǎo)出優(yōu)化方法（建議）501.50. 數(shù)據(jù)表和索引存儲表空間規(guī)定（嚴(yán)格要求）502 系統(tǒng)安全規(guī)范錯誤!未定義書簽。2.1. 數(shù)據(jù)庫賬戶口令規(guī)定512.2. 參數(shù)口令加密規(guī)定51o

6、racle sql性能優(yōu)化建議等級分類說明:1. 通過應(yīng)用綁定變量提升業(yè)務(wù)性能，降低數(shù)據(jù)庫消耗(嚴(yán)格要求)應(yīng)使用變量綁定實現(xiàn)sql語句共亨，避免使用硬解析a.不允許直接拼寫sql語句，而要使用綁定變量。例如：例此種寫法不允許：in it (d yn stmt);sprintf (dynstmt, ''insert intowchg%s(id_no, total_date, login_accept, sm_code, bolon g_code, pho no_no, org_codo, login _no, op_codo, op_time, mac hi njcodo, ca

7、sh_pay, choc k pay, s i m fee, machine fee, in net fee, choice fee, other fee, hand fee, deposit, back f lag, encrypt_fee, system_note, op_note) values (%ld, to_number (%s), %ld,' %s',' %s',' %s', '%s',' %s',' %s', to_date (' %s',' yyyynund

8、d hh24: mi: ss'),' zz', 0, 0, 0, 0, 0, 0, 0, 0, 0,' o', 0, '%s，,' %s，)", yearmonth, idno, totaldate, loginaccept, smcode, belongcode, phoneno, orgco de, loginno, opcodo, optime, systemnote, systemnote);exec sql prepare ins stmt from:dynstmt;execsql execute ins_stmt ;

9、例2：可以使用：init (dynstmt);sprintf (dynstmt, " insert into wlogin()pr%s/，(z，total_date, login_accept, op_code, pay_type, pay_money,"sm_code, id_no, phone_no, org_code, loin_no, op_time, op_note, ip_sddr)"values"(t0_number(:vl), :v2, :v3, :v4, :v5,：v6, : v7, : v& : v9, : vlo, to_d

10、ate(: vll, ' yyyymmdd hh24:mi:ss"), :vl2, :vl3yearmonth);exec sql prepare prepare1 from:dynstmt;exec sqlexecute prepare1 using :totaldate, rloginaccept, :opcode, :p aytypc, :handfee,:smcode, :idno, :phoneno, :orgcode, :loginno, :optime, :opnote,:ipaddress;例3：可以使用：exec sql insert into dcustm

11、sgadd(id no, busi type, user_ type, field code, field order, field value, other_value)valles:idno, :busitype,:usertype,:fieldcode, :fieldorder, :fieldvalue,:othervalue）;b.執(zhí)行相同操作的sql語句必須使用相同名字的綁定變量。例如：第一組的兩個sql語句，綁定變量是相同的，而第二組屮的兩個語句綁定變量 不同，即使賦于不同的綁定變量相同的值也不能使這兩個sql語句相同，達(dá)不到共享sql 語句目的。例如：s elect pin ,

12、name fro m people w here pin =:blkl. pin ; select pin , name from peop le where p in =:blk2 . pin;2. 共享sq l程序提升應(yīng)用性能（建議）為了不重復(fù)解析相同的sql語句,在第一次解析之后,oracle將sql語句存放在內(nèi)存中. 這塊位于系統(tǒng)全局區(qū)域s ga （system global are a）的共享池（sha red buffer pool）中的內(nèi) 存可以被所有的數(shù)據(jù)庫用戶共享.因此，當(dāng)你執(zhí)行一個sql語句（有時被稱為一個游標(biāo)）時， 如果它和之前的執(zhí)行過的語句完全相同，oracle就能很快

13、獲得已經(jīng)被解析的語句以及最好的 執(zhí)行路徑.oracle的這個功能大大地提高了 sql的執(zhí)行性能并節(jié)省了內(nèi)存的使用.可惜的是oracle只對簡單的表提供高速緩沖（cache b uffering）,這個功能并不適用 于多表連接查詢。數(shù)據(jù)庫管理員必須在in it .ora屮為這個區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個內(nèi)存區(qū)域越大， 就可以保留更多的語句，當(dāng)然被共亨的可能性也就越大了.當(dāng)你向or acle提交一個s ql語句,oracl e會首先在這塊內(nèi)存中查找相同的語句. 這里需要注明的是,or acle對兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,sql語句必須 完全相同（包括空格，換行等）.共亨的語句必須滿足以

14、下條件：1. 字符級的比較：當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同 例如：selec t * from e mp;和下列每一個都不同select * from em p;sei ect * from emp ;select *from emp;1. 兩個sql語句屮必須使用相同的名字的綁定變量（bind va riables）例如：第一組的兩個s ql語句是相同的（可以共享），而第二組中的兩個語句是不同的 （即使在運(yùn)行時，賦于不同的綁定變量相同的值）（大小寫的問題？）select pi n , name f rom people where pin = :blkl. o t_ind; s

15、e lect pin , name from people wh ere pin =:blkl. ov i nd;2. 采用最有效率的表名順序加快業(yè)務(wù)查詢速度（建議）注意：在基于規(guī)則的優(yōu)化器中有效oracle的解析器按照從右到左的順序處理from子句中的表名.因此from子句中寫在最后的表（基礎(chǔ)表drivi ng table）將被最先處理.在fr 0m子 句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)oracl e處理多個表時，會運(yùn)用排序及合并的方式連接它們.首先，掃描第一個表 （from子句中最后的那個表）并對記錄進(jìn)行派序，然后掃描第二個表（fro m子句中最后第二 個表）

16、，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并.例如：表tab1 16, 38 4條記錄表ta b2 1條記錄1. 選擇tab2作為基礎(chǔ)表（最好的方法）s elect coun t （*） from tabl, tab2 執(zhí)行時間 0.96 秒2. 選擇tab2作為基礎(chǔ)表（不佳的方法）sel ect cou nt （*） from tab2, tabl 執(zhí)行時間 26. 09 秒3. 如果有3個以上的表連接查詢，那就需要選擇交叉表（int ersection table）作為基 礎(chǔ)表，交叉表是指那個被其他表所引用的表.（描述驗證）例如：emp表描述了 lo cation表和c

17、a tegory表的交集.select *from loca t10n l ,c ategory c,emp ewhe re e. emp_n 0 between 1000 and 2 000and e. cat_n0 = c. cat_n0an d e. locn =l. locn將比下列sql更有效率select *from emp e ,locationl ,categ ory cwher e e. cat_no = c. cat_n 0and e. lo cn = l. loc nand e. em p no betwe en 1000 an d 20001. 通過條件子句中的連接順序

18、提升性能（強(qiáng)烈建議）0 racle采用自下而上的順序解析wher e子句,根據(jù)這個原理，表之間的連接必須寫在 其他where條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在where子句的末尾.例如：4. （低效，執(zhí)行時間156.3秒）selec t from emp ewhere sal > 500 00and job二'manager'and 25 count (*) f rom emp wh ere mgr=e. empno);5. (高效，執(zhí)行時間10. 6秒)select from empewhere 2t count(*)from emp where mgr

19、=e. empno)a nd sal > 5 0000and job = 'manager'1. 阻止查詢使用'* '提升性能(嚴(yán)格要求)sele ct子句中嚴(yán)禁使用'* '當(dāng)你想在sel ect子句中列出所有的column吋，使用動態(tài)sql列引用'*，是一個方便 的方法.不幸的是,這是一個非常低效的方法.實際上oracle在解析的過程中，會將匕' 依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時間。2. 減少訪問數(shù)據(jù)庫的次數(shù)(強(qiáng)烈建議)當(dāng)執(zhí)行每條sql語句時，oracle在內(nèi)部執(zhí)行了許多工作:

20、解析sql語句，估算索引的 利用率，綁定變量，讀數(shù)據(jù)塊等等.由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實際上減少 oracle的工作量.例如，以下有三種方法可以檢索出雇員號等于0342或029 1的職員.6. 方法1 (最低效)sele ct emp nam e , salary , gradefrom empwh ere emp_no = 342;se lect emp_n ame , sala ry , gradefrom empwhere emp_no = 291;7. 方法2 (次低效)declarecu rsor cl (e no number) isselec t emp_name ,

21、salary, gr adefrom empwhere emp mo 二 e mo;beginopen cl(34 2);fetch cl into;open cl (291);fetch cl into .close cl;end;8. 方法3 (高效)兩條合成一條！select a . emp.name , a. salary , a. grade , b. emp_na me , b. sal ary , b. gr adefrom e mp a, emp bwhere a. e mp_no = 34 2orb. emp no = 291;注意：在sql*plus , sql *fori

22、ns和pro *c中重新設(shè)置arr ays i ze參數(shù)，可以增加每次數(shù)據(jù) 庫訪問的檢索數(shù)據(jù)量，建議值為2001. 使用de code函數(shù)來減少處理時間(建議)使用d ecode函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.例如：select count (*), s um (sal)fr 0m empwhe re dept_no = 0020an d ename li ke“smith%'select count (*),fr 0m empsum(sal)whe re dept_mo= 0030an d ename li ke“smith%'你可以用dec ode函數(shù)高效地

23、得到相同結(jié)果selec t count (de code (dept_no, 0020,' x ', null) d 0020_count ,count (de code (dept_no, 0030,' x ', null) d 0030_c0unt ,sum (deco de (dept _n0,0020, sal, null) doo 20 sal,su m (decode (d ept_no, 003 0, sal, null ) d0030_s alfrom empwhere en ame like 'smith%'類似的,decode

24、函數(shù)也可以運(yùn)用于group by和ord er by子句中2. 整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問(建議)如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間 沒有關(guān)系)(兩個方面，建議)例如：select namefrom empwhere e mp no = 12 34;select namefrom dptwhere dpt_no =10 ;selec t namefrom catwher e cat_type = 'rd'上面的3個查詢可以被合并成一個：sele ct e. name , d. name , c. namefr om cat c , d

25、pt d , e mp e, dual xwhere nv l ( 'x',x. dummy)二 nvl ( 'x', e. row id(+)and nvl( 'x',x. dummy) =nvl( 'x', d. rowid(+)and nvl( 'x ', x. dummy)二 nvl ( 'x', c. row1d (+)and e. e mp.no (+) =1234and d. dept _no (+) = 10and c. cat_ty pe (+) = 'rd'3.

26、刪除重復(fù)記錄(建議)最高效的刪除重復(fù)記錄方法(因為使用了 rowid)delete from emp ewh ere e. row i d > (selec t min(x. ro wid)from emp xwiier e x. emp_no = e. emp_n 0);4. 用trunca te替代delete全表記錄(建議)當(dāng)刪除表中的所有記錄時，在通常情況下，回滾段(rollback segments )用來存放可 以被恢復(fù)的信息.如果你沒有commit事務(wù),oracle會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確 地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)而當(dāng)運(yùn)用tru ncate時，回滾段不

27、再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后，數(shù)據(jù)不 能被恢復(fù).因此很少的資源被調(diào)用，執(zhí)行吋間也會很短.5. 降低數(shù)據(jù)庫競爭優(yōu)化(建議)盡量多使用commit只要有可能，在程序屮盡量多使用commit,這樣程序的性能得到提髙，需求也會因為 comm it所釋放的資源而減少.commit所釋放的資源：1. 回滾段上用于恢復(fù)數(shù)據(jù)的信息。2. 被程序語句獲得的鎖3. redo log b uffer 中的空間4. oracle為管理上述3種資源中的內(nèi)部花費(fèi)1. 用where子句替換having子句(建議)避免使用ha ving子句，ha ving只會在檢索出所有記錄z后才對結(jié)果集進(jìn)行過濾. 這個處理需要排

28、序,總計等操作.如果能通過w here子句限制記錄的數(shù)目，那就能減少這方 面的開銷.例如：5. 低效：select region, av g (log size)from log at i ongrou p by regio nsydneyhavtng region !=''pertiiand regi on !=''6. 高效select r egion, avg(log size)from locat ionsydneywhere region !二''perthand region !="group by region1. 減少對

29、表的查詢(建議)在含有子查詢的sql語句中，要特別注意減少對表的查詢 例如：7. 低效select tab _namefrom tableswh ere tab_na me = ( sel ect tab_na mefrom ta b_columnswhere vers ion 二 604)and db_ve r二(selec t db_verf rom tab co lumnswhere version = 604)8. 高效select tab _namefrom tableswhere (tab_n ame, db_ver )=(sele ct tab_nam e, db_ver)fro

30、m tab columnswhe re version = 604)up date 多個 col umn 例子：9. 低效：update e mpset emp _cat = (se lect max (c ategory) from emp_ca tegories),sal range = (select max (sal r ange) from emp categ ories)whe re emp dept = 0020;10. 高效：update empset (emp_cat, s al_range)=(select max (catego ry) , max (sal_raxge)

31、from emp categories )where em p_dept 二 0 020;1. 通過內(nèi)部函數(shù)提高sql效率(建議)select h. empno, e. en ame, ii. hist _type, t. ty pe_desc, co unt(*)from history_type t, emp e, empjiis tory iiwhe re h. empno = e. empnoand h. hist_type = t . hist_typegroup by h. empno, e. ename, h. hi st type, t. type desc;通過調(diào)用下面的函數(shù)

32、可以提高效率.fu nct10n lookup hist type(typ in number) return varc har2astd esc varcha r2 (30);cu rsor cl isselect ty pe_descfr om history _typewher e hist_type = typ;beginopen cl;fetch cl into td esc;close cl;retur n (nvl (tde sc,' ?');end;funct ion lookup _emp (emp i n number) return var char2as

33、e name varcii ar2 (30);cursor cl isselect enamefrom empwhere empno二emp ;beginop en cl;fet ch cl into ename;cl ose cl;re turn (nvl (ename,'?');end;se lect h. emp no, lookup_emp (ii. empn 0),h. hist _type, look up_hist_ty pe (il i1ist_type), coun t (*)from emp_h1stor y hgroup by h. empno , h.

34、hist_type;2. 使用表的別名(alias)(嚴(yán)格執(zhí)行)當(dāng)在sql語句中連接多個表時，請使用表的別名并把別名前綴于每個colu mn上.這樣 一來，就可以減少解析的時間并減少那些由colum n歧義引起的語法錯誤.(譯者注:co lumn歧義指的是由于sql中不同的表具有相同的co i umn名，當(dāng)sql語句 中出現(xiàn)這個column 口寸,sql解析器無法判斷這個column的歸屬)3. 用exists替代in(強(qiáng)烈建議)在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接.在這 種情況下，使用exis ts (或not ex ists)通常將提高查詢的效率.11.

35、低效：select *from emp (基礎(chǔ)表)where empno > 0and deptn 0 in (sele ct deptnofrom deptwhere loc = 'melb')12. 髙效：select *from emp (基礎(chǔ)表)wh ere empno > 0and ex ists (sele ct 'x'from deptwhe re dept. de ptno = emp . deptnoand loc= felb')(譯者按：相對來說，用no t exists替換not in將更顯著地提高效率，下一節(jié)中將指 出

36、)1. 用n 0t ex i sts替代not in (嚴(yán)格要求)在子查詢中,not in子句將執(zhí)行一個內(nèi)部的排序和合并.無論在哪種情況下,no t in 都是最低效的(因為它對子查詢中的表執(zhí)行了一個全表遍歷).為了避免使用not in，我們 可以把它改寫成外連接(outer joi ns)或not ex ists.例如：select from empwh ere dept n onot in （select dep t nofrom deptwhere dept cat二'a'）;為了提高效率.改寫為：13. （方法一：高效）se lect .fr om emp a, d e

37、pt bwiier e a. deptjj 0 = b. dept （+）and b. dept_no is nulland b. dept cat （+） ='a'14. （方法二：最高效）select from empewhere not exists （select 'x'from dept dwhere d . dept _n0 =e. dept noand dept_cat ='a'）;1. 用表連接替換exi sts （建議）通常來說，采用表連接的方式比exists更有效率select enamefrom emp ewh ere ex

38、ists （select 'x'from de ptwhere d ept_no = e . dept_noa nd dept ca t ='a'）;（更高效）sele ct enamef rom dept d , emp ewhe re e. dept _n0 = d. dep t noand d ept_cat 二'a'（譯者按：在rbo的情況下，前者的執(zhí)行路徑包括filter,后者使用nested lo 0p）2. 用 exists 替換 distinct （建議）當(dāng)提交一個包含一對多表信息（比如部門表和雇員表）的查詢時,避免在sele c

39、t子句中 使用dis tinct. 一般可以考慮用exist替換例如：15. 低效：s elect dist inct dept no, dept na mefrom de pt d, empewhere d. d ept no = e. dept no16. 高效：select deptjjo, de pt_namefr om dept dwhere ex is ts （ selec t 'x'from emp ewhe re e. dept _no = d. dep t no）;exi sts使查詢更為迅速，因為rdbms核心模塊將在子查詢的條件一旦滿足后,立刻返 回結(jié)果.

40、1. 識別'低效執(zhí)行'的sql語句（自查方法）用下列sql工具找出低效sql:select ex ecuttons , disk_read s, buffer_gets,roun d（buffer_gets-disk_reads）/buf fer_gets, 2 ） ilit_radi o,round （d 1sk reads/execut1ons , 2） reads per run,s qi-textfr om v$sqlar eawhere executions。and buff er_gets >0and （buffer gets-d isk reads）/buf

41、fer ge0 rder by 4 desc;（譯者按：雖然目前各種關(guān)于sql優(yōu)化的圖形化工具層出不窮，但是寫出自己的sql工 具來解決問題始終是一個最好的方法）2. 使用tkpr 0e工具來查詢sq l性能狀態(tài)（自查方法）sql trace工具收集正在執(zhí)行的sql的性能狀態(tài)數(shù)據(jù)并記錄到一個跟蹤文件中.這個 跟蹤文件提供了許多有用的信息,例如解析次數(shù)執(zhí)行次數(shù),cpu使用吋間等.這些數(shù)據(jù)將可 以用來優(yōu)化你的系統(tǒng).設(shè)置sql trace在會話級別：有效a lter sessi on set sql _trace true設(shè)置sql tr ace在整個數(shù)據(jù)庫有效仿，你必須將s ql_trace參數(shù)在

42、init. ora中設(shè)為 true, us er_dump_de st參數(shù)說明了生成跟蹤文件的目錄（譯者按:這一節(jié)中，作者并沒有提到tkpro f的用法,對sql trace的用法也不夠準(zhǔn) 確，設(shè)置sq l trace首先要在init. ora中設(shè)定timed_st atistics,這樣才能得到那些重 要的時間狀態(tài).生成的trace文件是不可讀的，所以要用tkp rof工具對其進(jìn)行轉(zhuǎn)換,tkprof 有許多執(zhí)行參數(shù).大家可以參考o(jì)racle手冊來了解具體的配置.）3. 用explain plan分析sq l語句（自查方法）e xplain pla n是一個很好的分析sql語句的工具,它甚至可

43、以在不執(zhí)行sq l的情況 下分析語句.通過分析，我們就可以知道oracle是怎么樣連接表,使用什么方式掃描表（索引 掃描或全表掃描）以及使用到的索引名稱.你需要按照從里到外，從上到下的次序解讀分析的結(jié)果.expla in plan分析的結(jié)果是 用縮進(jìn)的格式排列的，最內(nèi)部的操作將被最先解讀，如杲兩個操作處于同一層中，帶有最小 操作號的將被首先執(zhí)行.nes ted loop是少數(shù)不按照上述規(guī)則處理的操作，止確的執(zhí)行路徑是檢查對nest ed loop提供數(shù)據(jù)的操作,其中操作號最小的將被最先處理.譯者按：通過實踐，感到還是用sql plus中的set trace功能比較方便.舉例：sq l>

44、list1 select *2 from dept , emp3* w here emp. d eptno = de pt. dept nosql> set a utotrace t raceonly atraceonly 可以不顯示執(zhí)行結(jié)果*/sql> /14 rows se lectedex ecution pl an0 select statement optimizer 二choose1 0 nested l oops2 1 t able acces s (full) 0 f ' emp，3 1 table ac cess (by i ndex row id ) o

45、f ' dept '4 3 inde x (unique scan) of ' pk_dept' (unique)st atistics0 recursi ve cal is2 db block get s30 consi stent gets0 physica 1 reads0 redo size2598 bytes sent via sql*net to cl ient50 3 bytes re ceived via sql*net f rom client2 sqlnet roundtrip s to/from client0 s orts (memo r

46、y)0 sort s (disk)1 4 rows pro cessed通過以上分析，可以得出實際的執(zhí)行步驟是：1. table acc ess (full) of ' emp'2. index (unique sca n) of ' pk dept' (uni que)3. ta ble access (by index rowtd) of'dept4. nested lo ops (joint ng 1 and 3 )注：目前許多第三方的工具如toad和oracle本身提供的工具如oms的s ql analyze 都提供了極其方便的e xplain

47、pla n工具.也許喜歡圖形化界面的朋友們可以選用它們.4. 用索引提高效率（強(qiáng)烈建議）索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率.實際上,ora cle使用了一個復(fù) 雜的白平衡b-tree結(jié)構(gòu).通常，通過索引查詢數(shù)據(jù)比全表掃描要快.當(dāng)0race e找出執(zhí)行 查詢和up date語句的最佳路徑時，oracle優(yōu)化器將使用索引.同樣在聯(lián)結(jié)多個表吋使用索 引也可以提高效率.另一個使用索引的好處是，它提供了主鍵（primary k ey）的唯一性驗 證.除了那些long或low raw數(shù)據(jù)類型，你可以索引兒乎所有的列.通常,在大型表中使 用索引特別有效.當(dāng)然,你也會發(fā)現(xiàn)，在掃描小表時，使用索引同

48、樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.索引需耍空 間來存儲，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時，索引木身也會被修 改.這意味著每條記錄的insert , del ete , upda te將為此多付出4, 5次的磁盤i/o . 因為索引需要額外的存儲空i'可和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.注：定期的重構(gòu)索引是有必要的.alter indeme> rebuil acename>5. 索引掃描的類型（自查方法）oracle對索引有兩種訪問模式.17. 索引唯一掃描（index unique scan）大

49、多數(shù)情況下，優(yōu)化器通過where子句訪問index.例如：表lodging有兩個索引:建立在l odging列上的唯一-性索引lodgin g_pk和建立在 ma nager列上的非唯一性索引lodgin g$manager.select *from lodgi ngwhere l odging = 'rose hill，;在內(nèi)部，上述sql將被分成兩步執(zhí)行，首先，lo dging_pk索引將通過索引唯一掃 描的方式被訪問，獲得相對應(yīng)的row1d ,通過row1d訪問表的方式執(zhí)行下一步檢索.如果被檢索返回的列包括在ind ex列中,oracl e將不執(zhí)行第二步的處理（通過rowid 訪

50、問表）.因為檢索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果.下面sql只需要inde x unique scan操作.se lect lodgi ngfrom lo doingwher e lodging = 'rose iii ll'18. 索引范圍查詢(index ra nge scan)適用于兩種情況：1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索例1:select lo dgingfrom lodgingwhere lodgi ng like 'm%'where子句條件包括一系列值,oracle將通過索引范圍查詢的方式查詢lodging.

51、p k . 由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描例2:se lect lodgi ngfrom lo dgingwher e manager = 'bill gates'這個sq l的執(zhí)行分兩步，l odging$man ager的索引范圍查詢(得到所有符合條件記錄 的row1d)和下一步同過row1 d訪問表得到lodg ing列的值.由于lodging$ma nager是一 個非唯一性的索引，數(shù)據(jù)庫不能對它執(zhí)行索引唯一掃描.由于sql返回lodgtng列,而它并不存在于lodg tng$manage r索引中，所以在索引范 圍查詢后會執(zhí)行一個通過row

52、id訪問表的操作.wher e子句中，如果索引列所對應(yīng)的值的第一個字符由通配符(wil dcard)開始，索引 將不被采用.select lodg ingfrom lodgingwhe re manager like ' % ha nman，;在這種情況下，oracle將使用全表掃描.1. 基礎(chǔ)表的選擇(口查方法)基礎(chǔ)表rivin g table)是指被最先訪問的表(通常以全表掃描的方式被訪問).根據(jù) 優(yōu)化器的不同，sql語句中基礎(chǔ)表的選擇是不一樣的.如果你使用的是cbo (cost based optt mtzer),優(yōu)化器會檢查sql語句中的每個表的 物理大小，索引的狀態(tài)，然后選用

53、花費(fèi)最低的執(zhí)行路徑.如果你用rbo (rule based optimizer),并且所有的連接條件都有索引對應(yīng)，在這種 情況下，基礎(chǔ)表就是from子句中列在最后的那個表.舉例：select a. name , b. managerfrom work er a,lodg ing bwiier e a. lodgin g = b. lodi ng;由于lodg ing表的lodin g列上有一個索引，而且worker表中沒有相比較的索引， worker表將被作為查詢中的基礎(chǔ)表.2. 多個平等的索引（自查方法）當(dāng)sql語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時，oracle會同時使用多 個索

54、引并在運(yùn)行時對它們的記錄進(jìn)行合并，檢索出僅對全部索引有效的記錄.在oracle選擇執(zhí)行路徑吋,唯一性索引的等級高于非唯一性索引.然而這個規(guī)則只有 當(dāng)where子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較.這種 子句在優(yōu)化器中的等級是非常低的.如果不同表中兩個想同等級的索引將被引用，from子句川表的順序?qū)Q定哪個會被率 先使用.from子句中最后的表的索引將有最高的優(yōu)先級.如果相同表中兩個想同等級的索引將被引用,where子句中最先被引用的索引將有最高 的優(yōu)先級.舉例：deptno上有一個非唯一性索引，emp _cat也有一個非唯一性索引.sele ct ename,from

55、 empw here dept_no = 20and emp cat= 'a'這里,deptno索引將被最先檢索,然后同em p_cat索引檢索岀的記錄進(jìn)行合并.執(zhí)行 路徑如下：tab le access by row id on empand-equaltnde x range sc an on dept _idxindex range sca n on cat_i dx3. 等式比較和范圍比較（自查方法）當(dāng)wh ere子句中有索引列，oracle不能合并它們,oracl e將用范圍比較.舉例：deptno上有一個非唯一性索引,emp_cat也有一個非唯一性索引.s elec

56、t enam efrom empwhere dep tno > 20and emp cat= 'a'這里只有emp.cat索引被用到，然后所有的記錄將逐條與dept no條件進(jìn)行比較.執(zhí)行 路徑如下：ta ble access by rowid on empind ex range scan on cat_idx4. 不明確的索引等級(自查方法)當(dāng)0 racle無法判斷索引的等級高低差別，優(yōu)化器將只使用一個索引，它就是在where 子句中被列在最前面的.舉例：dept no上有一個非唯一性索引，emp cat也有一個非唯一性索引.select e namefrom emp

57、where deptno >20and emp_cat >'a'這里，oracle只用到了 dept_no索引.執(zhí)行路徑如下：table access by rowid on empindex range scan on dept_id x注：我們來試一下以下這種情況：sq l> select index name , uniquene ss from us er indexes where tab le name'emp，;ind ex name un 1quenese mpno uniqu eemptype nonuntquesql> selec t * from e mp where e mpno >= 2 and emp ty pe = 'a' no rows s electedex ecuti on pl an0 select statement optimizer 二choose1 0 table ac cess (by i ndex rowid) of 'emp'2 1 index (range sc an) of 'emptype' (no n-uniq