8. Index 失效

雖然 Index 有利於增加效能，但在某些情況之下，即使有建立 Index ，優化器也不會使用這些索引，進而影響查詢的效能，我們在撰寫SQL Statement的時候應該避免使用這些語法。

• 避免於 Index 列上使用 Not 或不等於條件式 :

當 where 條件使用 Not 或者是不等於條件式，即使有 Index 也不會使用，例如一個查詢如下:

SQL> select * from jobs where min_salary != 0

使用了 min_salary != 0 條件式，即使 min_salary 有 Index JOB_MIN_SAL_IX 也無法使用，造成 Full Table Scan :

此時將查詢修正如下，便能使用到 Index :

SQL> select * from jobs where min_salary > 0

• 避免於 like 語法的前綴字元使用符號 :

當 where 子句使用到 like 語法時，應注意前綴字元的使用，例如where name like '%Peter%' 此時無法使用 Index ，若語句為where name like 'Peter%' 便可使用 Index 。

例如一段SQL如下:

SQL> select employee_id,last_name||' '||first_name from employees where first_name like '%Smith%';

此時無法使用 Index :

將 SQL 修正如下便可以使用到 Index :

SQL> select employee_id,last_name||' '||first_name from employees where first_name like 'Smith%';

• 避免於 Index 列上進行運算 :

若於 Index 列上進行計算，則優化器無法使用 Index ，例如一段 SQL如下 :

SQL> select * from employees where salary * 12 > 1000000;

此時優化器無法使用 Index 造成 Full Table Scan :

將 SQL 修正後便可以使用 Index :

SQL> select * from employees where salary > 1000000/12

• 避免於 Index 列上使用 is null 或 is not null 語法 :

若於 Index 列上使用is null或是is not null語法，此時優化器便不會使用 Index ，例如一段 SQL 如下 :

SQL> select * from employees where department_id is null;

此時無法使用 Index :

假使一個欄位常常使用到 is null 或 is not null 語法時，便需重新審視是否需於此欄位建立 Index ，因為即使建立了也無法使用，徒增 Index 的維護成本而已。

• 避免於 Index 列上使用函數轉換 :

若於 Index 列上使用函數，則優化器無法使用 Index ，例如一段 SQL如下 :

SQL> select employee_id,last_name||' '||first_name  from employees  where substr(last_name,1,3) = 'Smi';

因為於 Index 列上使用了函數 substr ，所以優化器無法使用 Index :

如果有使用函數的需求，可以適當的建立 Function Index ，例如一段 SQL 如下 :

SQL> select * from employees where upper(last_name) = 'SMITH';

此時建立帶有 upper 函數的 Function Index 就可以使用到 :

在此需注意的是，建立 Function Index 所使用的函數，必須為系統內建的函數，無法將使用者自行定義的函數來建立 Index 。

• 使用正確的資料型態 :

若where條件使用的資料型態與 Table 定義的資料型態不符時，則優化器內部會將資料型態進行轉換，此時無法使用 Index ，假設 employees 的薪資欄位的資料型態設定為 varchar2 :

一段 SQL 查詢如下 :

SQL> select employee_id,last_name||' '||first_name from employees        where salary > 10000;

由於 salary 的資料型態為 varchar2 但 where 條件使用的是number ，此時優化器內部會進行 to_char 的轉換導致無法使用 Index :

將 SQL 修正以 10000 替代為字串 '10000' ，此時優化器就會使用 Index :

SQL> select employee_id,last_name||' '||first_name from employees        where salary > '10000';

• 使用 Index 的第一個列 :

當 Index 建立在多個欄位上時，只有在第一個欄位被引用時，優化器才會選擇使用 Index ，例如有個複合索引，其欄位順序為(last_name,first_name)，當一段 SQ L如下 :

SQL> select * from employees where first_name = 'David';

由於第一個欄位 last_name 沒有存在 where 條件裡，因此優化器不會使用 Index :

將 SQL 修正如下 :

SQL> select * from employees where last_name = 'David';

此時 SQL 使用 Index 的第一個欄位 last_name ，即使第二個欄位first_name 沒有被 where 條件引用，優化器仍然會選擇使用 Index :

• order by 與 Index :

一般容易被忽略， order by 語法能夠使用到 Index ，但前提必須將欄位屬性設定為不允許空值且查詢中必須包含 Index 的欄位，例如 employees 這個 table 於 employee_id 有 primary key (EMP_EMP_ID_PK) ，一段含有 order by 的查詢如下 :

SQL> select employee_id from employees order by employee_id;

這個時候是會使用到 Index :

這些無法使用到 Index 的語法在撰寫的時候很容易被忽略，尤其是資料型態的部分更是開發人員最常會犯的錯誤，注意一下這些小細節對於 SQL 效能上的提升是有所幫助的。