oracle索引失效的情況有哪些

這篇文章主要介紹“oracle索引失效的情況有哪些”，在日常操作中，相信很多人在oracle索引失效的情況有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”oracle索引失效的情況有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

列與列對比

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

select * from test where id=c_id;

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

存在NULL值條件

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

select * from test where id is not null;

NOT條件

我們知道建立索引時，給每一個索引列建立一個條目，如果查詢條件為等值或范圍查詢時，索引可以根據(jù)查詢條件去找對應(yīng)的條目。反過來當查詢條件為非時，索引定位就困難了，執(zhí)行計劃此時可能更傾向于全表掃描，這類的查詢條件有：<>、NOT、in、not exists

select * from test where id<>500;select * from test where id in (1,2,3,4,5);select * from test where not in (6,7,8,9,0);select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE通配符

當使用模糊搜索時，盡量采用后置的通配符，例如：name||’%’，因為走索引時，其會從前去匹配索引列，這時候是可以找到的，如果采用前匹配，那么查索引就會很麻煩，比如查詢所有姓張的人，就可以去搜索’張%’。

相反如果你查詢所有叫‘明’的人，那么只能是%明。這時候索引如何定位呢？前匹配的情況下，執(zhí)行計劃會更傾向于選擇全表掃描。后匹配可以走INDEX RANGE SCAN。

所以業(yè)務(wù)設(shè)計的時候，盡量考慮到模糊搜索的問題，要更多的使用后置通配符。

select * from test where name like 張||'%';

條件上包括函數(shù)

查詢條件上盡量不要對索引列使用函數(shù)，比如下面這個SQL

select * from test where upper(name)='SUNYANG';

這樣是不會走索引的，因為索引在建立時會和計算后可能不同，無法定位到索引。但如果查詢條件不是對索引列進行計算，那么依然可以走索引。比如

select * from test where name=upper('sunyang');--INDEX RANGE SCAN

這樣的函數(shù)還有：to_char、to_date、to_number、trunc等

復(fù)合索引前導(dǎo)列區(qū)分大

當復(fù)合索引前導(dǎo)列區(qū)分小的時候，我們有INDEX SKIP SCAN，當前導(dǎo)列區(qū)分度大，且查后導(dǎo)列的時候，前導(dǎo)列的分裂會非常耗資源，執(zhí)行計劃想，還不如全表掃描來的快，然后就索引失效了。

select * from test where owner='sunyang';

數(shù)據(jù)類型的轉(zhuǎn)換

當查詢條件存在隱式轉(zhuǎn)換時，索引會失效。

比如在數(shù)據(jù)庫里id存的number類型，但是在查詢時，卻用了下面的形式：

select * from sunyang where id='123';

Connect By Level

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

謂詞運算

我們在上面說，不能對索引列進行函數(shù)運算，這也包括加減乘除的謂詞運算，這也會使索引失效。

建立一個sunyang表，索引為id，看這個SQL：

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

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

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

就可以使用索引了。

Vistual Index

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

create index idx_test_id on test(id);

虛擬索引Vistual Index這么建：

create index idx_test_id on test(id) nosegment;

做了一個實驗，首先創(chuàng)建一個表：

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為虛擬索引。

在表中插入十萬條數(shù)據(jù)

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

接著分別去執(zhí)行下面的SQL看時間，由于在內(nèi)網(wǎng)機做實驗，圖貼不出來，數(shù)據(jù)保證真實性。

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

因為在執(zhí)行過一次后，oracle對結(jié)果集緩存了，所以第二次執(zhí)行耗時不走索引，走內(nèi)存就都一樣了。

可以看到在這種情況下，虛擬索引比普通索引快了一倍。

具體虛擬索引的使用細節(jié)，這里不再展開討論。

Invisible Index

Invisible Index是oracle 11g提供的新功能，對優(yōu)化器不可見，MySQL 也有，我感覺這個功能更主要的是測試用，假如一個表上有那么多索引，一個一個去看執(zhí)行計劃調(diào)試就很慢了，這時候不如建一個對表和查詢都沒有影響的Invisible Index來進行調(diào)試，就顯得很好了。

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

alter index idx_test_id invisible;alter index idx_test_id visible;
![image](/img/bVbMc2Z)

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

alter session set optimizer_use_invisible_indexes = true;

到此，關(guān)于“oracle索引失效的情況有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>