我們在做數據庫程序的時候，程序內會寫大量的SQL語句，我們為什麼要優化SQL語句呢.
l SQL語句是對數據庫進行操作的惟一途徑，對數據庫系統的性能起著決定性的作用。
l SQL語句消耗了70%至90%的數據庫資源。
l SQL語句獨立於程序設計邏輯，對SQL語句進行優化不會影響程序邏輯。
l SQL語句有不同的寫法，在性能上的差異非常大。
l SQL語句易學，但難精通
大家在書寫SQL 語句的時候，根據個人經驗的不同，不同的寫法很多，但是可能得到的結果是一樣的，但是執行的效率是不同的，下面就把一些我們經常遇到的情況羅列出來做了一個簡單的比較，以供大家參考。
1. 計算記錄條數
和一般的觀點相反，count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的。例如 COUNT(EMPNO) 最快
2. 不要在where 子句裡面對列進行函數以及運算操作
SELECT ename, hiredate, sal
FROM emp
WHERE SUBSTR(ename,1,3) = 'SCO';
（Oracle 9I以後有了函數索引，如果建立了函數索引，這樣書寫比Like要快）
SELECT ename, hiredate, sal
FROM emp
WHERE ename LIKE 'SCO%';
VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'
SELECT ename, hiredate, sal
FROM emp
WHERE ename = NVL (:name, ename);
VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'
SELECT ename, hiredate, sal
FROM emp
WHERE ename LIKE NVL (:name, '%');
SELECT ename, hiredate, sal
FROM emp
WHERE TRUNC (hiredate) = TRUNC (SYSDATE);
SELECT ename, hiredate, sal
FROM emp
WHERE hiredate BETWEEN TRUNC (SYSDATE)
AND TRUNC (SYSDATE) + .99999;
SELECT ename, hiredate, sal
FROM emp
WHERE ename || empno = 'SCOTT7788';
SELECT ename, hiredate, sal
FROM emp
WHERE ename = 'SCOTT
AND empno = 7788;
SELECT ename, hiredate, sal
FROM emp
WHERE sal + 3000 < 5000;
SELECT ename, hiredate, sal
FROM emp
WHERE sal < 2000;
SELECT ename, hiredate, sal
FROM emp
WHERE sal != 0;
SELECT ename, hiredate, sal
FROM emp
WHERE sal > 0;
3. 用Where子句替換HAVING子句
避免使用HAVING子句，HAVING 只會在檢索出所有記錄之後才對結果集進行過濾。 這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷。
SELECT d.dname, AVG (e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING dname != 'RESEAECH'
AND dname != 'SALES';
SELECT d.dname, AVG (e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND dname != 'RESEAECH'
AND dname != 'SALES'
GROUP BY d.dname;
4. 減少對表的查詢
在含有子查詢的SQL語句中，要特別注意減少對表的查詢。
SELECT ename
FROM emp
WHERE sal = (SELECT MAX (sal)
FROM lookup)
AND comm = (SELECT MAX (comm)
FROM lookup);
SELECT ename
FROM emp
WHERE (sal,comm) = (SELECT MAX (sal),
MAX(comm)
FROM lookup);
5. .通過內部函數提高SQL效率。
SELECT COUNT(*)
FROM emp
WHERE status = 'Y'
AND ename LIKE 'SMITH%';
----------
SELECT COUNT(*)
FROM emp
WHERE status = 'N'
AND ename LIKE 'SMITH%';
SELECT COUNT(DECODE(status, 'Y', 'X', NULL)) Y_count,
COUNT(DECODE(status, 'N', 'X', NULL)) N_count
FROM emp
WHERE ename LIKE 'SMITH%';
6. 使用表的別名(Alias)
當在SQL語句中連接多個表時，請使用表的別名並把別名前綴於每個Column上。這樣一來，就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。
7. 用EXISTS替代IN
在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。
SELECT *
FROM dept
WHERE deptno IN (SELECT deptno FROM EMP);
SELECT *
FROM dept
WHERE EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);
8. 用NOT EXISTS替代NOT IN
在子查詢中，NOT IN子句將執行一個內部的排序和合並。無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷)。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。
SELECT *
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM EMP);
SELECT dept.*
FROM dept, emp
WHERE dept.deptno = emp.deptno (+)
AND emp.ROWID IS NULL;
SELECT *
FROM dept
WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);
9. 用表連接替換EXISTS,IN
通常來說 ， 采用表連接的方式比EXISTS,IN更有效率
SELECT ename
FROM emp E
WHERE EXISTS (SELECT 'X'
FROM dept
WHERE deptno = E.deptno
AND dname = 'ACCOUNTING');
SELECT ename
FROM emp E
WHERE deptno IN (SELECT deptno
FROM dept
WHERE deptno = E.deptno
AND dname = 'ACCOUNTING');
SELECT ename
FROM dept D, emp E
WHERE E.deptno = D.deptno
AND D.dname = 'ACCOUNTING';
10. 用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換
一般寫法
修改
SELECT DISTINCT d.deptno, d.dname FROM dept D, emp E WHERE D.deptno = E.deptno;
SELECT d.deptno, d.dname FROM dept D WHERE EXISTS (SELECT 'X' FROM emp E
WHERE E.deptno = D.deptno);
SQL編寫以外其它方面的注意事項1. 選用適合的Oracle優化器
Oracle的優化器共有3種：A、RULE (基於規則) b、COST (基於成本) c、CHOOSE (選擇性)
設置缺省的優化器，可以通過對init.ora文件中OPTIMIZER_MODE參數的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。 你當然也在SQL句級或是會話(session)級對其進行覆蓋。
為了使用基於成本的優化器(CBO， Cost-Based Optimizer) ， 你必須經常運行analyze 命令，以增加數據庫中的對象統計信息(object statistics)的准確性。
如果數據庫的優化器模式設置為選擇性(CHOOSE)，那麼實際的優化器模式將和是否運行過analyze命令有關。如果table已經被analyze過，優化器模式將自動成為CBO ， 反之，數據庫將采用RULE形式的優化器。
在缺省情況下，Oracle采用CHOOSE優化器，為了避免那些不必要的全表掃描(full table scan) ， 你必須盡量避免使用CHOOSE優化器，而直接采用基於規則或者基於成本的優化器。
2.訪問Table的方式
Oracle 采用兩種訪問表中記錄的方式：
A、全表掃描
全表掃描就是順序地訪問表中每條記錄。Oracle采用一次讀入多個數據塊(database block)的方式優化全表掃描。
B、通過ROWID訪問表
你可以采用基於ROWID的訪問方式情況，提高訪問表的效率， ROWID包含了表中記錄的物理位置信息。Oracle采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系。通常索引提供了快速訪問ROWID的方法，因此那些基於索引列的查詢就可以得到性能上的提高。
3.共享SQL語句
為了不重復解析相同的SQL語句，在第一次解析之後，Oracle將SQL語句存放在內存中。這塊位於系統全局區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享。因此，當你執行一個SQL語句(有時被稱為一個游標)時，如果它和之前的執行過的語句完全相同，ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑。Oracle的這個功能大大地提高了SQL的執行性能並節省了內存的使用。
可惜的是Oracle只對簡單的表提供高速緩沖(cache buffering)，這個功能並不適用於多表連接查詢。
數據庫管理員必須在init.ora中為這個區域設置合適的參數，當這個內存區域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。
當你向ORACLE提交一個SQL語句，ORACLE會首先在這塊內存中查找相同的語句。這裡需要注明的是，Oracle對兩者采取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同(包括空格，換行等)。
數據庫管理員必須在init.ora中為這個區域設置合適的參數，當這個內存區域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。
共享的語句必須滿足三個條件：
A、字符級的比較： 當前被執行的語句和共享池中的語句必須完全相同。
B、兩個語句所指的對象必須完全相同：
C、兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)。
4.選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎表。當Oracle處理多個表時，會運用排序及合並的方式連接它們。首先，掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序，然後掃描第二個表(FROM子句中最後第二個表)，最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並。
如果有3個以上的表連接查詢，那就需要選擇交叉表(intersection table)作為基礎表，交叉表是指那個被其他表所引用的表。
5.WHERE子句中的連接順序
Oracle采用自下而上的順序解析WHERE子句，根據這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。
6.SELECT子句中避免使用 ' * '
當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用 '*' 是一個方便的方法。不幸的是，這是一個非常低效的方法。實際上，Oracle在解析的過程中，會將'*' 依次轉換成所有的列名， 這個工作是通過查詢數據字典完成的， 這意味著將耗費更多的時間。
7.減少訪問數據庫的次數
當執行每條SQL語句時，Oracle在內部執行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數據塊等等。由此可見，減少訪問數據庫的次數，就能實際上減少Oracle的工作量。
8.使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表。
9.整合簡單，無關聯的數據庫訪問
如果你有幾個簡單的數據庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關系)
10.刪除重復記錄
11.用TRUNCATE替代DELETE
當刪除表中的記錄時，在通常情況下， 回滾段(rollback segments ) 用來存放可以被恢復的信息。 如果你沒有COMMIT事務，Oracle會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)。
而當運用TRUNCATE時，回滾段不再存放任何可被恢復的信息。當命令運行後，數據不能被恢復。因此很少的資源被調用，執行時間也會很短。
12.盡量多使用COMMIT
只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會因為COMMIT所釋放的資源而減少
COMMIT所釋放的資源：
A、回滾段上用於恢復數據的信息。
B、被程序語句獲得的鎖。
C、 redo log buffer 中的空間。
D、Oracle為管理上述3種資源中的內部花費。
