plsql語句優(yōu)化方法

經(jīng)驗分享

ORACLESQL語句的優(yōu)化：

1. 選擇最有效率的表名順序：應(yīng)該選擇記錄條數(shù)最少的表作為基表！

因為Oracle解析器的處理順序是從右至左的.當(dāng)ORACLE處理多個表時, 會運用排序及合并的方式連接它們.應(yīng)該選擇記錄條數(shù)最少的表作為基表，排序連接后面的表中之后可以最大限度的過濾掉后面的表的不必要的的記錄，加快執(zhí)行時間，提高執(zhí)行效率.

1. WHERE子句中的連接順序。Oracle Parser采用至下而上的WHERE子句，因此，表之間的連接應(yīng)該寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句末尾.

1. 使用表別名(Alias): 當(dāng)關(guān)聯(lián)的表比較多時，通過使用表別名并且Column名前面加上別名前綴時，可以很有效的節(jié)省Oracle解析器解析表名的時間.

1. 要盡量避免使用HAVING子句，因為HAVING需要在檢索出所有的記錄之后才對結(jié)果集進行過濾，而這個處理還需要進行排序和總計的操作。可以通過使用WHERE子句來代替HAVING關(guān)鍵字。通過WHERE子句過濾現(xiàn)在查詢記錄的條數(shù)可以節(jié)約ORACLE很多的工作量和開銷.

1. > 及 < 操作符

一般不用調(diào)整，但是如果可以優(yōu)先寫成 >= 和 <=.

如：一個表有100萬記錄，一個數(shù)值型字段A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。

6.       行數(shù)總計時，COUNT(*),COUNT(1)的速率基本一樣,但通過索引檢索對索引的計數(shù)是最快的COUNT(EMPNO).

1. 減少訪問數(shù)據(jù)庫的次數(shù)。當(dāng)執(zhí)行每條SQL語句時，ORACLE內(nèi)部還會執(zhí)行很多的工作：解析SQL語句，綁定變量，讀取數(shù)據(jù)等，因此盡量較少對數(shù)據(jù)庫的訪問次數(shù)就能減少ORACLE的工作量.

例:

低效，需要訪問兩次數(shù)據(jù)庫：

SELECT*FROM oe_order_lines_all ool

WHERE ool.header_id=35718;

SELECT *FROM oe_order_lines_all ool

WHERE ool.header_id=38516;

高效！只訪問了一次數(shù)據(jù)庫：

SELECT *FROM oe_order_lines_all ool1,oe_order_lines_all ool2

WHERE ool1.header_id=38516

ANDool2.header_id=35718;

1. 使用DECODE函數(shù)可以避免重發(fā)掃描相同記錄或重復(fù)連接相同的表.

例:

低效:

SELECTCOUNT(*),SUM(emp.SAL)

FROMEMP

WHERE emp.deptno = 20

AND emp.ENAME LIKE'SMITH%';

SELECTCOUNT(*),SUM(emp.SAL)

FROMEMP

WHERE emp.deptno = 30

AND emp.ENAME LIKE'SMITH%';

--你可以用DECODE函數(shù)高效地得到相同結(jié)果

SELECTCOUNT(DECODE(emp.deptno,20,’X’,NULL)) D20_COUNT,

COUNT(DECODE(emp.deptno,30,’X’,NULL)) D30_COUNT,

SUM(DECODE(emp.deptno,20,SAL,NULL)) D20_SAL,

SUM(DECODE(emp.deptno,30,SAL,NULL)) D30_SAL

FROM EMP WHERE emp.ENAME LIKE'SMITH%';

1. 注意：要盡量減少訪問表的次數(shù)，在子查詢語句中比較常見.

例:

              低效:

           UPDATE EMP_C1 X

SET X.SAL= (SELECTMAX(Y.SAL) FROM EMP Y),

X.HIREDATE = (SELECTMAX(Y.HIREDATE) FROM EMP Y)

WHERE X.DEPTNO = 20;

高效:

UPDATE EMP_C1 X

SET(X.SAL, X.HIREDATE) = (SELECTMAX(Y.SAL) , MAX(Y.HIREDATE)FROM EMP Y) WHERE X.DEPTNO = 20;

1.  ‘EXISTS’ 和’NOTEXISTS’用法:在處理大量的數(shù)據(jù)時，SQL語句中使用‘EXISTS’來代替’IN’和’NOT EXISTS’來代替’NOT IN’ 處理的效率要大很多;

例:

低效:

SELECT*FROM Oe_Order_Lines_All ool

WHERE ool.ship_from_org_id=86

AND ool.header_id IN (SELECT ooh.header_id FROMoe_order_headers_all ooh);

高效:

SELECT*FROM Oe_Order_Lines_All ool

WHERE ool.ship_from_org_id=86

ANDEXISTS (SELECT1FROMoe_order_headers_all ooh WHERE ool.org_id = ooh.org_id AND ool.header_id = ooh.header_id);

注：當(dāng)所用的表之間相互可以連接時盡量使用表連接而不是’EXISTS’;因為表連接速度更快更高效;

1. 用索引來提高效率

 索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率. 實際上,ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.

除了那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會發(fā)現(xiàn), 在掃描小表時,使用索引同樣能提高效率.

雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4, 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢

注: 定期的重構(gòu)索引是有必要的.

1. 在’WHERE’子句中避免在索引列上使用計算。

同樣道理，避免在索引列上使用’IS NULL’或’IS NOT NULL’，NOT或者’<>’ OR ’!=’號：因為索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中，即索引不能索引空值.當(dāng)ORACLE遇到’NOT’就會停止執(zhí)行索引搜索掃描而進行全表掃描.

1. 帶通配符(%)的like語句.

因為通配符(%)在搜索次首出現(xiàn)，Orcale解析器無法跳過’%’使用索引，只能進行全表搜索，這樣搜索的效率就會低很多。’%’在搜索詞后，ORACLE就能使用搜索詞的索引，使用索引掃描數(shù)據(jù)，效率將會得到很大的提高。

 ‘%’在搜索詞后面進行的是索引搜索掃描：

 ‘%’在搜索詞前面進行的是索引搜索全表掃描：