索引失效的情況有哪些？索引何時會失效？（全面總結）

雖然你這列上建了索引，查詢條件也是索引列，但最終執行計劃沒有走它的索引。下面是引起這種問題的幾個關鍵點。

列與列對比

某個表中，有兩列（id和c_id）都建了單獨索引，下面這種查詢條件不會走索引

select * from test where id=c_id;      

這種情況會被認為還不如走全表掃描。

存在NULL值條件

我們在設計資料庫表時，應該盡力避免NULL值出現，如果非要不可避免的要出現NULL值，也要給一個DEFAULT值，數值型可以給0、-1之類的， 字元串有時候給空串有問題，就給一個空格或其他。如果索引列是可空的，是不會給其建索引的，索引值是少于表的count(*)值的，是以這種情況下，執行計劃自然就去掃描全表了。

select * from test where id is not null;      

這樣的函數還有：to_char、to_date、to_number、trunc等

複合索引前導列區分大

當複合索引前導列區分小的時候，我們有INDEX SKIP SCAN，目前導列區分度大，且查後導列的時候，前導列的分裂會非常耗資源，執行計劃想，還不如全表掃描來的快，然後就索引失效了。

select * from test where owner='sunyang';      

資料類型的轉換

當查詢條件存在隐式轉換時，索引會失效。比如在資料庫裡id存的number類型，但是在查詢時，卻用了下面的形式：

select * from sunyang where id='123';      

Connect By Level

使用connect by level時，不會走索引。

謂詞運算

我們在上面說，不能對索引列進行函數運算，這也包括加減乘除的謂詞運算，這也會使索引失效。建立一個sunyang表，索引為id，看這個SQL：

select * from sunyang where id/2=:type_id;      

這裡很明顯對索引列id進行了’/2’除二運算，這時候就會索引失效，這種情況應該改寫為：

select * from sunyang where id=:type_id*2;      

就可以使用索引了。

Vistual Index

先說明一下，虛拟索引的建立是否有用，需要看具體的執行計劃，如果起作用就可以建一個，如果不起作用就算了。 普通索引這麼建：

create index idx_test_id on test(id);      

虛拟索引Vistual Index這麼建：

create index idx_test_id on test(id) nosegment;      

做了一個實驗，首先建立一個表：

CREATE TABLE test_1116( 
id number, 
a number 
); 

CREATE INDEX idx_test_1116_id on test_1116(id); 
CREATE INDEX idx_test_1116_a on test_1116(a)nosegment;       

其中id為普通索引，a為虛拟索引。

在表中插入十萬條資料

begin 
for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 
commit; 
end;       

接着分别去執行下面的SQL看時間，由于在内網機做實驗，圖貼不出來，資料保證真實性。

select count(id) from test_1116;
--第一次耗時：0.061秒
--第二次耗時：0.016秒

select count(a) from test_1116; 
--第一次耗時：0.031秒
--第二次耗時：0.016秒      

因為在執行過一次後，oracle對結果集緩存了，是以第二次執行耗時不走索引，走記憶體就都一樣了。 可以看到在這種情況下，虛拟索引比普通索引快了一倍。

具體虛拟索引的使用細節，這裡不再展開讨論。

Invisible Index

Invisible Index是oracle 11g提供的新功能，對優化器（還接到前面部落格裡講到的CBO嗎）不可見，我感覺這個功能更主要的是測試用，假如一個表上有那麼多索引，一個一個去看執行計劃調試就很慢了，這時候不如建一個對表和查詢都沒有影響的Invisible Index來進行調試，就顯得很好了。

通過下面的語句來操作索引

alter index idx_test_id invisible;
alter index idx_test_id visible;      

如果想讓CBO看到Invisible Index，需要加入這句：

alter session set optimizer_use_invisible_indexes = true;