ORACLE SQL效能最佳化系列 (十)

31.       強制索引失效

 

  

如果兩個或以上索引具有相同的等級,你可以強制命令ORACLE最佳化器使用其中的一個(通過它,檢索出的記錄數量少) .

 

舉例:

  

SELECT ENAME

FROM EMP

WHERE EMPNO = 7935 

AND DEPTNO + 0 = 10    /*DEPTNO上的索引將失效*/

AND EMP_TYPE || ‘’ = ‘A’  /*EMP_TYPE上的索引將失效*/

 

這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨最佳化幾個SQL時才能採用它.

 

這裡有一個例子關於何時採用這種策略,

 

假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引.

 

SELECT ENAME

FROM EMP

WHERE EMP_TYPE = ‘A’

AND EMP_CLASS = ‘X’;

 

最佳化器會注意到EMP_TYPE上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,最佳化器必須對兩個索引進行選擇,在通常情況下,最佳化器將使用兩個索引並在他們的結果集合上執行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重複的值. 排序及合并就會成為一種不必要的負擔. 在這種情況下,你希望使最佳化器屏蔽掉EMP_CLASS索引.

用下面的方案就可以解決問題.

SELECT ENAME

FROM EMP

WHERE EMP_TYPE = ‘A’

AND EMP_CLASS||’’ = ‘X’;

 

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

WHERE子句中，如果索引列是函數的一部分．最佳化器將不使用索引而使用全表掃描．

 

舉例:

 

低效：

SELECT …

FROM DEPT

WHERE SAL * 12 > 25000;

 

高效:

SELECT …

FROM DEPT

WHERE SAL > 25000/12;

 

譯者按:

這是一個非常實用的規則，請務必牢記

 

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會產生在和在索引列上使用函數相同的

影響. 當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.

   舉例:

 

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

 

   SELECT …

   FROM DEPT

   WHERE DEPT_CODE NOT = 0;

  

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

 

 SELECT …

   FROM DEPT

   WHERE DEPT_CODE > 0;

 

   需要注意的是,在某些時候, ORACLE最佳化器會自動將NOT轉化成相對應的關係操作符.

   NOT > to <=

   NOT >= to <

   NOT < to >=

   NOT <= to >

 

 

譯者按:

     在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執行的.

我做了一些測試:

    

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)    

 

SQL> select * from emp where 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)

 

      兩者的效率完全一樣，也許這符合作者關於” 在某些時候, ORACLE最佳化器會自動將NOT轉化成相對應的關係操作符” 的觀點．

     

35.       用>=替代>

 

如果DEPTNO上有一個索引,

 

高效:

 

   SELECT *

   FROM EMP

   WHERE DEPTNO >=4

  

   低效:

 

   SELECT *

   FROM EMP

   WHERE DEPTNO >3

 

      兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.