31. 強(qiáng)制索引失效
如果兩個或以上索引具有相同的等級,你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(通過它,檢索出的記錄數(shù)量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引將失效*/
這是一種相當(dāng)直接的提高查詢效率的辦法. 但是你必須謹(jǐn)慎考慮這種策略,一般來說,只有在你希望單獨(dú)優(yōu)化幾個SQL時才能采用它.
這里有一個例子關(guān)于何時采用這種策略,
假設(shè)在EMP表的EMP_TYPE列上有一個非性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’;
優(yōu)化器會注意到EMP_TYPE上的索引并使用它. 這是目前的選擇. 如果,一段時間以后, 另一個非性建立在EMP_CLASS上,優(yōu)化器必須對兩個索引進(jìn)行選擇,在通常情況下,優(yōu)化器將使用兩個索引并在他們的結(jié)果集合上執(zhí)行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近于性而另一個索引（EMP_CLASS）上有幾千個重復(fù)的值. 排序及合并就會成為一種不必要的負(fù)擔(dān). 在這種情況下,你希望使優(yōu)化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS||’’ = ‘X’;
32. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
譯者按:
這是一個非常實(shí)用的規(guī)則，請務(wù)必牢記
33. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個性索引，而其他是非性．
在這種情況下，ORACLE將使用性索引而完全忽略非性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這里，只有EMPNO上的索引是性的，所以EMPNO索引將用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
34. 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的
影響. 當(dāng)ORACLE”遇到”NOT,他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.
舉例:
低效: (這里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (這里,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是,在某些時候, ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應(yīng)的關(guān)系操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
譯者按:
在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執(zhí)行的.
我做了一些測試:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ’EMP’
2 1 INDEX (RANGE SCAN) OF ’EMPNO’ (UNIQUE)
如果兩個或以上索引具有相同的等級,你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(通過它,檢索出的記錄數(shù)量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引將失效*/
這是一種相當(dāng)直接的提高查詢效率的辦法. 但是你必須謹(jǐn)慎考慮這種策略,一般來說,只有在你希望單獨(dú)優(yōu)化幾個SQL時才能采用它.
這里有一個例子關(guān)于何時采用這種策略,
假設(shè)在EMP表的EMP_TYPE列上有一個非性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’;
優(yōu)化器會注意到EMP_TYPE上的索引并使用它. 這是目前的選擇. 如果,一段時間以后, 另一個非性建立在EMP_CLASS上,優(yōu)化器必須對兩個索引進(jìn)行選擇,在通常情況下,優(yōu)化器將使用兩個索引并在他們的結(jié)果集合上執(zhí)行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近于性而另一個索引（EMP_CLASS）上有幾千個重復(fù)的值. 排序及合并就會成為一種不必要的負(fù)擔(dān). 在這種情況下,你希望使優(yōu)化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS||’’ = ‘X’;
32. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
譯者按:
這是一個非常實(shí)用的規(guī)則，請務(wù)必牢記
33. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個性索引，而其他是非性．
在這種情況下，ORACLE將使用性索引而完全忽略非性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這里，只有EMPNO上的索引是性的，所以EMPNO索引將用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
34. 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的
影響. 當(dāng)ORACLE”遇到”NOT,他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.
舉例:
低效: (這里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (這里,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是,在某些時候, ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應(yīng)的關(guān)系操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
譯者按:
在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執(zhí)行的.
我做了一些測試:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF ’EMP’
2 1 INDEX (RANGE SCAN) OF ’EMPNO’ (UNIQUE)