ORACLE SQL語(yǔ)句優(yōu)化

1、這兩天閑著沒(méi)事情，項(xiàng)目也快上線了，就把以前在項(xiàng)目里寫的SQL語(yǔ)句都看了一遍。參照往年優(yōu)化SQL的經(jīng)驗(yàn)和網(wǎng)上對(duì)SQL語(yǔ)句優(yōu)化的方法總結(jié)，加上oracle這么強(qiáng)大的SQL分析工具，把寫的不是很優(yōu)的SQL拿來(lái)優(yōu)化了。 總結(jié)如下：一、固定的SQL書寫習(xí)慣，相同的查詢盡量保持相同二、存儲(chǔ)過(guò)程的效率較高。三、編寫與其格式一致的語(yǔ)句，包括字母的大小寫、標(biāo)點(diǎn)符號(hào)、換行的位置等都要一致 。sql語(yǔ)句用大寫的；因?yàn)閛racle總是先解析sql語(yǔ)句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行 .四、寫查詢語(yǔ)句的時(shí)候，盡量不要出現(xiàn) ' * '，把需要的列查詢出來(lái)就可以。 1.盡量少用IN操作符

2、，基本上所有的IN操作符都可以用EXISTS代替、not in 用not EXISTS代替. 特別注意：IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況。不是說(shuō)所有的in或者not in 都可以用EXISTS和not EXISTS代替. 用IN來(lái)替換OR  這是一條簡(jiǎn)單易記的規(guī)則，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的 低效: SELECT. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 

3、高效 SELECT FROM LOCATION WHERE LOC_IN  IN (10,20,30); 用UNION替換OR (適用于索引列) 通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描.注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION  FROM LO

4、CATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫在最前面. 2.不用“<>”或者“!=”操作符。對(duì)不等于操作符的處理會(huì)造成全表掃描，可以

5、用“<” or “>”代替 。 例如： a<>0 改為 a>0 or a<0  a<> 改為 a> 3.Where子句中出現(xiàn)IS NULL或者IS NOT NULL時(shí)，Oracle會(huì)停止使用索引而執(zhí)行全表掃描。可以考慮在設(shè)計(jì)表時(shí)，對(duì)索引列設(shè)置為NOT NULL。這樣就可以用其他操作來(lái)取代判斷NULL的操作 用其它相同功能的操作運(yùn)算代替，如：       1、a is not null 改為 a>0 或a>等。&

6、#160;      2、不允許字段為空，而用一個(gè)缺省值代替空值，如業(yè)擴(kuò)申請(qǐng)中狀態(tài)字段不允許為空，缺省為申請(qǐng)。       3、 建立位圖索引（有分區(qū)的表不能建，位圖索引比較難控制，如字段值太多索引會(huì)使性能下降，多人更新操作會(huì)增加數(shù)據(jù)塊鎖的現(xiàn)象） 如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能。任何在WHERE子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的。 4、當(dāng)通配符“%”或者“_”作為查詢字符串的第一個(gè)字符時(shí)，索引不會(huì)被使用

7、. 例如： select from user u where like '%huang%' 應(yīng)該改為： select from user u where like 'huang%' 5、對(duì)于有連接的列“|”，最后一個(gè)連接列索引會(huì)無(wú)效。盡量避免連接，可以分開(kāi)連接或者使用不作用在列上的函數(shù)替代。 6、如果索引不是基于函數(shù)的，那么當(dāng)在Where子句中對(duì)索引列使用函數(shù)時(shí)，索引不再起作用。 例如：creadate有索引 查詢：select a.paicUmN

8、um from ps_NumBer a where to_char(a.creadate,'yyyy-MM-dd')>'2010-10-12'不使用索引 select a.paicUmNum from ps_NumBer a where a.creadate>to_date('2010-10-12','yyyy-MM-dd')使用索引 7、Where子句中避免在索引列上使用計(jì)算，否則將導(dǎo)致索引失效而進(jìn)行全表掃描。 例如：select s.numer from student s where

9、 s.age-20 = 50; 應(yīng)該改為：select s.numer from student s where s.age = 30或者(50-20); 8、對(duì)數(shù)據(jù)類型不同的列進(jìn)行比較時(shí)，會(huì)使索引失效。 例如：age為number類型  ，score 為varcher類型 select s.* from student s where s.age>score 在age和score 上的索引將會(huì)失效 避免改變索引列的類型.: 當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換. 假設(shè) EMP

10、NO是一個(gè)數(shù)值類型的索引列. SELECT   FROM EMP  WHERE  EMPNO = 123' 實(shí)際上,經(jīng)過(guò)ORACLE類型轉(zhuǎn)換, 語(yǔ)句轉(zhuǎn)化為: SELECT   FROM EMP  WHERE  EMPNO = TO_NUMBER(123') 幸運(yùn)的是,類型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引的用途沒(méi)有被改變.現(xiàn)在,假設(shè)EMP_TYPE是一個(gè)字符類型的索引列. SELECT   FROM EMP  WHERE EMP_TYPE = 123

11、60;這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為: SELECT   FROM EMP  WHERE TO_NUMBER(EMP_TYPE)=123 因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到! 為了避免ORACLE對(duì)你的SQL進(jìn)行隱式的類型轉(zhuǎn)換, 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來(lái).注意當(dāng)字符和數(shù)值比較時(shí), ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型 9、采用UNION ALL操作符替代UNION，因?yàn)閁NION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。UNION，在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量

12、大的話可能會(huì)導(dǎo)致用磁盤進(jìn)行排序。 10.總是使用索引的第一個(gè)列： 如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引. 這也是一條簡(jiǎn)單而重要的規(guī)則，當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引 11、在含有子查詢的SQL語(yǔ)句中,要特別注意減少對(duì)表的查詢.例子： SELECT  EMP_NO FROM EMP WHERE (GROUP,NAME) = ( SELECT COLUMN1,COLUMN2 FROM  TEST WHERE

13、60; TEST_ID = 604) 以上對(duì)sql的優(yōu)化建議，都是通過(guò)本人親身經(jīng)歷和點(diǎn)點(diǎn)滴滴總結(jié)出來(lái)的。如有不對(duì)的地方請(qǐng)指教。 順便收藏了一些好東西，拿出來(lái)分享下1、最高效的刪除重復(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); 2、參數(shù)用法很好<!- 查詢已存在的審批

14、實(shí)體生產(chǎn)架構(gòu)的流水號(hào) ->  resultClass="appEntityDeptDTO"> <!CDATA      select t.entity_id appTempletId        from oms_entity_dept t > <isNotEmpty prepend="where" property="userList"

15、;> <iterate property="userList" conjunction="OR"> (t.dept_no = #userList.deptNo# and t.entity_no = #userList.entityNo#) </iterate> </isNotEmpty> </select> 3、ibatis調(diào)用proc <!- 調(diào)用存儲(chǔ)過(guò)程，寫人員生產(chǎn)信息表 -> <parameterM

16、ap id="swapParameters" class="map">   </parameterMap> <procedure id="swapEmailAddresses" parameterMap="swapParameters"> call oms_sync_ps_c_oms_prd_emp_io(?, ?) </procedure> 另附索引的一些概念（轉(zhuǎn)載） 一、索引的

17、概念         索引就是加快檢索表中數(shù)據(jù)的方法。數(shù)據(jù)庫(kù)的索引類似于書籍的索引。在書籍中，索引允許用戶不必翻閱完整個(gè)書就能迅速地找到所需要的信息。在數(shù)據(jù)庫(kù)中，索引也允許數(shù)據(jù)庫(kù)程序迅速地找到表中的數(shù)據(jù)，而不必掃描整個(gè)數(shù)據(jù)庫(kù)。 二、索引的特點(diǎn)     1.索引可以加快數(shù)據(jù)庫(kù)的檢索速度     2.索引降低了數(shù)據(jù)庫(kù)插入、修改、刪除等維護(hù)任務(wù)的速度     3.索引創(chuàng)建在表上，不能創(chuàng)建在視圖

18、上     4.索引既可以直接創(chuàng)建，也可以間接創(chuàng)建     5.可以在優(yōu)化隱藏中，使用索引     6.使用查詢處理器執(zhí)行SQL語(yǔ)句，在一個(gè)表上，一次只能使用一個(gè)索引 三、索引的優(yōu)點(diǎn)     1.創(chuàng)建唯一性索引，保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性     2.大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因     3.加速表和表之間的連接，特別

19、是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。     4.在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。     5.通過(guò)使用索引，可以在查詢的過(guò)程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能。 四、索引的缺點(diǎn)     1.創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加     2.索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更

20、大     3.當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度 五、索引分類     1.直接創(chuàng)建索引和間接創(chuàng)建索引     直接創(chuàng)建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)     間接創(chuàng)建索引：定義主鍵約束或者唯一性鍵約束，可以間接創(chuàng)建索引     2.普通索引和唯一性索引 

21、    普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)     唯一性索引：保證在索引列中的全部數(shù)據(jù)是唯一的，對(duì)聚簇索引和非聚簇索引都可以使用     CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)     3.單個(gè)索引和復(fù)合索引     單個(gè)索引：即非復(fù)合索引&#

22、160;    復(fù)合索引：又叫組合索引，在索引建立語(yǔ)句中同時(shí)包含多個(gè)字段名，最多16個(gè)字段     CREATE INDEX name_index ON username(firstname,lastname)     4.聚簇索引和非聚簇索引(聚集索引，群集索引)    聚簇索引：物理索引，與基表的物理順序相同，數(shù)據(jù)值的順序總是按照順序排列     CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH     ALLOW_DUP_ROW(允許有重復(fù)記錄的聚簇索引)    非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) 六、索引的使用&#