oracle 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種資源中的内部花費。

13.計算記錄條數

和一般的觀點相反，count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的。例如 COUNT(EMPNO)

14.用Where子句替換HAVING子句

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

15.減少對表的查詢

在含有子查詢的SQL語句中，要特别注意減少對表的查詢。

16.通過内部函數提高SQL效率。 17.使用表的别名(Alias)

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

18.用EXISTS替代IN

在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下，使用EXISTS(或NOT EXISTS)通常将提高查詢的效率。

19.用NOT EXISTS替代NOT IN

在子查詢中，NOT IN子句将執行一個内部的排序和合并。 無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表周遊)。為了避免使用NOT IN ，我們可以把它改寫成外連接配接(Outer Joins)或NOT EXISTS。

20.用表連接配接替換EXISTS

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

21.用EXISTS替換DISTINCT

當送出一個包含一對多表資訊(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換 WHERE Clause

Try to avoid operations on database objects referenced in the WHERE clause.

Given Query	Alternative
SELECT ename, hiredate, sal FROM emp WHERE SUBSTR(ename,1,3) = 'SCO';	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;

HAVING Clause

The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Given Query	Alternative
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;

Combined Subqueries

Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

Separate Subqueries	Combined Subqueries
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);

EXISTS, NOT IN, Table Joins

Consider the alternatives EXISTS, IN and table joins when doing multiple table joins. None of these are consistently faster; it depends on your data.

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';

DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.

Given Query	Alternative
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);

UNION ALL

Consider whether a UNION ALL will suffice in place of a UNION. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables are mutually exclusive (include no duplicate records), or you don't care if duplicates are returned, the UNION ALL is much more efficient.

UNION	UNION ALL
SELECT acct, balance FROM debit WHERE trandate = '31-DEC-95' UNION SELECT acct, balance FROM credit WHERE trandate = '31-DEC-95';	SELECT acct, balance FROM debit WHERE trandate = '31-DEC-95' UNION ALL SELECT acct, balance FROM credit WHERE trandate = '31-DEC-95';

DECODE

Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. Note, DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.

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%';

Anti Joins

An anti-join is used to return rows from a table that that are present in another table. It might be used for example between DEPT and EMP to return only those rows in DEPT that didn't join to anything in EMP;

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);

Full Outer Joins

Normally, an outer join of table A to table B would return every record in table A, and if it had a mate in table B, that would be returned as well. Every row in table A would be output, but some rows of table B might not appear in the result set. A full outer join would return ebery row in table A, as well as every row in table B. The syntax for a full outer join is new in Oracle 9i, but it is a syntactic convenience, it is possible to produce full outer joins sets using conventional SQL.

update emp set deptno = 9 where deptno = 10;

commit;

Conventional SQL

New Syntax

SELECT empno, ename, dept.deptno, dname FROM emp, dept WHERE emp.deptno(+) = dept.deptno UNION ALL SELECT empno, ename, emp.deptno, NULL FROM emp, dept WHERE emp.deptno = dept.deptno(+) AND dept.deptno IS NULL ORDER BY 1,2,3,4; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 9 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS

SELECT empno, ename, NVL(dept.deptno,emp.deptno) deptno, dname FROM emp FULL OUTER JOIN dept ON (emp.deptno = dept.deptno) ORDER BY 1,2,3,4; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 9 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS