Oracle中不使用索引的幾種情況分析

Oracle不使用b*tree索引的情況大致如下1：where條件中和null比較可能導(dǎo)致不使用索引2：count，sum，ave，max，min等聚集操作時(shí)可能導(dǎo)致不使用索引3：顯示或者隱式的函數(shù)轉(zhuǎn)換導(dǎo)致不使用索引4：在cbo模式下，統(tǒng)計(jì)信息過于陳舊導(dǎo)致不使用索引5：組合索引中沒有使用前導(dǎo)列導(dǎo)致沒有使用索引6：訪問的數(shù)據(jù)量超過一定的比例導(dǎo)致不使用索引下面就其中的幾點(diǎn)做一些說明一：Null可以使用索引嗎一般情況下，where條件中和null比較將會(huì)導(dǎo)致fulltablescan，實(shí)際上，如果table中索引建列的值都為null，那么該行在索引（此處指b*tree，位圖索引和聚簇索引可以有空值）中就不會(huì)存在，因此oracle為了保證查詢結(jié)構(gòu)的準(zhǔn)確性，就會(huì)用full table scan代替index scan，這樣理解，不走索引也就在情理之中。當(dāng)然，如果某個(gè)索引列上有定義為not null，在這種情況下，不存在所有索引列都為空的情況，所以此種情況下，是可以走index scan的，因此，對(duì)于where條件中含有類似is null，=null的情況，是否走索引，還是要看索引建中是否有某個(gè)列定義為not null。具體實(shí)驗(yàn)如下：SQL create table t(x char(3),y char(5));SQL insert into t(x,y) values ('001','xxxxx');SQL insert into t(x,y) values ('002',null);SQL insert into t(x,y) values (null,'yyyyy');SQL insert into t(x,y) values (null,null);SQL commit;SQL create unique index t_idx on t(x,y);SQL analyze table t compute statistics for table for all indexes;SQL select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t_idx'); BLEVEL LEAF_BLOCKS NUM_ROWS---------- ----------- ---------- 0 1 3isnert四條記錄，但索引只保存3條，最后一條沒有保存在索引中SQL set autotrace traceonly explain;SQL select * from t where x is null;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8) 1 0 TABLE ACCESS (FULL)OF 'T' (Cost=2 Card=1 Bytes=8) SQL create table t1(x char(3),y char(5) not null);SQL insert into t1(x,y) values ('001','xxxxx');SQL insert into t1(x,y) values (null,'xxxxx');SQL commit;SQL create unique index t1_idx on t1(x,y);SQL analyze table t1 compute statistics for table for all indexes;SQL select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t1_idx'); BLEVEL LEAF_BLOCKS NUM_ROWS---------- ----------- ---------- 0 1 2SQL select * from t1 where x is null;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=11) 1 0 INDEX (RANGE SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=1 Byt二：COUNT(*)等聚集函數(shù)可能導(dǎo)致不使用索引在做count，sum，ave，max，min等聚集操作時(shí)，有的時(shí)候也會(huì)不用索引，因?yàn)槿绻麅?yōu)化器發(fā)現(xiàn)索引列沒有任何一個(gè)列定義為not null，而且where條件中也沒有索引鍵列，如x=x,在此情況下，索引掃描結(jié)果會(huì)不準(zhǔn)確，此時(shí)oracle就會(huì)用全表full table scan。沿用上面的二個(gè)表來說明SQL select count(*) from t;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4) SQL select sum(x) from t;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=5) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4 Bytes=20)因?yàn)樵摫淼乃饕校▁，y）沒有定義為not null，所以都走了全表掃描，即使把x=null，y=null的行刪除，同樣還是走全表掃描。SQL delete t where x is null and y is null;已刪除1行。CommitSQL analyze table t compute statistics for table for all indexes;SQL select count(*) from t;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=3) 同樣的sql語句，對(duì)于t1表，因?yàn)樗饕衴定義為not null，所以oracle會(huì)選擇index scanSQL select count(*) from t1; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2)SQL select sum(x) from t1;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2 By tes=10)三：隱式或者顯示的函數(shù)轉(zhuǎn)換降導(dǎo)致全表掃描SQL Select * from t1 where x=001;X Y COMM------ ---------- ----------------------------001 xxxxx 88888Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=26) 1 0 TABLE ACCESS (FULL) OF 'T1'(Cost=2 Card=1 Bytes=26)因?yàn)閤為char類似，在這里oracle把x=001做了隱式轉(zhuǎn)換to_number(x)=001,建在該字段的索引將不起作用，基于函數(shù)的索引（function based index）可以在此派上用場，相對(duì)于普通索引，fbi是把經(jīng)過函數(shù)轉(zhuǎn)換后的值存放到索引中SQL create index t1_fbi on t1(to_number(x));SQL analyze table t1 compute statistics for table for all indexes;SQL Select * from t1 where x=001;X Y COMM------ ---------- ----------------------------001 xxxxx 88888Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=26) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes =26) 2 1 INDEX (RANGE SCAN) OF 'T1_FBI'(NON-UNIQUE) (Cost=1 Card =1)四：統(tǒng)計(jì)信息不是最新的，導(dǎo)致無法使用五：組合索引中沒有用到前導(dǎo)列導(dǎo)致沒有用索引,如組合索引（x，y）,where條件類似where y=….,此時(shí)不走索引（如果x的不同值很少，那么oracle9i以后就有可能走index skip scan，其原理類似于select * from t where y=…and x=(某個(gè)確定的值) union all select * from t where y=…and x=(某個(gè)確定的值)……..六：訪問的數(shù)據(jù)比例超過一定范圍，優(yōu)化器會(huì)認(rèn)為full table scan的成本更低，此事走索引掃描反而會(huì)使總成本變大，因此，索引用來快速訪問表中的少量記錄，對(duì)于訪問表中的大量記錄是不適合用索引的。

站在用戶的角度思考問題，與客戶深入溝通，找到錫林浩特網(wǎng)站設(shè)計(jì)與錫林浩特網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：網(wǎng)站建設(shè)、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名申請(qǐng)、雅安服務(wù)器托管、企業(yè)郵箱。業(yè)務(wù)覆蓋錫林浩特地區(qū)。

oracle 強(qiáng)制走索引

若查詢數(shù)據(jù)量過大，需要走索引提升查詢速度，但查詢不走索引，可通過強(qiáng)制走索引方式讓查詢走索引查詢

用法：/*+index(t idx_name)*/

比如：select /*+index(t idx_name)*/t.a from t; t是表別名，idx_name是索引名。若要走多個(gè)索引可在后面添加比如：

/*+index(t idx_name1)(t idx_name2)*/ 不過自己嘗試似乎沒有走多個(gè)有待驗(yàn)證。

oracle不使用索引的原因有哪些

索引失效

1) 沒有查詢條件，或者查詢條件沒有建立索引

2) 在查詢條件上沒有使用引導(dǎo)列

3) 查詢的數(shù)量是大表的大部分，應(yīng)該是30％以上。

4) 索引本身失效

5) 查詢條件使用函數(shù)在索引列上（見12）

6) 對(duì)小表查詢

7) 提示不使用索引

8) 統(tǒng)計(jì)數(shù)據(jù)不真實(shí)

9) CBO計(jì)算走索引花費(fèi)過大的情況。其實(shí)也包含了上面的情況，這里指的是表占有的block要比索引小。

10)隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點(diǎn)應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會(huì)犯的錯(cuò)誤. 由于表的字段tu_mdn定義為varchar2(20),

但在查詢時(shí)把該字段作為number類型以where條件傳給Oracle,這樣會(huì)導(dǎo)致索引失效.

錯(cuò)誤的例子：select * from test where tu_mdn=13333333333;

正確的例子：select * from test where tu_mdn='13333333333';

11)對(duì)索引列進(jìn)行運(yùn)算導(dǎo)致索引失效,我所指的對(duì)索引列進(jìn)行運(yùn)算包括(+，-，*，/，! 等)

錯(cuò)誤的例子：select * from test where id-1=9;

正確的例子：select * from test where id=10;

12)使用Oracle內(nèi)部函數(shù)導(dǎo)致索引失效.對(duì)于這樣情況應(yīng)當(dāng)創(chuàng)建基于函數(shù)的索引.

錯(cuò)誤的例子：select * from test where round(id)=10;

說明，此時(shí)id的索引已經(jīng)不起作用了 正確的例子：首先建立函數(shù)索引，

create index test_id_fbi_idx on test(round(id));

然后 select * from test where round(id)=10; 這時(shí)函數(shù)索引起作用了

1,

2,單獨(dú)的,,(有時(shí)會(huì)用到，有時(shí)不會(huì))

3,like "%_" 百分號(hào)在前.

4,表沒分析.

5,單獨(dú)引用復(fù)合索引里非第一位置的索引列.

6,字符型字段為數(shù)字時(shí)在where條件里不添加引號(hào).

7,對(duì)索引列進(jìn)行運(yùn)算.需要建立函數(shù)索引.

8,not in ,not exist.

9,當(dāng)變量采用的是times變量，而表的字段采用的是date變量時(shí).或相反情況。

10, 索引失效。

11,基于cost成本分析(oracle因?yàn)樽呷沓杀緯?huì)更小)：查詢小表,或者返回值大概在10%以上

12,有時(shí)都考慮到了 但就是不走索引,drop了從建試試在

13,B-tree索引 is null不會(huì)走,is not null會(huì)走,位圖索引 is null,is not null 都會(huì)走

14,聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會(huì)走,

in null時(shí) 必須要和建立索引第一列一起使用,當(dāng)建立索引第一位置條件是is null 時(shí),

其他建立索引的列可以是is null（但必須在所有列 都滿足is null的時(shí)候）,

或者=一個(gè)值；當(dāng)建立索引的第一位置是=一個(gè)值時(shí),其他索引列可以是任何情況（包括is null =一個(gè)值）,

以上兩種情況索引都會(huì)走。其他情況不會(huì)走。

網(wǎng)站名稱：oracle怎么不走索引,oracle不使用索引
文章來源：http://chinadenli.net/article18/hegcdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、網(wǎng)站制作、建站公司、微信公眾號(hào)、網(wǎng)站策劃、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)