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

Oracle不使用b*tree索引的情況大致如下1：where條件中和null比較可能導致不使用索引2：count，sum，ave，max，min等聚集操作時可能導致不使用索引3：顯示或者隱式的函數轉換導致不使用索引4：在cbo模式下，統計信息過于陳舊導致不使用索引5：組合索引中沒有使用前導列導致沒有使用索引6：訪問的數據量超過一定的比例導致不使用索引下面就其中的幾點做一些說明一：Null可以使用索引嗎一般情況下，where條件中和null比較將會導致fulltablescan，實際上，如果table中索引建列的值都為null，那么該行在索引（此處指b*tree，位圖索引和聚簇索引可以有空值）中就不會存在，因此oracle為了保證查詢結構的準確性，就會用full table scan代替index scan，這樣理解，不走索引也就在情理之中。當然，如果某個索引列上有定義為not null，在這種情況下，不存在所有索引列都為空的情況，所以此種情況下，是可以走index scan的，因此，對于where條件中含有類似is null，=null的情況，是否走索引，還是要看索引建中是否有某個列定義為not null。具體實驗如下：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(*)等聚集函數可能導致不使用索引在做count，sum，ave，max，min等聚集操作時，有的時候也會不用索引，因為如果優化器發現索引列沒有任何一個列定義為not null，而且where條件中也沒有索引鍵列，如x=x,在此情況下，索引掃描結果會不準確，此時oracle就會用全表full table scan。沿用上面的二個表來說明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)因為該表的索引列（x，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語句，對于t1表，因為索引列y定義為not null，所以oracle會選擇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)三：隱式或者顯示的函數轉換降導致全表掃描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)因為x為char類似，在這里oracle把x=001做了隱式轉換to_number(x)=001,建在該字段的索引將不起作用，基于函數的索引（function based index）可以在此派上用場，相對于普通索引，fbi是把經過函數轉換后的值存放到索引中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)四：統計信息不是最新的，導致無法使用五：組合索引中沒有用到前導列導致沒有用索引,如組合索引（x，y）,where條件類似where y=….,此時不走索引（如果x的不同值很少，那么oracle9i以后就有可能走index skip scan，其原理類似于select * from t where y=…and x=(某個確定的值) union all select * from t where y=…and x=(某個確定的值)……..六：訪問的數據比例超過一定范圍，優化器會認為full table scan的成本更低，此事走索引掃描反而會使總成本變大，因此，索引用來快速訪問表中的少量記錄，對于訪問表中的大量記錄是不適合用索引的。

站在用戶的角度思考問題，與客戶深入溝通，找到錫林浩特網站設計與錫林浩特網站推廣的解決方案，憑借多年的經驗，讓設計與互聯網技術結合，創造個性化、用戶體驗好的作品，建站類型包括：網站建設、做網站、企業官網、英文網站、手機端網站、網站推廣、域名申請、雅安服務器托管、企業郵箱。業務覆蓋錫林浩特地區。

oracle 強制走索引

若查詢數據量過大，需要走索引提升查詢速度，但查詢不走索引，可通過強制走索引方式讓查詢走索引查詢

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

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

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

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

索引失效

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

2) 在查詢條件上沒有使用引導列

3) 查詢的數量是大表的大部分，應該是30％以上。

4) 索引本身失效

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

6) 對小表查詢

7) 提示不使用索引

8) 統計數據不真實

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

10)隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤. 由于表的字段tu_mdn定義為varchar2(20),

但在查詢時把該字段作為number類型以where條件傳給Oracle,這樣會導致索引失效.

錯誤的例子：select * from test where tu_mdn=13333333333;

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

11)對索引列進行運算導致索引失效,我所指的對索引列進行運算包括(+，-，*，/，! 等)

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

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

12)使用Oracle內部函數導致索引失效.對于這樣情況應當創建基于函數的索引.

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

說明，此時id的索引已經不起作用了 正確的例子：首先建立函數索引，

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

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

1,

2,單獨的,,(有時會用到，有時不會)

3,like "%_" 百分號在前.

4,表沒分析.

5,單獨引用復合索引里非第一位置的索引列.

6,字符型字段為數字時在where條件里不添加引號.

7,對索引列進行運算.需要建立函數索引.

8,not in ,not exist.

9,當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。

10, 索引失效。

11,基于cost成本分析(oracle因為走全表成本會更小)：查詢小表,或者返回值大概在10%以上

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

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

14,聯合索引 is not null 只要在建立的索引列（不分先后）都會走,

in null時 必須要和建立索引第一列一起使用,當建立索引第一位置條件是is null 時,

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

或者=一個值；當建立索引的第一位置是=一個值時,其他索引列可以是任何情況（包括is null =一個值）,

以上兩種情況索引都會走。其他情況不會走。

網站名稱：oracle怎么不走索引,oracle不使用索引
文章來源：http://www.yijiale78.com/article18/hegcdp.html

成都網站建設公司_創新互聯，為您提供手機網站建設、網站制作、建站公司、微信公眾號、網站策劃、

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯