plsql語句優化方法

經驗分享

ORACLESQL語句的優化：

1. 選擇最有效率的表名順序：應該選擇記錄條數最少的表作為基表！

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

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

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

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

1. > 及 < 操作符

一般不用調整，但是如果可以優先寫成 >= 和 <=.

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

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

1. 減少訪問數據庫的次數。當執行每條SQL語句時，ORACLE內部還會執行很多的工作：解析SQL語句，綁定變量，讀取數據等，因此盡量較少對數據庫的訪問次數就能減少ORACLE的工作量.

例:

低效，需要訪問兩次數據庫：

SELECT*FROM oe_order_lines_all ool

WHERE ool.header_id=35718;

SELECT *FROM oe_order_lines_all ool

WHERE ool.header_id=38516;

高效！只訪問了一次數據庫：

SELECT *FROM oe_order_lines_all ool1,oe_order_lines_all ool2

WHERE ool1.header_id=38516

ANDool2.header_id=35718;

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

例:

低效:

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函數高效地得到相同結果

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. 注意：要盡量減少訪問表的次數，在子查詢語句中比較常見.

例:

              低效:

           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’用法:在處理大量的數據時，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);

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

1. 用索引來提高效率

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

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

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

注: 定期的重構索引是有必要的.

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

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

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

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

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

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