一：不要讓Oracle做得太多

1.避免復雜的多表關聯(lián)

2.避免使用 ‘ * ‘

（1）當你想在SELECT子句中列出所有的COLUMN時,使用動態(tài) SQL列引用 ‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉 換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費更多的時間

（2）只提取你所要使用的列

（3）使用別名能夠加快解析速度

3.避免使用耗費資源的操作

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次 排序.

例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句 , GROUP BY會觸發(fā)嵌入排序(NESTED SORT) ; 這樣, 每個 查詢需要執(zhí)行一次排序, 然后在執(zhí)行UNION時, 又一個唯一 排序(SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入 排序結束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查 詢的效率.

通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以 用其他方式重寫.

4.用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)

5.用UNION-ALL 替換UNION ( if possible)

低效：

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’

二：給優(yōu)化器更明確的命令

1.自動選擇索引

如果表中有兩個以上（包括兩個）索引，其中有一個唯一性 索引，而其他是非唯一性． 在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯 一性索引． 舉例:

SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20?

這里，只有EMPNO上的索引是唯一性的，所以EMPNO索 引將用來檢索記錄

2.至少要包含組合索引的第一列

如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優(yōu)化器才會選擇使用該索引

3.避免在索引列上使用函數(shù)

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12

4.避免使用前置通配符

WHERE子句中, 如果索引列所對應的值的第一個字符由通 配符(WILDCARD)開始, 索引將不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO LIKE '%109204421'

在這種情況下，ORACLE將使用全表掃描

5.避免在索引列上使用NOT

通常，我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在 索引列上使用函數(shù)相同的影響. 當ORACLE”遇到”NOT,他就 會停止使用索引轉而執(zhí)行全表掃描.

低效: (這里,不使用索引)

SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

高效: (這里,使用了索引)

SELECT … FROM DEPT WHERE DEPT_CODE > 0;

6.避免在索引列上使用 IS NULL和IS NOT NULL

避免在索引中使用任何可以為空的列，ORACLE將無法使用該 索引 ．對于單列索引，如果列包含空值，索引中將不存在此記 錄. 對于復合索引，如果每個列都為空，索引中同樣不存在此 記錄.　如果至少有一個列不為空，則記錄存在于索引中．

如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記 錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同 A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為 空，ORACLE將認為整個鍵值為空而空不等于空. 因此你可以 插入1000條具有相同鍵值的記錄,當然它們都是空!

因為空值不存在于索引列中,所以WHERE子句中對索引列進行 空值比較將使ORACLE停用該索引.

任何在where子句中使用is null或is not null的語句優(yōu)化器是 不允許使用索引的。

7.避免出現(xiàn)索引列自動轉換

當比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進行簡單 的類型轉換.

假設EMP_TYPE是一個字符類型的索引列.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO = 109204421

這個語句被ORACLE轉換為:

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE TO_NUMBER(USER_NO) = 109204421

因為內部發(fā)生的類型轉換, 這個索引將不會被用到!

8.在查詢時盡量少用格式轉換

三：減少訪問次數(shù)

1.減少訪問數(shù)據(jù)庫的次數(shù)

當執(zhí)行每條SQL語句時, ORACLE在內部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實際上減少 ORACLE的工作量

2.使用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函數(shù)高效地得到相同結果

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%’

3.減少對表的查詢

在含有子查詢的SQL語句中,要特別注意減少對表的查詢.

低效

SELECT TAB_NAME FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND　DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

高效

SELECT TAB_NAME FROM TABLES

WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

四：細節(jié)上的影響

1.WHERE子句中的連接順序

ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個原 理, 當在WHERE子句中有多個表聯(lián)接時，WHERE子句中排 在最后的表應當是返回行數(shù)可能最少的表，有過濾條件的子 句應放在WHERE子句中的最后。

如：設從emp表查到的數(shù)據(jù)比較少或該表的過濾條件比較確定，能大大縮小查詢范圍，則將最具有選擇性部分放在WHERE子句中的最后：

select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

如果dept表返回的記錄數(shù)較多的話，上面的查詢語句會比下面的查詢語句響應快得多

select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

2.WHERE子句 ——函數(shù)、表達式使用

最好不要在WHERE子句中使用函或表達式，如果要使用的話，最好統(tǒng)一使用相同的表達式或函數(shù)，這樣便于以后使用合理的索引

3.Order by語句

ORDER BY語句決定了Oracle如何將返回的查詢結果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語句的非索引項或者有計算表達式都將降低查詢速度。

仔細檢查order by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order by子句中使用表達式。

4.聯(lián)接列

對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器 是不會使用索引的。

select * from employss 　 where 　 first_name||''||last_name ='Beill Cliton';

系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒有使用。 當采用下面這種SQL語句的編寫，Oracle系統(tǒng)就可以采用基 于last_name創(chuàng)建的索引。

select * from employee? where first_name ='Beill' and last_name ='Cliton';

5.帶通配符（%）的like語句

通配符（%）在搜尋詞首出現(xiàn)，Oracle系統(tǒng)不使用 last_name的索引。

select * from employee where last_name like '%cliton%';

在很多情況下可能無法避免這種情況，但是一定要心中有底 ，通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)在字 符串其他位置時，優(yōu)化器就能利用索引。

在下面的查詢中索引得到了使用：

select * from employee where last_name like 'c%';

6.用Where子句替換HAVING子句

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

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’

高效：

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION 順序 WHERE > GROUP > HAVING

7.用NOT EXISTS 替代 NOT IN

在子查詢中,NOT IN子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況 下,NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 使用NOT EXISTS 子句可以有效地利用索引。盡可能使用NOT EXISTS 來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度）， NOT EXISTS要比NOT IN查詢效率更高

語句1

SELECT dname, deptno FROM dept

WHERE deptno NOT IN (SELECT deptno FROM emp);

語句2

SELECT dname, deptno FROM dept

WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

2要比1的執(zhí)行性能好很多。 因為1中對emp進行了full table scan,這是很浪費時間的操作。而且1中沒有用到emp的index， 因為沒有where子句。而2中的語句對emp進行的是縮小范圍的查詢。

8.用索引提高效率

索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使 用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數(shù)據(jù)比全表 掃描要快. 當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結多個表時使用索引也可以 提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key) 的唯一性驗證。

通常, 在大型表中使用索引特別有效. 當然,你也會發(fā)現(xiàn), 在掃描小 表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提 高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要 定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也 會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為 此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理, 那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引 是有必要的。

9.避免在索引列上使用計算

WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不 使用索引而使用全表掃描．

低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

10.用>= 替代 >

如果DEPTNO上有一個索引。

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

低效: SELECT * FROM EMP WHERE DEPTNO >3

11.通過使用>=、<=等，避免使用NOT命令

select * from employee where salary <> 3000;

對這個查詢，可以改寫為不使用NOT：

select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。

12.外部聯(lián)接"+"的用法

外部聯(lián)接"+"按其在"="的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶"+"運算符的表中的一個行不直接匹配于帶"+"預算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯(lián)接"+"，可以替代效率十分低下的 not in 運算，大大提高運行速度。

例如，下面這條命令執(zhí)行起來很慢：

select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE');

利用外部聯(lián)接，改寫命令如下:

select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE';

這樣運行速度明顯提高.

13.盡量多使用COMMIT

事務是消耗資源的，大事務還容易引起死鎖

COMMIT所釋放的資源:

（1）回滾段上用于恢復數(shù)據(jù)的信息.

（2）被程序語句獲得的鎖

（3）redo log buffer 中的空間

（4）ORACLE為管理上述3種資源中的內部花費

14.用TRUNCATE替代DELETE

當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有 COMMIT事務,ORACLE會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的 信息.當命令運行后,數(shù)據(jù)不能被恢復.因此很少的資源被調用, 執(zhí)行時間也會很短

15.計算記錄條數(shù)

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

16.字符型字段的引號

比如有的表PHONE_NO字段是CHAR型,而且創(chuàng)建有索引， 但在WHERE條件中忘記了加引號，就不會用到索引。

WHERE PHONE_NO=‘13920202022’

WHERE PHONE_NO=13920202022