一：不要讓Oracle做得太多

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

2.避免使用 ‘ * ‘

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

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

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

3.避免使用耗費(fèi)資源的操作

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

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

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

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.自動(dòng)選擇索引

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

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

這里丁逝，只有EMPNO上的索引是唯一性的怔揩，所以EMPNO索 引將用來(lái)檢索記錄

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

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

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

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12

4.避免使用前置通配符

WHERE子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通 配符(WILDCARD)開(kāi)始, 索引將不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO LIKE '%109204421'

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

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

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

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

SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

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

SELECT … FROM DEPT WHERE DEPT_CODE > 0;

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

避免在索引中使用任何可以為空的列库正，ORACLE將無(wú)法使用該 索引 ．對(duì)于單列索引口锭，如果列包含空值朦前，索引中將不存在此記 錄. 對(duì)于復(fù)合索引，如果每個(gè)列都為空鹃操，索引中同樣不存在此 記錄.　如果至少有一個(gè)列不為空韭寸，則記錄存在于索引中．

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

因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行 空值比較將使ORACLE停用該索引.

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

7.避免出現(xiàn)索引列自動(dòng)轉(zhuǎn)換

當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單 的類型轉(zhuǎn)換.

假設(shè)EMP_TYPE是一個(gè)字符類型的索引列.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO = 109204421

這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為:

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE TO_NUMBER(USER_NO) = 109204421

因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到!

8.在查詢時(shí)盡量少用格式轉(zhuǎn)換

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

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

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

2.使用DECODE來(lái)減少處理時(shí)間

例如:

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ù)高效地得到相同結(jié)果

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.減少對(duì)表的查詢

在含有子查詢的SQL語(yǔ)句中,要特別注意減少對(duì)表的查詢.

低效

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)

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

1.WHERE子句中的連接順序

ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原 理, 當(dāng)在WHERE子句中有多個(gè)表聯(lián)接時(shí)椰拒，WHERE子句中排 在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表晶渠，有過(guò)濾條件的子 句應(yīng)放在WHERE子句中的最后凰荚。

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

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

如果dept表返回的記錄數(shù)較多的話便瑟，上面的查詢語(yǔ)句會(huì)比下面的查詢語(yǔ)句響應(yīng)快得多

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

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

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

3.Order by語(yǔ)句

ORDER BY語(yǔ)句決定了Oracle如何將返回的查詢結(jié)果排序爽彤。Order by語(yǔ)句對(duì)要排序的列沒(méi)有什么特別的限制养盗，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語(yǔ)句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度适篙。

仔細(xì)檢查order by語(yǔ)句以找出非索引項(xiàng)或者表達(dá)式往核，它們會(huì)降低性能。解決這個(gè)問(wèn)題的辦法就是重寫order by語(yǔ)句以使用索引嚷节，也可以為所使用的列建立另外一個(gè)索引聂儒，同時(shí)應(yīng)絕對(duì)避免在order by子句中使用表達(dá)式。

4.聯(lián)接列

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

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

系統(tǒng)優(yōu)化器對(duì)基于last_name創(chuàng)建的索引沒(méi)有使用效斑。 當(dāng)采用下面這種SQL語(yǔ)句的編寫非春，Oracle系統(tǒng)就可以采用基 于last_name創(chuàng)建的索引。

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

5.帶通配符（%）的like語(yǔ)句

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

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

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

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

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

6.用Where子句替換HAVING子句

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

低效:

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í)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況 下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 使用NOT EXISTS 子句可以有效地利用索引业踏。盡可能使用NOT EXISTS 來(lái)代替NOT IN禽炬，盡管二者都使用了NOT（不能使用索引而降低速度）， NOT EXISTS要比NOT IN查詢效率更高

語(yǔ)句1

SELECT dname, deptno FROM dept

WHERE deptno NOT IN (SELECT deptno FROM emp);

語(yǔ)句2

SELECT dname, deptno FROM dept

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

2要比1的執(zhí)行性能好很多勤家。 因?yàn)?中對(duì)emp進(jìn)行了full table scan,這是很浪費(fèi)時(shí)間的操作腹尖。而且1中沒(méi)有用到emp的index， 因?yàn)闆](méi)有where子句。而2中的語(yǔ)句對(duì)emp進(jìn)行的是縮小范圍的查詢热幔。

8.用索引提高效率

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

通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小 表時(shí),使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提 高,但是我們也必須注意到它的代價(jià). 索引需要空間來(lái)存儲(chǔ),也需要 定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也 會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為 此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理, 那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.绎巨。定期的重構(gòu)索引 是有必要的近尚。

9.避免在索引列上使用計(jì)算

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

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

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

10.用>= 替代 >

如果DEPTNO上有一個(gè)索引场勤。

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

低效: SELECT * FROM EMP WHERE DEPTNO >3

11.通過(guò)使用>=戈锻、<=等，避免使用NOT命令

select * from employee where salary <> 3000;

對(duì)這個(gè)查詢和媳，可以改寫為不使用NOT：

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

雖然這兩種查詢的結(jié)果一樣格遭，但是第二種查詢方案會(huì)比第一種查詢方案更快些。第二種查詢?cè)试SOracle對(duì)salary列使用索引留瞳，而第一種查詢則不能使用索引拒迅。

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

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

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

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

這樣運(yùn)行速度明顯提高.

13.盡量多使用COMMIT

事務(wù)是消耗資源的开瞭，大事務(wù)還容易引起死鎖

COMMIT所釋放的資源:

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

（2）被程序語(yǔ)句獲得的鎖

（3）redo log buffer 中的空間

（4）ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)

14.用TRUNCATE替代DELETE

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

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

和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可 以通過(guò)索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如 COUNT(EMPNO)

16.字符型字段的引號(hào)

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

WHERE PHONE_NO=‘13920202022’

WHERE PHONE_NO=13920202022