Oracle資料庫優化的總結及優化方法

1、減少通路資料庫的次數。

2、不要讓資料庫做得太多。

（1）SELECT子句中避免使用' * '：ORACLE在解析的過程中，會将' * '依次轉換成所有的列名， 這個工作是通過查詢資料字典完成的，這意味着将耗費更多的時間。

（2）sql語句用大寫的：因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執行。另外，在java代碼中盡量少用連接配接符“＋”連接配接字元串！

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

（4） 用>=替代>：

高效: SELECT * FROM EMP WHERE DEPTNO >=4 

低效: SELECT * FROM EMP WHERE DEPTNO >3 

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

（5） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接.在這種情況下， 使用EXISTS(或NOT EXISTS)通常将提高查詢的效率。 在子查詢中，NOT IN子句将執行一個内部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表周遊)。 為了避免使用NOT IN ，我們可以把它改寫成外連接配接(Outer Joins)或NOT EXISTS。

例子：

（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（6）用EXISTS替換DISTINCT：例如:

低效: 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);

（7）盡量用UNION-ALL 替換UNION ( if possible)

當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并， 然後在輸出最終結果前進行排序。

低效：SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

（8） 避免在索引列上使用NOT：通常，避免在索引列上使用NOT， NOT會産生在和在索引列上使用函數相同的影響。 當Oracle”遇到”NOT，就會停止使用索引轉而執行全表掃描。

（9）用索引提高效率：索引是表的一個概念部分，用來提高檢索資料的效率，ORACLE使用了一個複雜的自平衡B-tree結構。 通常，通過索引查詢資料比全表掃描要快。當Oracle找出執行查詢和Update語句的最佳路徑時，Oracle優化器将使用索引。同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是，它提供了主鍵(primary key)的唯一性驗證。通常, 在大型表中使用索引特别有效。 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價。 索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味着每條記錄的INSERT , DELETE , UPDATE将為此多付出4 , 5 次的磁盤I/O 。因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。定期的重構索引是有必要的。

3.優化方法

(01)選擇最有效率的表名順序(筆試常考)

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，

FROM子句中寫在最後的表将被最先處理，

在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表放在最後，

如果有3個以上的表連接配接查詢,那就需要選擇那個被其他表所引用的表放在最後。

例如：查詢員工的編号，姓名，工資，工資等級，部門名

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname

from salgrade,dept,emp

where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

      1)如果三個表是完全無關系的話，将記錄和列名最少的表，寫在最後，然後依次類推

      2)如果三個表是有關系的話，将引用最多的表，放在最後，然後依次類推

(02)WHERE子句中的連接配接順序(筆試常考)

ORACLE采用自右而左的順序解析WHERE子句,根據這個原理,表之間的連接配接必須寫在其他WHERE條件之左,

那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的之右。

例如：查詢員工的編号，姓名，工資，部門名

select emp.empno,emp.ename,emp.sal,dept.dname

from emp,dept

where (emp.deptno = dept.deptno) and (emp.sal > 1500)

(03)SELECT子句中避免使用*号

ORACLE在解析的過程中,會将*依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味着将耗費更多的時間

select empno,ename from emp;

(04)使用DECODE函數來減少處理時間

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

(05)整合簡單，無關聯的資料庫通路；

(06)用TRUNCATE替代DELETE；

(07)盡量多使用COMMIT，

因為COMMIT會釋放復原點；

(08)用WHERE子句替換HAVING子句

WHERE先執行，HAVING後執行

(09)多使用内部函數提高SQL效率

(10)使用表的别名

salgrade s

(11)使用列的别名

ename e

(12)用索引提高效率

在查詢中，善用索引

(13)字元串型，能用=号，不用like

因為=号表示精确比較，like表示模糊比較

(14)SQL語句用大寫的

因為Oracle伺服器總是先将小寫字母轉成大寫後，才執行

在eclipse中，先寫小寫字母，再通過ctrl+shift+X轉大寫;ctrl+shift+Y轉小寫

(15)避免在索引列上使用NOT

因為Oracle伺服器遇到NOT後，他就會停止目前的工作，轉而執行全表掃描

(16)避免在索引列上使用計算

WHERE子句中，如果索引列是函數的一部分，優化器将不使用索引而使用全表掃描，這樣會變得變慢

例如，SAL列上有索引，

低效：

SELECT EMPNO,ENAME

FROM EMP

WHERE SAL*12 > 24000;

高效：

SELECT EMPNO,ENAME

FROM EMP

WHERE SAL > 24000/12;

(17)用 >= 替代 >

低效：

SELECT * FROM EMP WHERE DEPTNO > 3

首先定位到DEPTNO=3的記錄并且掃描到第一個DEPT大于3的記錄

高效：

SELECT * FROM EMP WHERE DEPTNO >= 4

直接跳到第一個DEPT等于4的記錄

(18)用IN替代OR

select * from emp where sal = 1500 or sal = 3000 or sal = 800;

select * from emp where sal in (1500,3000,800);

(19)總是使用索引的第一個列

如果索引是建立在多個列上，隻有在它的第一個列被WHERE子句引用時，優化器才會選擇使用該索引

當隻引用索引的第二個列時，不引用索引的第一個列時，優化器使用了全表掃描而忽略了索引

create index emp_sal_job_idex

on emp(sal,job);

----------------------------------

select *

from emp

where job != 'SALES';

(20)避免改變索引列的類型，顯示比隐式更安全

當字元和數值比較時，ORACLE會優先轉換數值類型到字元類型

select 123 || '123' from dual;