1. SELECT子句中避免使用 “*”
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用
‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將“*” 依次轉換成所有的列名,
這個工作是通過查詢數據字典完成的,
這意味著將耗費更多的時間.
2.使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’; SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函數高效地得到相同結果:
Sql代碼
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’;
類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.
3.刪除重復記錄
最高效的刪除重復記錄方法 (
因為使用了ROWID)
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
4.
用TRUNCATE替代DELETE
當刪除表中的記錄時，在通常情況下，回滾段(rollback segments )
用來存放可以被恢復的信息，如果你沒有COMMIT事務，ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)，而當運用TRUNCATE時,
回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.
5.計算記錄條數
和一般的觀點相反,
count(*) 比count(1)稍快 ，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的. 例如
COUNT(EMPNO)
6.用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
7.
用EXISTS替代IN
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,
使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
--低效 SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’) --高效: SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
8.用NOT
EXISTS替代NOT IN
在子查詢中，NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下，NOT IN都是最低效的
(因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例如:
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE
DEPT_CAT=’A’);
--為了提高效率改寫為: (方法一: 高效) SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’ -- (方法二: 最高效) SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
9.用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT.
一般可以考慮用EXIST替換
例如:
--低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO --高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); --EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果.
10.
用索引提高效率
索引是表的一個概念部分，用來提高檢索數據的效率，實際上ORACLE使用了一個復雜的自平衡B-tree結構，通常通過索引查詢數據比全表掃描要快，當ORACLE找出執行查詢和Update語句的最佳路徑時，
ORACLE優化器將使用索引， 同樣在聯結多個表時使用索引也可以提高效率，另一個使用索引的好處是，它提供了主鍵(primary
key)的唯一性驗證，除了那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現,
在掃描小表時,使用索引同樣能提高效率，雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.
索引需要空間來存儲，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改，這意味著每條記錄的INSERT , DELETE ,
UPDATE將為此多付出4 , 5
次的磁盤I/O，因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢
注：定期的重構索引是有必要的.
11.
避免在索引列上使用計算
WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引而使用全表掃描. 舉例：
--低效： SELECT …FROM DEPT WHERE SAL * 12 > 25000; --高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
12.
用>=替代>
--如果DEPTNO上有一個索引 --高效： SELECT * FROM EMP WHERE DEPTNO >=4 --低效： SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
