一、用索引提高效率
索引是表的一個(gè)概念部分,用來(lái)提高檢索數(shù)據(jù)的效率，通過(guò)索引查詢數(shù)據(jù)比全表掃描要快.
①避免在索引列上使用NOT 通常，
我們要避免在索引列上使用NOT, NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的影響. 當(dāng)ORACLE”遇到”NOT,他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.
②避免在索引列上使用計(jì)算．
WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
③ 用> 替代 >=
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
④用UNION替換OR (適用于索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫(xiě)在最前面.
⑤避免在索引列上使用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停用該索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
⑥總是使用索引的第一個(gè)列：
如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引. 這也是一條簡(jiǎn)單而重要的規(guī)則，當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引
⑦ 避免改變索引列的類型.:
當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換.
假設(shè) EMPNO是一個(gè)數(shù)值類型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'
實(shí)際上,經(jīng)過(guò)ORACLE類型轉(zhuǎn)換, 語(yǔ)句轉(zhuǎn)化為:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸運(yùn)的是,類型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引的用途沒(méi)有被改變.
現(xiàn)在,假設(shè)EMP_TYPE是一個(gè)字符類型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到! 為了避免ORACLE對(duì)你的SQL進(jìn)行隱式的類型轉(zhuǎn)換, 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來(lái). 注意當(dāng)字符和數(shù)值比較時(shí), ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型
二、選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理.
例如:
表 TAB1 16,384 條記錄
表 TAB2 1 條記錄
選擇TAB2作為基礎(chǔ)表 (最好的方法)
select count(*) from tab1,tab2 執(zhí)行時(shí)間0.96秒
選擇TAB2作為基礎(chǔ)表 (不佳的方法)
select count(*) from tab2,tab1 執(zhí)行時(shí)間26.09秒
如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
三、 WHERE子句中的連接順序
ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫(xiě)在其他WHERE條件之前, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾.
例如:
(低效,執(zhí)行時(shí)間156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執(zhí)行時(shí)間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';
四、 優(yōu)化GROUP BY:
提高GROUP BY 語(yǔ)句的效率, 可以通過(guò)將不需要的記錄在GROUP BY 之前過(guò)濾掉.下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP JOB
五、用Where子句替換HAVING子句：
避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷.
六、用EXISTS替換DISTINCT：
當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果. 例子：
(低效):
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);
七、用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫(xiě)成外連接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')