SQL常見性能優化方法

•硬解析

對送出的Sql完全重新從頭進行解析(當在Shared Pool中找不到時候将會進行此操作)，總共有以下5個執行步驟：

1：文法分析；

2：權限與對象檢查；

3：在共享池中檢查是否有完全相同的之前完全解析好的.如果存在，直接跳過4和5，運作Sql, 此時算soft parse；

4：選擇執行計劃；

5：産生執行計劃；

•軟解析

就如果是在Shared Pool中找到了與之完全相同的Sql解析好的結果後會跳過Hard Parse中的後面的兩個步驟，即：

1：選擇執行計劃；

2：産生執行計劃；

•SELECT子句中避免使用'*'

當你想在SELECT子句中列出所有的COLUMN時，使用動态SQL列引用'*'是一個友善的方法。不幸的是，這是一個非常低效的方法。實際上，ORACLE在解析的過程中，會将'*'依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味着将耗費更多的時間。

隻提取你所要使用的列，使用别名能夠加快解析速度。

高效：

SELECT E.EMPNO, E.ENAME, E.DEPTNO FROM EMP E;

低效：

SELECT * FROM EMP;

•使用表的别名(Alias)

當在SQL語句中連接配接多個表時， 請使用表的别名并把别名字首于每個COLUMN上。這樣一來，就可以減少解析的時間并減少那些由COLUMN歧義引起的文法錯誤。

COLUMN歧義指的是由于SQL中不同的表具有相同的COLUMN名，當SQL語句中出現這個COLUMN時，SQL解析器無法判斷這個COLUMN的歸屬。

•選擇最有效率的表名順序

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)将被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。

當ORACLE處理多個表時，會運用排序及合并的方式連接配接它們。首先，掃描第一個表(FROM子句中最後的那個表)并對記錄進行排序，然後掃描第二個表(FROM子句中最後第二個表)，最後将所有從第二個表中檢索出的記錄與第一個表中合适記錄進行合并。

如果有3個以上的表連接配接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表。

•WHERE子句中的連接配接順序

ORACLE采用自下而上(自右向左)的順序依次解析WHERE子句，根據這個原理，表之間的連接配接必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

例如：從表EMP中查找某部門中男職員

高效：

SELECT E.EMPNO, E.ENAME, E.DEPTNO FROM EMP E

WHERE E.SEX = 'MAN' AND E.DEPTNO = 10;

低效：

SELECT E.EMPNO, E.ENAME, E.DEPTNO FROM EMP E

WHERE E.DEPTNO = 10 AND E.SEX = 'MAN';

•用EXISTS替換DISTINCT

當送出一個包含一對多表資訊(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換，EXISTS使查詢更為迅速，因為RDBMS核心子產品将在子查詢的條件一旦滿足後，立刻傳回結果。

高效：

SELECT D.DEPT_NO, D.DEPT_NAME FROM DEPT D

WHERE EXISTS (

SELECT 'X' FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO

);

低效：

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO;

•用 >= 替代 >

高效:

SELECT E.EMPNO, E.ENAME FROM EMP E

WHERE E.DEPTNO >= 4

低效:

SELECT E.EMPNO, E.ENAME FROM EMP E

WHERE E.DEPTNO > 3

兩者的差別在于，前者DBMS将直接跳到第一個DEPTNO等于4的記錄，而後者将首先定位到DEPTNO=3的記錄，并且向後掃描到第一個DEPTNO大于3的記錄。

•IN、EXISTS的使用

如果IN後面的子查詢或者清單超過5個值，盡量不要使用IN子查詢；當IN後面的子查詢結果集較多，可以轉化為EXISTS子句或者把這個子查詢放在FROM後面作為虛表，在WHERE條件中使用'='條件關聯。

低效：

SELECT * FROM PRODUCT P WHERE PRODID IN

(SELECT P.PRODID FROM ITEM I WHERE I.ORGID=2000000001);

高效：

SELECT * FROM PRODUCT P WHERE EXISTS

(SELECT 'X' FROM ITEM I WHERE I.PRODID = P.PRODID AND I.ORGID = 2000000001);

•用NOT EXISTS替代NOT IN、用EXISTS替代IN

低效：

SELECT * FROM EMP E WHERE E.DEPTNO NOT IN (SELECT D.DEPTNO FROM DEPT D WHERE D DEPTNAME='ABC');

高效：

SELECT * FROM EMP E WHERE NOT EXISTS (SELECT 'X' FROM DEPT D WHERE D.DEPTNO = E.DEPTNO AND D.DEPTNAME = 'ABC');

•通配符的使用技巧

當通配符出現在LIKE後面字元串的首位時，索引将不會被使用，是以在已知某字元的情況下，LIKE查詢中應盡量不要把通配符寫在首位。

%代表不定長的字元，_代表定長的字元，如果在确定要通配的字元長度時，應該盡量使用_，而不是%。

•使用DECODE函數減少處理步驟

使用DECODE函數可以避免重複掃描相同記錄或重複連接配接相同的表。

低效：

SELECT COUNT(*) FROM EMP E WHERE E.DEPTNO = 10;

SELECT COUNT(*) FROM EMP E WHERE E.DEPTNO = 20;

高效：

SELECT COUNT(DECODE(E.DEPTNO, 10, E.EMPNO)), COUNT(DECODE(E.DEPTNO, 20, E.EMPNO)) FROM EMP E WHERE E.DEPTNO IN (10, 20);

•UNION ALL 和 UNION

根據需要，能用UNION ALL 的地方不要用 UNION，比如 SELECT USERCODE FROM A1 UNION SELECT USERCODE FROM B1， 如果兩個SELECT 的結果集沒有重複值，要使用UNION ALL，因為，無論是否有重複值，如果你不加ALL，資料庫都會再對兩個查詢結果再過濾一遍看看有沒有重複值（做一個GROUP BY操作），在ORACLE資料庫中DISTINCT 和GROUP BY都是比較費時的操作。

•ORDER BY、GROUP BY、DISTINCT的使用

當存在嵌套查詢時,内層不需要做ORDER BY處理，不要加 ORDER BY 子句(除非是為了得到ROWNUM的需要)；

如果沒有分組(标志是使用分組函數，如SUM()/AVG()等)處理，也沒有DISTINCT 要求，不要使用GROUP BY；

如果能夠通過WHERE條件過濾得到唯一要查詢的結果，禁止使用寬泛條件+DISTINCT來得到要查詢的結果；如果查詢結果在不使用DISTINCT時已經是唯一不重複結果，禁止再使用DISTINCT；

•盡量避免全表掃描

a.全表掃描

　 全表掃描就是順序地通路表中每條記錄。 ORACLE采用一次讀入多個資料塊(database block)的方式優化全表掃描。

b.通過ROWID通路表

你可以采用基于ROWID的通路方式情況，提高通路表的效率， ROWID包含了表中記錄的實體位置資訊……ORACLE采用索引(INDEX)實作了資料和存放資料的實體位置(ROWID)之間的聯系。通常索引提供了快速通路ROWID的方法，是以那些基于索引列的查詢就可以得到性能上的提高。

消除不必要的大表全表搜尋：不必要的全表搜尋導緻大量不必要的I/O，進而拖慢整個資料庫的性能。在一個有序的表中，如果查詢傳回少于40%的行，或者在一個無序的表中，傳回少于7%的行，那麼這個查詢都可以調整為使用一個索引來代替全表搜尋。對于不必要的全表搜尋來說，最常見的調優方法是增加索引。可以在表中加入标準的B樹索引，也可以加入bitmap和基于函數的索引。

•用WHERE子句替換HAVING子句

避免使用HAVING子句，HAVING隻會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷。

低效：

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != 'SYDNEY' AND REGION != 'PERTH'

高效：

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION != 'SYDNEY' AND REGION != 'PERTH' GROUP BY REGION

(HAVING 中的條件一般用于對一些集合函數的比較，如COUNT() 等等。 除此而外，一般的條件應該寫在WHERE子句中)

•用表連接配接替換EXISTS

通常來說，采用表連接配接的方式比EXISTS更有效率

低效：

SELECT ENAME FROM EMP E

WHERE EXISTS (

SELECT 'X' FROM DEPT

WHERE DEPTNO = E.DEPTNO AND DEPTCAT = 'A'

);

高效：

SELECT ENAME FROM DEPT D,EMP E

WHERE E.DEPTNO = D.DEPT_NO AND DEPTCAT = 'A';

(在RBO的情況下，前者的執行路徑包括FILTER，後者使用NESTED LOOP)

•用TRUNCATE替代DELETE

當删除表中的記錄時，在通常情況下，復原段(rollback segments)用來存放可以被恢複的資訊。如果你沒有COMMIT事務，ORACLE會将資料恢複到删除之前的狀态(準确地說是恢複到執行删除指令之前的狀況)而當運用TRUNCATE時，復原段不再存放任何可被恢複的資訊。當指令運作後，資料不能被恢複。是以很少的資源被調用，執行時間也會很短。

(TRUNCATE隻在删除全表适用，TRUNCATE是DDL不是DML)

•用索引提高效率

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

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

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

定期的重構索引是有必要的。

1)采用函數處理的字段不能利用索引，包括'||'，'+'等操作；

2)進行了顯式或隐式的運算的字段不能進行索引；

3)條件内包括了多個本表的字段運算時不能進行索引；

4)避免在索引列上使用IS NULL和IS NOT NULL；

5)避免在索引列上使用計算，如果索引列是函數的一部分，優化器将不使用索引而使用全表掃描；

6)避免在索引列上使用NOT，當ORACLE“遇到”NOT，他就會停止使用索引轉而執行全表掃描；

7)避免在索引列上使用!=操作，因為索引隻能告訴你什麼存在于表中, 而不能告訴你什麼不存在于表中；

8)盡量建單個索引；