ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [zt] Oracle不使用索引的几种情况

[zt] Oracle不使用索引的几种情况

原创 Linux操作系统 作者:tolywang 时间:2009-05-07 14:21:28 0 删除 编辑

Oracle不使用b*tree索引的情况大致如下

1where条件中和null比较可能导致不使用索引

2countsumavemaxmin等聚集操作时可能导致不使用索引

3:显示或者隐式的函数转换导致不使用索引

4:在cbo模式下,统计信息过于陈旧导致不使用索引

5:组合索引中没有使用前导列导致没有使用索引

6:访问的数据量超过一定的比例导致不使用索引

下面就其中的几点做一些说明

一:Null可以使用索引吗

一般情况下,where条件中和null比较将会导致full table scan,实际上,如果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         3

isnert四条记录,但索引只保存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         2

SQL> 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(*)等聚集函数可能导致不使用索引

在做countsumavemaxmin等聚集操作时,有的时候也会不用索引,因为如果优化器发现索引列没有任何一个列定义为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)

因为该表的索引列(xy)没有定义为not null,所以都走了全表扫描,即使把x=nully=null的行删除,同样还是走全表扫描。

SQL> delete t where x is null and y is null;

已删除1行。

Commit

SQL> 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 scan

SQL> 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     88888

Execution 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)

因为xchar类似,在这里oraclex=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     88888

Execution 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)

四:统计信息不是最新的,导致无法使用

五:组合索引中没有用到前导列导致没有用索引,如组合索引(xy,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的成本更低,此事走索引扫描反而会使总成本变大,因此,索引用来快速访问表中的少量记录,对于访问表中的大量记录是不适合用索引的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-604538/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13421919