Oracle SQL優(yōu)化

1、.(摘錄自互聯(lián)網(wǎng))一： oracle 與sql相關(guān)的幾個(gè)概念1. 選用適合的oracle優(yōu)化器 oracle的優(yōu)化器共有3種: a. rule (基于規(guī)則) b. cost (基于成本) c. choose (選擇性) 設(shè) 置缺省的優(yōu)化器,可以通過(guò)對(duì)init.ora文件中optimizer_mode參數(shù)的各種聲明,如rule,cost,choose, all_rows,first_rows . 你當(dāng)然也在sql句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋. 為了使用基于成本的優(yōu)化器(cbo, cost-based optimizer) , 你必須經(jīng)常運(yùn)行analyze 命令,以增加數(shù)據(jù)庫(kù)中的對(duì)

2、象統(tǒng)計(jì) 信息(object statistics)的準(zhǔn)確性. 如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(choose),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過(guò)analyze命令有關(guān). 如果table已經(jīng)被 analyze過(guò), 優(yōu)化器模式將自動(dòng)成為cbo , 反之,數(shù)據(jù)庫(kù)將采用rule形式的優(yōu)化器. 在缺省情況下,oracle采用choose優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用choose優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器. 2.訪問(wèn)table的方式 oracle 采用兩種訪問(wèn)表中記錄的方式: a. 全表掃描 全表掃描就是順序地訪問(wèn)表

3、中每條記錄. oracle采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.b. 通過(guò)rowid訪問(wèn)表 你可以采用基于rowid的訪問(wèn)方式情況,提高訪問(wèn)表的效率, , rowid包含了表中記錄的物理位置信息.oracle采用索引(index)實(shí)現(xiàn) 了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(rowid)之間的聯(lián)系. 通常索引提供了快速訪問(wèn)rowid的方法,因此那些基于索引列的查詢就可以得到性能上的提高. 3.共享sql語(yǔ)句 為 了不重復(fù)解析相同的sql語(yǔ)句,在第一次解析之后, oracle將sql語(yǔ)句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域sga (system global area)的共

4、享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享. 因此,當(dāng)你執(zhí)行一個(gè) sql語(yǔ)句(有時(shí)被稱為一個(gè)游標(biāo))時(shí),如果它 和之前的執(zhí)行過(guò)的語(yǔ)句完全相同, oracle就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑. oracle的這個(gè)功能大大地提高了sql的執(zhí)行性能并節(jié)省了內(nèi)存的使用. 可惜的是oracle只對(duì)簡(jiǎn)單的表提供高速緩沖(cache buffering) ,這個(gè)功能并不適用于多表連接查詢. 數(shù)據(jù)庫(kù)管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù),當(dāng)這個(gè)內(nèi)存區(qū)域越大,就可以保留更多的語(yǔ)句,當(dāng)然被共享的可能性也就越大了. 當(dāng)你向oracle 提交一個(gè)sql語(yǔ)

5、句,oracle會(huì)首先在這塊內(nèi)存中查找相同的語(yǔ)句. 這里需要注明的是,oracle對(duì)兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,sql語(yǔ)句必須 完全相同(包括空格,換行等). 共享的語(yǔ)句必須滿足三個(gè)條件: 精品.a.字符級(jí)的比較: 當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同. 例如: select * from emp; 和下列每一個(gè)都不同 select * from emp; select * from emp; select * from emp; b.兩個(gè)語(yǔ)句所指的對(duì)象必須完全相同: 例如: 用戶 對(duì)象名 如何訪問(wèn) jack sal_limit private synonym work_ci

6、ty public synonym plant_detail public synonym jill sal_limit private synonym work_city public synonym plant_detail table owner 考慮一下下列sql語(yǔ)句能否在這兩個(gè)用戶之間共享.sql能否共享原因select max(sal_cap) from sal_limit;不能每個(gè)用戶都有一個(gè)private synonym - sal_limit , 它們是不同的對(duì)象，在不同的用戶名下，只能內(nèi)部訪問(wèn)select count(*0 from work_city where sdes

7、clike new%;能兩個(gè)用戶訪問(wèn)相同的對(duì)象public synonym - work_city select a.sdesc,b.location from work_city a ,plant_detail b where a.city_id = b.city_id不能用戶jack 通過(guò)public synonym訪問(wèn)plant_detail 而jill 是表的所有者,對(duì)象不同synonym：c. 兩個(gè)sql語(yǔ)句中必須使用相同的名字的綁定變量(bind variables) 例如：第一組的兩個(gè)sql語(yǔ)句是相同的(可以共享),而第二組中的兩個(gè)語(yǔ)句是不同的(即使在運(yùn)行時(shí),賦于不同的綁定變量相

8、同的值) 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; select pin , name from people where pin = :blk1.ov_ind;精品.二. 優(yōu)化方法：1. 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效) oracle 的解析器按照從右到左的順序處理from子句中的表

9、名,因此from子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在 from子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)oracle處理多個(gè)表時(shí), 會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描 第一個(gè)表(from子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行排序序,然后掃描第二個(gè)表(from子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第 一個(gè)表中合適記錄進(jìn)行合并. 例如: 表 tab1 16,384 條記錄 表 tab2 1條記錄 選擇tab2作為基礎(chǔ)表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時(shí)間0.96秒

10、 選擇tab2作為基礎(chǔ)表 (不佳的方法)select count(*) from tab2,tab1 執(zhí)行時(shí)間26.09秒如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表. 例如: emp表描述了location表和category表的交集. select * from location l , category c, emp e where e.emp_no between 1000 and 2000 and e.cat_no = c.cat_no and e.locn = l.locn 將比下列sql更有

11、效率 select * from emp e , location l , category c where e.cat_no = c.cat_no and e.locn = l.locn and e.emp_no between 1000 and 20002. where子句中的連接順序 oracle采用自下而上where子句,根據(jù)這個(gè)原理,表之間的連接必須寫(xiě)在其他where條件之前, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在where子句的末尾. 例如: (低效,執(zhí)行時(shí)間156.3秒) select from emp e where sal 50000 and job = manager

12、and 25 (select count(*) from emp精品.的順序解析where mgr=e.empno); (高效,執(zhí)行時(shí)間10.6秒) select from emp e where 25 50000 and job = manager;3. select子句中避免使用 * 當(dāng)你想在select子句中列出所有的column時(shí),使用動(dòng)態(tài)sql列引用 * 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,oracle在解析的過(guò)程中, 會(huì)將* 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典, 這意味著將耗費(fèi)更多的時(shí)間. 完成的4.減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)當(dāng)執(zhí)行每條sql語(yǔ)句

13、時(shí), oracle在內(nèi)部執(zhí)行了許多工作: 解析sql語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見(jiàn), 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù) , 就能實(shí)際上減少oracle的工作量. 例如, 以下有三種方法可以檢索出雇員號(hào)等于0342或0291的職員. 方法1 (最低效) select emp_name , salary , grade from emp where emp_no = 342; select emp_name , salary , grade from emp where emp_no = 291; 方法2 (次低效) declare cursor c1 (e_no numb

14、er) is select emp_name,salary,gradefrom emp where emp_no = e_no; begin open c1(342); fetch c1 into ,.,. ; open c1(291); fetch c1 into ,.,. ; close c1; end; 方法3 (高效) select a.emp_name , a.salary , a.grade, b.emp_name , b.salary , b.grade from emp a,emp b where a.emp_no = 342 and b.emp_no = 291;精品.sel

15、ect emp_name , salary , grade from emp where emp_no = 342union select emp_name , salary , grade from emp where emp_no = 291; select emp_name , salary , grade from emp where emp_no = 342 or emp_no = 291 ; 注意: 在sql*plus , sql*forms和pro*c中重新設(shè)置arraysize參數(shù), 可以增加每次數(shù)據(jù)庫(kù)訪問(wèn)的檢索數(shù)據(jù)量 ,建議值為200.5.使用decode函數(shù)來(lái)減少處理時(shí)間使

16、用decode函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表. 例如: select count(*)，sum(sal) fromemp where dept_no = 0020 and ename likesmith%; select count(*)，sum(sal) fromemp where dept_no = 0030 and ename likesmith%; 你可以用decode函數(shù)高效地得到相同結(jié)果 select count(decode(dept_no,0020,x,null) d0020_count, count(decode(dept_no,0030,x,null) d0

17、030_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ù)也可以運(yùn)用于group by 和order by子句中.6.整合簡(jiǎn)單,無(wú)關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問(wèn) 如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢語(yǔ)句,你可以把它們整合到一個(gè)查詢中(即使它們之間沒(méi)有關(guān)系) 例如: select name from emp where emp_no = 1234; select name from dp

18、t where dpt_no = 10 ; select name from cat where cat_type = rd; 上面的3個(gè)查詢可以被合并成一個(gè): 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(

19、+) = 10 and c.cat_type(+) = rd; (精品.譯者按: 雖然采取這種方法,效率得到提高,但是程序的可讀性大大降低,所以讀者 還是要權(quán)衡之間的利弊)7.刪除重復(fù)記錄 最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧藃owid) delete from emp e where e.rowid (select min(x.rowid) from emp x where x.emp_no = e.emp_no);8.用truncate替代delete 當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有commit

20、事務(wù),oracle會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說(shuō)是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用truncate時(shí), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. (譯者按: truncate只在刪除全表適用,truncate是ddl不是dml)9.盡量多使用commit 只要有可能,在程序中盡量多使用commit, 這樣程序的性能得到提高,需求也會(huì)因?yàn)閏ommit所釋放的資源而減少: commit所釋放的資源: a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息. b. 被程序語(yǔ)句獲得的鎖 c. redo log buffer 中的空間 d. or

21、acle為管理上述3種資源中的內(nèi)部花費(fèi) (譯者按: 在精品.使用commit時(shí)必須要注意到事務(wù)的完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚(yú)和熊掌不可得兼)10.計(jì)算記錄條數(shù) 和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可以通過(guò)索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如 count(empno) (譯者按: 在csdn論壇中,曾經(jīng)對(duì)此有過(guò)相當(dāng)熱烈的討論, 作者的觀點(diǎn)并不十分準(zhǔn)確,通過(guò)實(shí)際的測(cè)試,上述三種方法并沒(méi)有顯著的性能差別)11.用where子句替換having子句 避免使用having子句, having 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需

22、要排序,總計(jì)等操作. 如果能通過(guò)where子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷. 例如: 低效: select region，avg(log_size) from location group by region having region region != sydney and region != perth 高效 select region，avg(log_size) from location where region region != sydney and region != perth group by region (譯者按: having 中的條件一般用于對(duì)一些集合函數(shù)

23、的比較,如count() 等等. 除此而外,一般的條件應(yīng)該寫(xiě)在where子句中)12.減少對(duì)表的查詢?cè)诤凶硬樵兊膕ql語(yǔ)句中,要特別注意減少對(duì)表的查詢. 例如: 低效 select tab_name from tables where tab_name = ( select tab_name from tab_columns where version = 604) anddb_ver= ( select db_ver from tab_columns where version = 604) 高效 select tab_name from tables where (tab_name,db

24、_ver) = ( select tab_name,db_ver) from tab_columns where version = 604) update 多個(gè)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(ca

25、tegory) , max(sal_range) from emp_categories) where emp_dept = 0020;精品.13通過(guò)內(nèi)部函數(shù)提高sql效率. 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; 通過(guò)調(diào)用下面的函數(shù)可以提

26、高效率. 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

27、); 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), h.hist_type,lookup_hist_type(h.hist_type),count(*) from emp_history h group by h.empno , h.hist_type; (精品.譯者按: 經(jīng)常在論壇中看到如 能不能用一個(gè)sql寫(xiě)出. 的貼

28、子, 殊不知復(fù)雜的sql往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問(wèn)題的方法在實(shí)際工作中是非常有意義的)14.使用表的別名(alias) 當(dāng)在sql語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由column歧義引起的語(yǔ)法錯(cuò)誤. (譯者注: column歧義指的是由于sql中不同的表具有相同的column名,當(dāng)sql語(yǔ)句中出現(xiàn)這個(gè)column時(shí),sql解析器無(wú)法判斷這個(gè)column的歸屬) 15.用exists替代in 在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用exists(

29、或not exists)通常將提高查詢的效率. 低效: select * from emp (基礎(chǔ)表) where empno 0 and deptno in (select deptno from dept where loc = melb) 高效: select * from emp (基礎(chǔ)表) where empno 0 and exists (select x from dept where dept.deptno = emp.deptno and loc = melb)這里 oracle 會(huì)把 exists 解析 成 join 語(yǔ)句精品.(譯者按: 相對(duì)來(lái)說(shuō),用not exists替

30、換not in 將更顯著地提高效率,下一節(jié)中將指出)16.用not exists替代not in 在子查詢中,not in子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,not in都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 為了避免使用not in ,我們可以把它改寫(xiě)成外連接(outer joins)或not exists.例如: select from emp where dept_no not in (select dept_no from dept where dept_cat=a); 為了提高效率.改寫(xiě)為: (方法一: 高效) select . from emp

31、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);17.用表連接替換exists 通常來(lái)說(shuō) , 采用表連接的方式比exists更有效率 select ename from emp e where exists (select x from dept whe

32、re 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 ; (譯者按: 在rbo的情況下,前者的執(zhí)行路徑包括filter,后者使用nested loop)精品.18.用exists替換distinct 當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門(mén)表和雇員表)的查詢時(shí),避免在select子句中使用distinct. 一般可以考慮用exist替換 例如: 低效: select distinct dept_no,dep

33、t_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 使查詢更為迅速,因?yàn)閞dbms核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果. 19.識(shí)別低效執(zhí)行的sql語(yǔ)句 用下列sql工具找出低效sql: select executions , disk_reads, buffer_gets, round(buffer_ge

34、ts-disk_reads)/buffer_gets,2) hit_radio, round(disk_reads/executions,2) reads_per_run, sql_text from v$sqlarea where executions;0 and buffer_gets ; 0 and (buffer_gets-disk_reads)/buffer_gets ; list 1 select * 2 from dept, emp 3* where emp.deptno = dept.deptno sql; set autotrace traceonly /*traceonly

35、 可以不顯示執(zhí)行結(jié)果*/ sql; / 14 rows selected. execution plan - 0 select statement optimizer=choose 1 0 nested loops 2 1 table access (full) of emp 3 1 table access (by index rowid) of dept 4 3 index (unique scan) of pk_dept (unique) statistics - 0 recursive calls 2 db block gets 30 consistent gets 0 physica

36、l reads 0 redo size 2598 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed 通過(guò)以上分析,可以得出實(shí)際的執(zhí)行步驟是: 1. table access (full) of emp 2. index (unique scan) of pk_dept (unique) 3. table access (by

37、 index rowid) of dept 4. nested loops (joining 1 and 3) 注: 目前許多第三方的工具如toad和oracle本身提供的工具如oms的sql analyze都提供了極其方便的explain plan工具.也許喜歡圖形化界面的朋友們可以選用它們. 精品.22.用索引提高效率 索引是表的一個(gè)概念部分, 用來(lái)提高檢索數(shù)據(jù)的效率. 實(shí)際上,oracle使用了一個(gè)復(fù)雜的自平衡b-tree結(jié)構(gòu). 通常,通過(guò)索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)oracle找 出執(zhí)行查詢和update語(yǔ)句的最佳路徑時(shí), oracle優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引

38、也可以提高效率. 另一個(gè)使用索引的好處是, 它提供了主鍵(primary key)的唯一性驗(yàn)證. 除了那些long或long raw數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來(lái) 存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的insert , delete , update將為此多付出4 , 5 次的磁盤(pán)i/o . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而

39、會(huì)使查詢反應(yīng)時(shí)間變慢. 譯者按: 定期的重構(gòu)索引是有必要的. alter index ; rebuild ; 23.索引的操作 oracle對(duì)索引有兩種訪問(wèn)模式. 索引唯一掃描 ( index unique scan) 大多數(shù)情況下, 優(yōu)化器通過(guò)where子句訪問(wèn)index. 例如: 表lodging有兩個(gè)索引 : 建立在lodging列上的唯一性索引lodging_pk和建立在manager列上的非唯一性索引lodging$manager. select * from lodging where lodging = rose hill; 在內(nèi)部 , 上述sql將被分成兩步執(zhí)行, 首先 , l

40、odging_pk 索引將通過(guò)索引唯一掃描的方式被訪問(wèn) , 獲得相對(duì)應(yīng)的rowid, 通過(guò)rowid訪問(wèn)表的方式 執(zhí)行下一步檢索. 如果被檢索返回的列包括在index列中,oracle將不執(zhí)行第二步的處理(通過(guò)rowid訪問(wèn)表). 因?yàn)闄z索數(shù)據(jù)保存在索引中, 單單訪問(wèn)索引就可以完全滿足查詢結(jié)果. 下面sql只需要index unique scan 操作. select lodging from lodging where lodging = rose hill; 索引范圍查詢(index range scan) 適用于兩種情況: 1. 基于一個(gè)范圍的檢索 2. 基于非唯一性索引的檢索 例1:

41、select lodging from lodging where lodging like m%; where子句條件包括一系列值, oracle將通過(guò)索引范圍查詢的方式查詢lodging_pk . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描低一些. 例2: select lodging from lodging where manager = bill gates; 這個(gè)sql的執(zhí)行分兩步, lodging$manager的索引范圍查詢(得到所有符合條件記錄的rowid) 和下一步同過(guò)rowid訪問(wèn)表得到 lodging列的值. 由于lodging$manager是一個(gè)非唯

42、一性的索引,數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描. 由于sql返回lodging列,而它并不存在于lodging$manager索引中, 所以在索引范圍查詢后會(huì)執(zhí)行一個(gè)通過(guò)rowid訪問(wèn)表的操作. where子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(wildcard)開(kāi)始, 索引將不被采用. select lodging from lodging where manager like hanman; 精品.在這種情況下，oracle將使用全表掃描. 精品.24. 基礎(chǔ)表的選擇 基礎(chǔ)表(driving table)是指被最先訪問(wèn)的表(通常以全表掃描的方式被訪問(wèn)). 根據(jù)優(yōu)化器的不同, sql

43、語(yǔ)句中基礎(chǔ)表的選擇是不一樣的. 如果你使用的是cbo (cost based optimizer),優(yōu)化器會(huì)檢查sql語(yǔ)句中的每個(gè)表的物理大小,索引的狀態(tài),然后選用花費(fèi)最低的執(zhí)行路徑. 如果你用rbo (rule based optimizer) , 并且所有的連接條件都有索引對(duì)應(yīng), 在這種情況下, 基礎(chǔ)表就是from 子句中列在最后的那個(gè)表. 舉例: select a.name , b.manager fromworker a, lodging b wherea.lodging = b.loding; 由于lodging表的loding列上有一個(gè)索引, 而且worker表中沒(méi)有相比較的索引,

44、 worker表將被作為查詢中的基礎(chǔ)表. 25.多個(gè)平等的索引 當(dāng)sql語(yǔ)句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí), oracle會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并, 檢索出僅對(duì)全部索引有效的記錄. 在oracle選擇執(zhí)行路徑時(shí),唯一性索引的等級(jí)高于非唯一性索引. 然而這個(gè)規(guī)則只有 當(dāng)where子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優(yōu)化器中的等級(jí)是非常低的. 如果不同表中兩個(gè)相同等級(jí)的索引將被引用, from子句中表的順序?qū)Q定哪個(gè)會(huì)被率先使用. from子句中最后的表的索引將有最高的優(yōu)先級(jí). 如果相同表中兩個(gè)想同等級(jí)的索引將被引用, where子句中最先被引用的索引將有最高的優(yōu)先級(jí). 舉例: deptno上有一個(gè)非唯一性索引,emp_cat也有一個(gè)非唯一性索引. select ename, from emp where dept_no = 20 and e