oracle中查詢效率的優(yōu)化

1、Oracle中怎樣提高査詢效率(sql優(yōu)化)一、執(zhí)行順序及優(yōu)化細(xì)則1表名順序優(yōu)化基礎(chǔ)表放下面，當(dāng)兩表進(jìn)行關(guān)聯(lián)時(shí)數(shù)據(jù)量少的表的表名放右邊表或視圖：Stude nt_info (30000 條數(shù)據(jù))Descriptio n_info (30 條數(shù)據(jù))select *from description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_ID與select *from stude nt_infosi-學(xué) 生信息表,descripti o

2、n_info diwhere si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_ID以student_info作為基礎(chǔ)表，你會(huì)發(fā)現(xiàn)運(yùn)行的速度會(huì)有很大的差距。當(dāng)出現(xiàn)多個(gè)表時(shí)關(guān)聯(lián)表被稱之為交叉表交叉表作為基礎(chǔ)表select *from description_info di,descripti on_info di2,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand si

3、.school_id =di.lo okup_code(+)and di.lookup_type(+ = SCHOOL_ID與select *from stude nt_infosi-學(xué) 生信息表,descripti on_info di,descripti on_info di2where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand si.school_id =di.lo okup_code(+)and di.lookup_type(+ = SCHOOL_ID以student_info作為基礎(chǔ)表

4、，你會(huì)發(fā)現(xiàn)運(yùn)行的速度會(huì)有很大的差距當(dāng)基礎(chǔ)表放在后面，這樣的執(zhí)行速度會(huì)明顯快很多。where執(zhí)行 順序where執(zhí)行會(huì)從至下往上執(zhí)行select *from stude nt_info si -學(xué)生信息表where si.school_id=10 -學(xué)院 IDand si.system_id=100-系 ID擺放where子句時(shí)，把能過(guò)濾大量數(shù)據(jù)的條件放在最下邊is null 和 is not null當(dāng)要過(guò)濾列為空數(shù)據(jù)或不為空的數(shù)據(jù)時(shí)使用select *from stude nt_info si -學(xué)生信息表where si.school_id is null當(dāng)前列中的 null為少數(shù)時(shí)用 i

5、s not null,否則 is null)4使用表別名當(dāng)查詢時(shí)出現(xiàn)多個(gè)表時(shí)查詢時(shí)加上別名，避免出現(xiàn)減少解析的時(shí)間字段歧義引起的語(yǔ)法錯(cuò)誤。where執(zhí)行速度比hav ing快盡可能的使用where代替having select from stude nt_info si group by si.stude nt_id hav ing si.system_id!=100and si.school_id!=10(select from student_info siwehre si.system_id!=100and si.school_id!=10group by si.stude nt_id)

6、*號(hào)引起的執(zhí)行效率盡量減少使用select *來(lái)進(jìn)行查詢，當(dāng)你查詢使用*, 數(shù)據(jù)庫(kù)會(huì)進(jìn)行解析并將*轉(zhuǎn)換為全部列。二、替代優(yōu)化1、用=替代select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id = 10與select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id9執(zhí)行時(shí) =會(huì)比執(zhí)行得要快 2、用UNION替換OR (適用于索引列) select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=

7、1Ounionselect ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=2上面語(yǔ)句可有效避免全表查詢select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=1Oor ui.stude nt_id=2如果堅(jiān)持要用OR,可以把返回記錄最少的索引列寫(xiě)在最前面3、用in代替orselect ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=1Oor ui.stude nt_id=20or ui.st

8、ude nt_id=30改成select ui.user_namefrom user_i nfo ui-員工信息表where ui.student_id in (10,20,30)執(zhí)行會(huì)更有效率4、Union All 與 UnionUnion All重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄如果兩個(gè)并集中數(shù)據(jù)都不一樣那么使用Union All與Un ion是沒(méi)有區(qū)別的,select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=10union Allselect ui.user_namefrom user_i nfo ui-員工信息表wh

9、ere ui.stude nt_id=2與select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=10unionselect ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id=2但Un io n All會(huì)比Union要執(zhí)行得快5、分離表和索引總是將你的表和索引建立在另外的表空間內(nèi) 決不要將這些對(duì)象存放到SYSTEM表空間里三、一些優(yōu)化技巧1、計(jì)算表的記錄數(shù)時(shí)select coun t(si.stude nt_id)from Student_info si(st

10、udent_id為索引)與select count(*) fromStude nt_info si執(zhí)行時(shí)上面的語(yǔ)句明顯會(huì)比下面沒(méi)有用索引統(tǒng)計(jì)的語(yǔ)句要快2使用函數(shù)提高SQL執(zhí)行速度當(dāng)出現(xiàn)復(fù)雜的查詢sql語(yǔ)名，可以考慮使用函數(shù)來(lái)提高速度 查詢學(xué)生信息并查詢學(xué)生(李明)個(gè)人信息與的數(shù)學(xué)成績(jī)排名 如select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_idorder by result_math) order_ numfrom de

11、scription_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.descripti on =李明而且我們將上面order_num排名寫(xiě)成一個(gè)fuction時(shí)create or replace package body order_nu m_pkg isfun cti on order_nu m(p_stude nt_id nu mber) retur n_nu rrber is v_retur n_num ber nu

12、m ber;begi nselect res.order_num -排名into v_retur n_nu nrberfrom result reswhere res.stude nt_id =di.stude nt_idorder by result_math;return v_retur n_nu ntoer;excepti onwhe n others the nn ull;return n ull;end;end order_ nu m_pkg;執(zhí)行select di.descripti on stude nt_nane,order_nu m_pkg.order_nu m(di.stu

13、de nt_id) order_ numfrom description_info di,stude nt_i nfo si -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.descripti on =李明執(zhí)行查詢時(shí)的速度也會(huì)有所提高3減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)執(zhí)行次數(shù)的減少(當(dāng)要查詢出student_id=1OO的學(xué)生和student_id=20的學(xué)生信息時(shí)) select address_idfrom stude nt_info si -學(xué)生信息表where si.stud

14、e nt_id=1OO與select address_idfrom stude nt_info si -學(xué)生信息表where si.stude nt_id=20都進(jìn)行查詢這樣的效率是很低的而進(jìn)行(select si.address_id,si2.address_idfrom stude nt_info si -學(xué)生信息表,stude nt_i nfo si2where si.stude nt_id=1OOand si2.stude nt_id=20與select decode(si.stude nt_id,1OO,address_id),decode(si.stude nt_id,20,add

15、ress_id)from stude nt_info si)執(zhí)行速度是提高了，但可讀性反而差了.所以這種寫(xiě)法個(gè)人并不太推薦4、用 Exists(Not Exists)代替 In(Not In)在執(zhí)行當(dāng)中使用Exists或者Not Exists可以高效的進(jìn)行查詢5、Exists取代Distinct取唯一值的取出關(guān)聯(lián)表部門(mén)對(duì)員工時(shí)這時(shí)取出員工部門(mén)時(shí)出現(xiàn)多條.select disti net di.dept _namefrom departme nts_i nfo di -部門(mén)表,user_ info ui -員工信息表where ui.dept _no =di.dept_ no可以修改成selec

16、t di.dept _namefrom departme nts_i nfo di -部門(mén)表where exists (select Xfrom user_ info ui -員工信息表where di.dept _no = ui.dept _no)6、用表連接代替Exists通過(guò)表的關(guān)聯(lián)來(lái)代替exists會(huì)使執(zhí)行更有效率select ui.user_namefrom user_i nfo ui-員工信息表where exists (select x from departme nts_i nfo di-部門(mén)表where di.dept _no = ui.dept _noand ui.dept

17、_cat =IT);執(zhí)行是比較快，但還可以使用表的連接取得更快的查詢效率select ui.user_namefrom departme nts_i nfo di,user_ infoui -員工信息表where ui.dept _no =di.dept _noand ui.departme nt_type_code = IT代碼是經(jīng)測(cè)試并進(jìn)行優(yōu)化所寫(xiě)以上只例子,具體使用還是要針對(duì)各個(gè)不同的具體的業(yè)務(wù)使用用Exists(Not Exists代替In (Not In)四、索引篇1、運(yùn)算導(dǎo)致的索引失效select di.descripti on stude nt_nane,(select res.

18、order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_idorder by result_math) order_ numfrom description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand si.stude nt_id+)=1OO/ *stude nt_id 索引將失效*/2、類(lèi)型轉(zhuǎn)換導(dǎo)致的索引失效select di.descripti on

19、 stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_id order by result_math) order_ num from description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+) and di.lookup_type(+ = STUDENT_ID and di.student_id=1OOstudent_id為number類(lèi)型的索引，當(dāng)執(zhí)行下列語(yǔ)

20、句 oracle會(huì)自動(dòng)轉(zhuǎn)換成select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_id order by result_math) order_ num from description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+) and di.lookup_type(+ = STUDENT_ID and di.stude n

21、t_id=o_ nu mber(100) 所幸，只是解析并轉(zhuǎn)換類(lèi)型并沒(méi)有導(dǎo)到失效， 但要是寫(xiě)成下面，將會(huì)使用其失效 select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_id order by result_math) order_ num from description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+) and

22、di.lookup_type(+ = STUDENT_ID and to_char(di.student_id)=1OO3、在索引列上進(jìn)行計(jì)算引起的問(wèn)題select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_id order by result_math) order_num from description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo

23、okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.stude nt_id-2=10在索引列中進(jìn)行運(yùn)算，將會(huì)不使用索引而使用全表掃描 而將select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_idorder by result_math) order_ numfrom description_info di,stude nt_i nfosi -學(xué)生信息表where si.s

24、tude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.stude nt_id=L0+2將會(huì)得到高效的運(yùn)行速度4、Is not null引起的問(wèn)題(student id為索引)不要把存在空值的列做為索引否則無(wú)法使用索引select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id is not n ull-索弓 I失效select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id =

25、-1-索引有效5、Order bv導(dǎo)致索弓丨失效(student id為索引)select ui.user_namefrom user_i nfo ui-員工信息表group by ui.stude nt_id而使用select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id = -1將使其有效，在order by中只存在兩種條件下可以使用索引(ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序 ORDER BY中所有的列必須定義為非空)6、自動(dòng)選擇索引 如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性

26、索引，而其他是非唯一性. 在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引.select ui.user_namefrom user_i nfo ui-員工信息表where ui.stude nt_id!=0在Where中使用!=將會(huì)把索引失效& %導(dǎo)致的索引失效select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_idorder by result_math) order_ numfrom descript

27、ion_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.look_code Like %12/ *look_code為索引,索引將失效*/而select di.descripti on stude nt_nane,(select res.order_ nu m-排名from result reswhere res.stude nt_id =di.stude nt_idorder by result_math) order_

28、 numfrom description_info di,stude nt_i nfosi -學(xué)生信息表where si.stude nt_id =di.lo okup_code(+)and di.lookup_type(+ = STUDENT_IDand di.look_code Like 12%/嗦引有效*/以上只例子，具體還是要針對(duì)各個(gè)不同的具體的業(yè)務(wù)使用五、oracle中的not Exists與Not in的性能巨大差異Not Exists與Not in的作用同樣是排除數(shù)據(jù)在oracle中使用not in并不象mysql中的執(zhí)行那么 快,如(select jt1.doc_ num-單據(jù)號(hào)碼,oalc.descripti on schoo l_name -學(xué)校名稱,oalc2.descripti on system_ name -系名稱,oalc.descripti on class _name -班級(jí)名稱from java_table1jt1,java_table_descripti on oalc,java_table_descripti on oalc2,java_table_descripti on oalc3where oalc.lookup_type(+ = JA