oracle|效能|最佳化|語句 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 JoinsConsider 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'; |
DISTINCTAvoid 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 ALLConsider whether a UNION ALL will suffice in place of aUNION. The UNION clause forces all rows returned by each portion of theUNION 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'; |
DECODEConsider 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 |
