OracleSQL性能調整.doc

oracle sql 性能調整性能調整 1.1. 選用適合的選用適合的 oracleoracle 優(yōu)化器優(yōu)化器 oracle 的優(yōu)化器共有 3 種: a. rule (基于規(guī)則) b. cost (基于成本) c. choose (選擇性) 設置缺省的優(yōu)化器,可以通過對 init.ora 文件中 optimizer_mode 參數(shù)的各種聲明,如 rule, cost,choose,all_rows,first_rows . 你當然也在 sql 句級或是會話(session)級對其進行覆蓋. 為了使用基于成本的優(yōu)化器(cbo, cost-based optimizer) , 你必須經(jīng)常運行 analyze 命 令,以增加數(shù)據(jù)庫中的對象統(tǒng)計信息(object statistics)的準確性. 如果數(shù)據(jù)庫的優(yōu)化器模式設置為選擇性(choose),那么實際的優(yōu)化器模式將和是否運行過 an alyze 命令有關. 如果 table 已經(jīng)被 analyze 過, 優(yōu)化器模式將自動成為 cbo , 反之,數(shù)據(jù)庫將 采用 rule 形式的優(yōu)化器. 在缺省情況下,oracle 采用 choose 優(yōu)化器, 為了避免那些不必要的全表掃描(full table s can) , 你必須盡量避免使用 choose 優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器. 2.2.訪問訪問 tabletable 的方式的方式 oracle 采用兩種訪問表中記錄的方式: a.全表掃描 全表掃描就是順序地訪問表中每條記錄. oracle 采用一次讀入多個數(shù)據(jù)塊(database bl ock)的方式優(yōu)化全表掃描. b.通過 rowid 訪問表 你可以采用基于 rowid 的訪問方式情況,提高訪問表的效率, , rowid 包含了表中記錄的 物理位置信息oracle 采用索引(index)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(rowid)之間的 聯(lián)系. 通常索引提供了快速訪問 rowid 的方法,因此那些基于索引列的查詢就可以得到性 能上的提高. 3.3.共享共享 sqlsql 語句語句 為了不重復解析相同的 sql 語句,在第一次解析之后, oracle 將 sql 語句存放在內存中.這 塊位于系統(tǒng)全局區(qū)域 sga(system global area)的共享池(shared buffer pool)中的內存可以 被所有的數(shù)據(jù)庫用戶共享. 因此,當你執(zhí)行一個 sql 語句(有時被稱為一個游標)時,如果它和之 前的執(zhí)行過的語句完全相同, oracle 就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑. or acle 的這個功能大大地提高了 sql 的執(zhí)行性能并節(jié)省了內存的使用. 可惜的是 oracle 只對簡單的表提供高速緩沖(cache buffering) ,這個功能并不適用于多 表連接查詢. 數(shù)據(jù)庫管理員必須在 init.ora 中為這個區(qū)域設置合適的參數(shù),當這個內存區(qū)域越大,就可 以保留更多的語句,當然被共享的可能性也就越大了. 當你向 oracle 提交一個 sql 語句,oracle 會首先在這塊內存中查找相同的語句.這里需要 注明的是,oracle 對兩者采取的是一種嚴格匹配,要達成共享,sql 語句必須完全相同(包括空格, 換行等). 共享的語句必須滿足三個條件: a.字符級的比較: 當前被執(zhí)行的語句和共享池中的語句必須完全相同當前被執(zhí)行的語句和共享池中的語句必須完全相同. . 例如: select * from emp; 和下列每一個都不同 select * from emp; select * from emp; select * from emp; b. 兩個語句所指的對象必須完全相同兩個語句所指的對象必須完全相同: : 例如: 用戶 對象名 如何訪問 jack sal_limit private synonym work_city public synonym plant_detail public synonym jill sal_limit private synonym work_city public synonym plant_detail table owner 考慮一下下列 sql 語句能否在這兩個用戶之間共享. sqlsql 能否共享能否共享原因原因 select max(sal_cap) from sal_limit; 不能 每個用戶都有一個 private synonym - sal_limit , 它們是不 同的對象 select count(*0 from work_city where sdesc like new%; 能 兩個用戶訪問相同的對象 public synonym - work_city select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用戶 jack 通過 private synonym 訪問 plant_detail 而 jill 是表 的所有者,對象不同. c. 兩個兩個 sqlsql 語句中必須使用相同的名字的綁定變量語句中必須使用相同的名字的綁定變量(bind(bind variables)variables) 例如： 第一組的兩個 sql 語句是相同的(可以共享),而第二組中的兩個語句是不同的(即使在 運行時,賦于不同的綁定變量相同的值) a. select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin; b. select pin , name from people where pin = :blk1.ot_ind:blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind:blk1.ov_ind; 4.4. 選擇最有效率的表名順序選擇最有效率的表名順序( (只在基于規(guī)則的優(yōu)化器中有效只在基于規(guī)則的優(yōu)化器中有效) ) oracleoracle 的解析器按照從右到左的順序處理的解析器按照從右到左的順序處理 fromfrom 子句中的表名子句中的表名,因此 from 子句中寫在最后 的表(基礎表 driving table)將被最先處理. 在 from 子句中包含多個表的情況下,你必須選擇 記錄條數(shù)最少的表作為基礎表.當 oracle 處理多個表時, 會運用排序及合并的方式連接它們.首 先,掃描第一個表(from 子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(from 子句 中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并. 例如: 表 tab1 16,384 條記錄 表 tab2 1 條記錄 選擇 tab2 作為基礎表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時間 0.96 秒 選擇 tab2 作為基礎表 (不佳的方法) select count(*) from tab2,tab1 執(zhí)行時間 26.09 秒 如果有 3 個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉 表是指那個被其他表所引用的表. 例如: emp 表描述了 location 表和 category 表的交集. select * from location l , category c, empemp e e where e.emp_no between 1000 and 2000 and e.cat_no = c.cat_no and e.locn = l.locn 將比下列 sql 更有效率 select * from empemp e e , , location l , category c where e.cat_no = c.cat_no and e.locn = l.locn and e.emp_no between 1000 and 2000 5.where5.where 子句中的連接順序子句中的連接順序 oracleoracle 采用自下而上的順序解析采用自下而上的順序解析 wherewhere 子句子句,根據(jù)這個原理,表之間的連接必須寫在其他 wh ere 條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在 where 子句的末尾. 例如: (低效,執(zhí)行時間 156.3 秒) select from emp e where salsal 5000050000 andand jobjob = = managermanager and 25 5000050000 andand jobjob = = manager;manager; 6.select6.select 子句中避免使用子句中避免使用 * * 當你想在 select 子句中列出所有的 column 時,使用動態(tài) sql 列引用 * 是一個方便的 方法.不幸的是,這是一個非常低效的方法. 實際上,oracle 在解析的過程中, 會將* 依次轉換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費更多的時 間. 7.7.減少訪問數(shù)據(jù)庫的次數(shù)減少訪問數(shù)據(jù)庫的次數(shù) 當執(zhí)行每條 sql 語句時, oracle 在內部執(zhí)行了許多工作: 解析 sql 語句, 估算索引的利 用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實際上減少 orac le 的工作量. 例如,以下有三種方法可以檢索出雇員號等于 0342 或 0291 的職員. 方法 1 (最低效) select emp_name , salary , grade fromfrom empemp where emp_no = 342; select emp_name , salary , grade fromfrom empemp where emp_no = 291; 方法 2 (次低效) declare cursor c1 (e_no number) is select emp_name,salary,grade from emp where emp_no = e_no; begin open c1(342); fetchfetch c1c1 intointo , ; ; open c1(291); fetchfetch c1c1 intointo , ; ; close c1; end; 方法 3 (高效) select a.emp_name , a.salary , a.grade, b.emp_name , b.salary , b.grade fromfrom empemp a,empa,emp b b where a.emp_no = 342 and b.emp_no = 291; 注意注意: : 在 sql*plus , sql*forms 和 pro*c 中重新設置 arraysize 參數(shù), 可以增加每次數(shù)據(jù)庫 訪問的檢索數(shù)據(jù)量 ,建議值為 200 8.8.使用使用 decodedecode 函數(shù)來減少處理時間函數(shù)來減少處理時間 使用 decode 函數(shù)可以避免重復掃描相同記錄或重復連接相同的表. 例如: select count(*)，sum(sal) from emp where dept_no = 0020 and ename like smith%; select count(*)，sum(sal) from emp where dept_no = 0030 and ename like smith%; 你可以用 decode 函數(shù)高效地得到相同結果 select count(decode(dept_no,0020,x,null) d0020_count, count(decode(dept_no,0030,x,null) d0030_count, sum(decode(dept_no,0020,sal,null) d0020_sal, sum(decode(dept_no,0030,sal,null) d0030_sal from emp where ename like smith%; 類似的,decode 函數(shù)也可以運用于 group by 和 order by 子句中. 9.9.整合簡單整合簡單, ,無關聯(lián)的數(shù)據(jù)庫訪問無關聯(lián)的數(shù)據(jù)庫訪問 如果你有幾個簡單的數(shù)據(jù)庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有 關系) 例如: select name from emp where emp_no = 1234; select name from dpt where dpt_no = 10 ; select name from cat where cat_type = rd; 上面的 3 個查詢可以被合并成一個: select e.name , d.name , c.name from cat c , dpt d , emp e,dual x where nvl(x,x.dummy) = nvl(x,e.rowid(+) and nvl(x,x.dummy) = nvl(x,d.rowid(+) and nvl(x,x.dummy) = nvl(x,c.rowid(+) and e.emp_no(+) = 1234 and d.dept_no(+) = 10 and c.cat_type(+) = rd; ( (譯者按譯者按: : 雖然采取這種方法雖然采取這種方法, ,效率得到提高效率得到提高, ,但是程序的可讀性大大降低但是程序的可讀性大大降低, ,所以讀者所以讀者 還是要還是要 權衡之間的利弊權衡之間的利弊) ) 10.10.刪除重復記錄刪除重復記錄 最高效的刪除重復記錄方法 ( 因為使用了 rowid) delete from emp e where e.rowid (select min(x.rowid) from emp x where x.emp_no = e.emp_no); 11.11.用用 truncatetruncate 替代替代 deletedelete 當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復 的信息. 如果你沒有 commit 事務,oracle 會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準確地說是恢 復到執(zhí)行刪除命令之前的狀況) 而當運用 truncate 時, 回滾段不再存放任何可被恢復的信息.當命令運行后,數(shù)據(jù)不能被恢 復.因此很少的資源被調用,執(zhí)行時間也會很短. ( (譯者按譯者按: : truncatetruncate 只在刪除全表適用只在刪除全表適用,truncate,truncate 是是 ddlddl 不是不是 dml)dml) 12.12.盡量多使用盡量多使用 commitcommit 只要有可能,在程序中盡量多使用 commit, 這樣程序的性能得到提高,需求也會因為 commit 所釋放的資源而減少: commit 所釋放的資源: a. 回滾段上用于恢復數(shù)據(jù)的信息. b. 被程序語句獲得的鎖 c. redo log buffer 中的空間 d. oracle 為管理上述 3 種資源中的內部花費 ( (譯者按譯者按: : 在使用在使用 commitcommit 時必須要注意到事務的完整性時必須要注意到事務的完整性, ,現(xiàn)實中效率和事務完整性往往是魚現(xiàn)實中效率和事務完整性往往是魚 和熊掌不可得兼和熊掌不可得兼) ) 13.13.計算記錄條數(shù)計算記錄條數(shù) 和一般的觀點相反, count(*) 比 count(1)稍快 , 當然如果可以通過索引檢索,對索引列 的計數(shù)仍舊是最快的. 例如 count(empno) ( (譯者按譯者按: : 在在 csdncsdn 論壇中論壇中, ,曾經(jīng)對此有過相當熱烈的討論曾經(jīng)對此有過相當熱烈的討論, , 作者的觀點并不十分準確作者的觀點并不十分準確, ,通過通過 實際的測試實際的測試, ,上述三種方法并沒有顯著的性能差別上述三種方法并沒有顯著的性能差別) ) 14.14.用用 wherewhere 子句替換子句替換 havinghaving 子句子句 避免使用 having 子句, having 只會在檢索出所有記錄之后才對結果集進行過濾. 這個處 理需要排序,總計等操作. 如果能通過 where 子句限制記錄的數(shù)目,那就能減少這方面的開銷. 例如: 低效: select region，avg(log_size) from location group by region having region region != sydney and region != perth 高效 select region，avg(log_size) from location wherewhere regionregion regionregion !=!= sydneysydney andand regionregion !=!= perthperth group by region ( (譯者按譯者按: : havinghaving 中的條件一般用于對一些集合函數(shù)的比較中的條件一般用于對一些集合函數(shù)的比較, ,如如 count()count() 等等等等. . 除此而外除此而外, ,一般一般 的條件應該寫在的條件應該寫在 wherewhere 子句中子句中) ) 15.15.減少對表的查詢減少對表的查詢 在含有子查詢的 sql 語句中,要特別注意減少對表的查詢. 例如: 低效 select tab_name from tables where tab_name = ( select tab_name from tab_columns where version = 604) and db_ver= ( select db_ver from tab_columns where version = 604) 高效 select tab_name from tables where (tab_name,db_ver) = ( select tab_name,db_ver) from tab_columns where version = 604) update 多個 column 例子: 低效: update emp set emp_cat = (select max(category) from emp_categories), sal_range = (select max(sal_range) from emp_categories) where emp_dept = 0020; 高效: update emp set (emp_cat, sal_range) = (select max(category) , max(sal_range) from emp_categories) where emp_dept = 0020; 16.16.通過內部函數(shù)提高通過內部函數(shù)提高 sqlsql 效率效率. . select h.empno,e.ename,h.hist_type,t.type_desc,count(*) from history_type t,emp e,emp_history h where h.empno = e.empno and h.hist_type = t.hist_type group by h.empno,e.ename,h.hist_type,t.type_desc; 通過調用下面的函數(shù)可以提高效率. function lookup_hist_type(typ in number) return varchar2 as tdesc varchar2(30); cursor c1 is select type_desc from history_type where hist_type = typ; begin open c1; fetch c1 into tdesc; close c1; return (nvl(tdesc,?); end; function lookup_emp(emp in number) return varchar2 as ename varchar2(30); cursor c1 is select ename from emp where empno=emp; begin open c1; fetch c1 into ename; close c1; return (nvl(ename,?); end; select h.empno,lookup_emp(h.empno),lookup_emp(h.empno), h.hist_type,lookup_hist_type(h.hist_type)lookup_hist_type(h.hist_type),count(*) from emp_history h group by h.empno , h.hist_type; ( (譯者按譯者按: : 經(jīng)常在論壇中看到如經(jīng)常在論壇中看到如 能不能用一個能不能用一個 sqlsql 寫出寫出. 的貼子的貼子, , 殊不知復雜的殊不知復雜的 sqlsql 往往 往犧牲了執(zhí)行效率往犧牲了執(zhí)行效率. . 能夠掌握上面的運用函數(shù)解決問題的方法在實際工作中是非常有意義的能夠掌握上面的運用函數(shù)解決問題的方法在實際工作中是非常有意義的) ) 17.17.使用表的別名使用表的別名(alias)(alias) 當在 sql 語句中連接多個表時, 請使用表的別名并把別名前綴于每個 column 上.這樣一來, 就可以減少解析的時間并減少那些由 column 歧義引起的語法錯誤. ( (譯者注譯者注: : columncolumn 歧義指的是由于歧義指的是由于 sqlsql 中不同的表具有相同的中不同的表具有相同的 columncolumn 名名, ,當當 sqlsql 語句中出現(xiàn)這語句中出現(xiàn)這 個個 columncolumn 時時,sql,sql 解析器無法判斷這個解析器無法判斷這個 columncolumn 的歸屬的歸屬) ) 18.18.用用 existsexists 替代替代 inin 在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯(lián)接.在這種情況 下, 使用 exists(或 not exists)通常將提高查詢的效率. 低效: select * from emp (基礎表) where empno 0 and deptno in (select deptno from dept where loc = melb) 高效: select * from emp (基礎表) where empno 0 and exists (select x from dept where dept.deptno = emp.deptno and loc = melb) ( (譯者按譯者按: : 相對來說相對來說, ,用用 notnot existsexists 替換替換 notnot inin 將更顯著地提高效率將更顯著地提高效率, ,下一節(jié)中將指出下一節(jié)中將指出) ) 19.19.用用 notnot existsexists 替代替代 notnot inin 在子查詢中,not in 子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況下,not in 都是最 低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 為了避免使用 not in ,我們可以 把它改寫成外連接(outer joins)或 not exists. 例如: select from emp where dept_no not in (select dept_no from dept where dept_cat=a); 為了提高效率.改寫為: (方法一: 高效) select . from emp a,dept b where a.dept_no = b.dept(+) and b.dept_no is null and b.dept_cat(+) = a (方法二: 最高效) select . from emp e where not exists (select x from dept d where d.dept_no = e.dept_no and dept_cat = a); 20.20.用表連接替換用表連接替換 existsexists 通常來說 , 采用表連接的方式比 exists 更有效率 select ename from emp e where exists (select x from dept where dept_no = e.dept_no and dept_cat = a); (更高效) select ename from dept d,emp e where e.dept_no = d.dept_no and dept_cat = a ; ( (譯者按譯者按: : 在在 rborbo 的情況下的情況下, ,前者的執(zhí)行路徑包括前者的執(zhí)行路徑包括 filter,filter,后者使用后者使用 nestednested loop)loop) 21.21.用用 existsexists 替換替換 distinctdistinct 當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在 select 子句中使用 distinct. 一般可以考慮用 exist 替換 例如: 低效: select distinctdistinct dept_no,dept_name from dept d,emp e where d.dept_no = e.dept_no 高效: select dept_no,dept_name from dept d where exists ( select x from emp e where e.dept_no = d.dept_no); exists 使查詢更為迅速,因為 rdbms 核心模塊將在子查詢的條件一旦滿足后,立刻返回 結果. 22.22.識別識別低效執(zhí)行低效執(zhí)行的的 sqlsql 語句語句 用下列 sql 工具找出低效 sql: select executions , disk_reads, buffer_gets, round(buffer_gets-disk_reads)/buffer_gets,2) hit_radio, round(disk_reads/executions,2) reads_per_run, sql_text from v$sqlarea where executions0 and buffer_gets 0 and (buffer_gets-disk_reads)/buffer_gets sql listlist 1 1 selectselect * * 2 2 fromfrom dept,dept, empemp 3*3* wherewhere emp.deptnoemp.deptno = = dept.deptnodept.deptno sqlsql setset autotraceautotrace traceonlytraceonly /*traceonly/*traceonly 可以不顯示執(zhí)行結果可以不顯示執(zhí)行結果*/*/ sqlsql / / 1414 rowsrows selected.selected. executionexecution planplan - 0 0 selectselect statementstatement optimizer=chooseoptimizer=choose 1 1 0 0 nestednested loopsloops 2 2 1 1 tabletable accessaccess (full)(full) ofof empemp 3 3 1 1 tabletable accessaccess (by(by indexindex rowid)rowid) ofof deptdept 4 4 3 3 indexindex (unique(unique scan)scan) ofof pk_deptpk_dept (unique)(unique) statisticsstatistics - 0 0 recursiverecursive callscalls 2 2 dbdb blockblock getsgets 3030 consistentconsistent getsgets 0 0 physicalphysical readsreads 0 0 redoredo sizesize 25982598 bytesbytes sentsent viavia sql*netsql*net toto clientclient 503503 bytesbytes receivedreceived viavia sql*netsql*net fromfrom clientclient 2 2 sql*netsql*net roundtripsroundtrips to/fromto/from clientclient 0 0 sortssorts (memory)(memory) 0 0 sortssorts (disk)(disk) 1414 rowsrows processedprocessed 通過以上分析通過以上分析, ,可以得出實際的執(zhí)行步驟是可以得出實際的執(zhí)行步驟是: : 1.1. tabletable accessaccess (full)(full) ofof empemp 2.2. indexindex (unique(unique scan)scan) ofof pk_deptpk_dept (unique)(unique) 3.3. tabletable accessaccess (by(by indexindex rowid)rowid) ofof deptdept 4.4. nestednested loopsloops (joining(joining 1 1 andand 3)3) 注注: : 目前許多第三方的工具如目前許多第三方的工具如 toadtoad 和和 oracleoracle 本身提供的工具如本身提供的工具如 omsoms 的的 sqlsql analyzeanalyze 都提供了都提供了 極其方便的極其方便的 explainexplain planplan 工具工具. .也許喜歡圖形化界面的朋友們可以選用它們也許喜歡圖形化界面的朋友們可以選用它們. . 25.25.用索引提高效率用索引提高效率 索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率. 實際上,oracle 使用了一個復雜的自 平衡 b-tree 結構. 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當 oracle 找出執(zhí)行查詢和 updat e 語句的最佳路徑時, oracle 優(yōu)化器將使用索引. 同樣在聯(lián)結多個表時使用索引也可以提高效 率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證. 除了那些 long 或 long raw 數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用 索引特別有效. 當然,你也會發(fā)現(xiàn), 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來 存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意 味著每條記錄的 insert , delete , update 將為此多付出 4 , 5 次的磁盤 i/o . 因為索引需 要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢. 譯者按譯者按: : 定期的重構索引是有必要的定期的重構索引是有必要的. . alteralter indexindex rebuildrebuild 26.26.索引的操作索引的操作 oracle 對索引有兩種訪問模式. 索引唯一掃描 ( index unique scan) 大多數(shù)情況下, 優(yōu)化器通過 where 子句訪問 index. 例如: 表 lodging 有兩個索引 : 建立在 lodging 列上的唯一性索引 lodging_pk 和建立在 ma nager 列上的非唯一性索引 lodging$manager. select * from lodging where lodging = rose hill; 在內部 , 上述 sql 將被分成兩步執(zhí)行, 首先 , lodging_pk 索引將通過索引唯一掃描的方 式被訪問 , 獲得相對應的 rowid, 通過 rowid 訪問表的方式執(zhí)行下一步檢索. 如果被檢索返回的列包括在 index 列中,oracle 將不執(zhí)行第二步的處理(通過 rowid 訪問表). 因為檢索數(shù)據(jù)保存在索引中, 單單訪問索引就可以完全滿足查詢結果. 下面 sql 只需要 index unique scan 操作. select lodging from lodging where lodging = rose hill; 索引范圍查詢(index range scan) 適用于兩種情況: 1. 基于一個范圍的檢索 2. 基于非唯一性索引的檢索 例 1: select lodging from lodging where lodging like m%; where 子句條件包括一系列值, oracle 將通過索引范圍查詢的方式查詢 lodging_pk . 由于 索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描 低一些. 例 2: select lodging from lodging where manager = bill gates; 這個 sql 的執(zhí)行分兩步, lodging$manager 的索引范圍查詢(得到所有符合條件記錄的 rowid) 和下一步同過 rowid 訪問表得到 lodging 列的值. 由于 lodging$manager 是一個非唯一性的索 引,數(shù)據(jù)庫不能對它執(zhí)行索引唯一掃描. 由于 sql 返回 lodging 列,而它并不存在于 lodging$manager 索引中, 所以在索引范圍查詢后 會執(zhí)行一個通過 rowid 訪問表的操作. where 子句中, 如果索引列所對應的值的第一個字符由通配符(wildcard)開始, 索引將不被采 用. select lodging from lodging where manager like hanman; 在這種情況下，oracle 將使用全表掃描. 27.27.基礎表的選擇基礎表的選擇 基礎表(driving table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據(jù)優(yōu)化器 的不同, sql 語句中基礎表的選擇是不一樣的. 如果你使用的是 cbo (cost based optimizer),優(yōu)化器會檢查 sql 語句中的每個表的物理 大小,索引的狀態(tài),然后選用花費最低的執(zhí)行路徑. 如果你用 rbo (rule based optimizer) , 并且所有的連接條件都有索引對應, 在這種情 況下, 基礎表就是 from 子句中列在最后的那個表. 舉例: select a.name , b.manager from worker a, lodging b where a.lodging = b.loding; 由于 lodging 表的 loding 列上有一個索引, 而且 worker 表中沒有相比較的索引, worker 表將被作為查詢中的基礎表. 28.28.多個平等的索引多個平等的索引 當 sql 語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時, oracle 會同時使用多個索 引并在運行時對它們的記錄進行合并, 檢索出僅對全部索引有效的記錄. 在 oracle 選擇執(zhí)行路徑時,唯一性索引的等級高于非唯一性索引. 然而這個規(guī)則只有 當 where 子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優(yōu) 化器中的等級是非常低的. 如果不同表中兩個想同等級的索引將被引用, from 子句中表的順序將決定哪個會被率先使 用. from 子句中最后的表的索引將有最高的優(yōu)先級. 如果相同表中兩個想同等級的索引將被引用, where 子句中最先被引用的索引將有最高的 優(yōu)先級. 舉例: deptno 上有一個非唯一性索引,emp_cat 也有一個非唯一性索引. select ename, from emp where dept_no = 20 and emp_cat = a; 這里,deptno 索引將被最先檢索,然后同 emp_cat 索引檢索出的記錄進行合并. 執(zhí)行路徑如 下: table access by rowid on emp and-equal index range scan on dept_idx index range scan on cat_idx 29.29. 等式比較和范圍比較等式比較和范圍比較 當 where 子句中有索引列, , oracle 不能合并它們,oracle 將用范圍比較. 舉例: deptno 上有一個非唯一性索引,emp_cat 也有一個非唯一性索引. select ename from emp where deptno 20 and emp_cat = a; 這里只有 emp_cat 索引被用到,然后所有的記錄將逐條與 deptno 條件進行比較. 執(zhí) 行路徑如下: table access by rowid on emp index range scan on cat_idx 30.30.不明確的索引等級不明確的索引等級 當 oracle 無法判斷索引的等級高低差別,優(yōu)化器將只使用一個索引,它就是在 where 子句 中被列在最前面的.舉例: deptno 上有一個非唯一性索引,emp_cat 也有一個非唯一性索引. select ename from emp where deptno 20 and emp_cat a; 這里, oracle 只用到了 dept_no 索引. 執(zhí)行路徑如下: table access by rowid on emp index range scan on dept_idx 譯者按譯者按: : 我們來試一下以下這種情況我們來試一下以下這種情況: : sqlsql selectselect index_name,index_name, uniquenessuniqueness fromfrom user_indexesuser_indexes wherewhere table_nametable_name = = emp;emp; index_nameindex_name uniquenesuniquenes - - empnoempno uniqueunique emptypeemptype nonuniquenonunique sqlsql selectselect * * fromfrom empemp wherewhere empnoempno = 2 2 andand emp_typeemp_type = = aa ; ; nono rowsrows selectedselected executionexecution planplan - 0 0 selectselect statementstatement optimizer=chooseoptimizer=choose 1 1 0 0 tabletable accessaccess (by(by indexindex rowid)rowid) ofof empemp 2 2 1 1 indexindex (range(range scan)scan) ofof emptypeemptype (non-unique)(non-unique) 雖然雖然 empnoempno 是唯一性索引是唯一性索引, ,但是由于它所做的是范圍比較但是由于它所做的是范圍比較, , 等級要比非唯一性索引的等式等級要比非唯一性索引的等式 比較低比較低! ! 31.31.強制索引失效強制索引失效 如果兩個或以上索引具有相同的等級,你可以強制命令 oracle 優(yōu)化器使用其中的一個(通過 它,檢索出的記錄數(shù)量少) . 舉例: select ename from emp where empno = 7935 and deptno + 0 = 10 /*deptno/