Oracle不使用索引的几种情况列举 |
您所在的位置:网站首页 › oracle索引unusable的几种情况 › Oracle不使用索引的几种情况列举 |
本文介绍了几种不使用索引的情况,本文实验的数据库版本均为11.2.0.4 情况1: 我们在使用一个B*树索引,而且谓词中没有使用索引的最前列。 如果这种情况,可以假设有一个表T,在T(x,y)上有一个索引。要做以下查询:select * from t where y=5。此时,优化器就不打算使用T(x,y)上的索引,因为谓词中不涉及X列。在这种情况下,倘若使用索引,可能就必须查看每个索引条目,而优化器通常更倾向于对T表做一个全表扫描。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 zx@ORCL> create table t as select rownum x,rownum+1 y,rownum+2 z from dual connect by level select count (*) from t; COUNT (*) ---------- 99999 zx@ORCL> create index idx_t on t(x,y); Index created. zx@ORCL> exec dbms_stats.gather_table_stats( user , 'T' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL> set autotrace traceonly explain --where条件使用y=5 zx@ORCL> select * from t where y=5; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 80 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL | T | 1 | 15 | 80 (2)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( "Y" =5) --where条件使用x=5 zx@ORCL> select * from t where x=5; Execution Plan ---------------------------------------------------------- Plan hash value: 1594971208 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 15 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access( "X" =5)但这并不完全排除使用索引。如果查询是select x,y from t where y=5,优化器就会注意到,它不必全面扫描表来得到X或Y(x和y都在索引中),对索引本身做一个民快速的全面扫描会更合适,因为这个索引一般比底层表小得多。还要注意,仅CBO能使用这个访问路径。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 zx@ORCL> select x,y from t where y=5; Execution Plan ---------------------------------------------------------- Plan hash value: 2497555198 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 10 | 81 (2)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| IDX_T | 1 | 10 | 81 (2)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( "Y" =5)另一种情况下CBO也会使用T(x,y)上的索引,这就是索引跳跃式扫描。当且仅当索引的最前列(在上面的例子中最前列是x)只有很少的几个不同值,而且优化器了解这一点,跳跃式扫描(skip scan)就能很好地发挥作用。例如,考虑(GEMDER,EMPNO)上的一个索引,其中GENDER可取值有M和F,而且EMPNO是唯一的。对于以下查询: select * from t where empno=5; 可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询,这说明从概念上讲这个查询会如下处理: select * from t where GENDER='M' and empno=5 union all select * from t where GENDER='F' and empno=5 它会跳跃式地扫描索引,以为这是两个索引:一个对应值M,另一个对应值F。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 zx@ORCL> create table t1 as select decode(mod(rownum,2),0, 'M' , 'F' ) gender,all_objects.* from all_objects; Table created. zx@ORCL> create index idx_t1 on t1(gender,object_id); Index created. zx@ORCL> exec dbms_stats.gather_table_stats( user , 'T1' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL> set autotrace traceonly explain zx@ORCL> select * from t1 where object_id=42; Execution Plan ---------------------------------------------------------- Plan hash value: 4072187533 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 100 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 4 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access( "OBJECT_ID" =42) filter( "OBJECT_ID" =42)INDEX SKIP SCAN 步骤告诉Oralce要跳跃式扫描这个索引,查询GENDER值有改变的地方,并从那里开始向下读树,然后在所考虑的各个虚拟索引中查询OBJECT_id=42。如果大幅增加GENDER的可取值,如下: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 zx@ORCL> alter table t1 modify GENDER varchar2(2); Table altered. zx@ORCL> update t1 set gender=(chr(mod(rownum,1024))); 84656 rows updated. zx@ORCL> commit ; Commit complete. zx@ORCL> exec dbms_stats.gather_table_stats( user , 'T1' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL> set autotrace traceonly explain zx@ORCL> select * from t1 where object_id=42; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 101 | 344 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 101 | 344 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( "OBJECT_ID" =42)情况2: 在使用select count(*) from t查询(或类似的查询),而且在表T上有一个B*树索引。不过,优化器并不是统计索引条目,而是在全面扫描这个表(尽管索引比表要小)。在这种情况下,索引可能建立在一个允许有NULL值的列上。由于对于索引键完全为null的行不会建立相应的索引条目,所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的,如若不然,倘若它使用索引来统计行数,则可能会得到一个错误的答案。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 zx@ORCL> desc t; Name Null ? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- X NUMBER Y NUMBER Z CHAR (23) zx@ORCL> select count (*) from t; Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 153 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL | T | 99999 | 153 (1)| 00:00:02 | ------------------------------------------------------------------- zx@ORCL> alter table t modify y not null ; Table altered. zx@ORCL> desc t Name Null ? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- X NUMBER Y NOT NULL NUMBER Z CHAR (23) zx@ORCL> select count (*) from t; Execution Plan ---------------------------------------------------------- Plan hash value: 2371838348 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 80 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T | 99999 | 80 (0)| 00:00:01 | -----------------------------------------------------------------------情况3: 对于一个有索引的列,做以下查询: select * from t where function(indexed_column)=value; 却发现没有使用indexed_colum上的索引。原因是这个列上使用了函数。如果是对indexed_column的值建立了索引,而不是对function(indexed_column)的值建索引。在此不能使用这个索引。如果愿意,可以另外对函数建立索引。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 zx@ORCL> select * from t where mod(x,999)=1; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 34000 | 153 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL | T | 1000 | 34000 | 153 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(MOD( "X" ,999)=1) zx@ORCL> create index idx_t_f on t(mod(x,999)); Index created. zx@ORCL> exec dbms_stats.gather_table_stats( USER , 'T' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL> select * from t where mod(x,999)=1; Execution Plan ---------------------------------------------------------- Plan hash value: 4125918735 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 3800 | 102 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3800 | 102 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | IDX_T_F | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(MOD( "X" ,999)=1)情况4: 考虑以下情况,已经对一个字符钱建立了索引。这个列只包含数据数据。如果使用以下语法来查询: select * from t where indexed_colum=5; 注意查询中的数字5是常数5(而不是一个字符串),此时就没有使用INDEXED_COLUMN上的索引。这是因为,前面的查询等价于以下查询: select * from t where to_number(indexed_column)=5; 我们对这个列隐式地应用了一个函数,如情况3所述,这就会禁止使用这个索引。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 zx@ORCL> create table t2 (x char (1) constraint t2_pk primary key ,y date ); Table created. zx@ORCL> insert into t2 values ( '5' ,sysdate); 1 row created. zx@ORCL> commit ; Commit complete. zx@ORCL> exec dbms_stats.gather_table_stats( USER , 'T2' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL>explain plan for select * from t2 where x=5; Explained. zx@ORCL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL | T2 | 1 | 12 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER( "X" )=5) Note ----- - dynamic sampling used for this statement ( level =2)可以看到,它会全面扫描表;另外即使我们对查询给出了以下提示: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 zx@ORCL>explain plan for select /*+ index (t2 t2_pk) */ * from t2 where x=5; Explained. zx@ORCL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3365102699 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 2 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER( "X" )=5)在此使用了索引,但是并不像我们想像中那样对索引完成唯一扫描(UNIQUE SCAN),而是完成了全面扫描(FULL SCAN)。原因从最后一行输出可以看出:filter(TO_NUMBER("X")=5)。这里对这个数据库列应用了一个隐式函数。X中存储的字符串必须转换为一个数字,之后才能与值5进行比较。在此无法把5转换为一个串,因为我们的NLS(国家语言支持)设置会控制5转换成串时的具体形式(而这是不确定的,不同的NLS设置会有不同的控制),所以应当把串转为数据。而这样一样(由于应用也函数),就无法使用索引来快速地查找这一行了。如果只是执行串与串的比较: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 zx@ORCL>explain plan for select * from t2 where x= '5' ; Explained. zx@ORCL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3897349516 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 12 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access( "X" = '5' ) 14 rows selected.不出所料,这会得到我们期望的INDEX UNIQUE SCAN,而且可以看到这里没有应用函数。一定要尽可能地避免隐式转换。 还经常出现一个关于日期的问题,如果做以下查询: select * from t where trunc(date_col)=trunc(sysdate); 而且发现这个查询没有使用DATE_COL上的索引,为了解决这个问题,可以对trunc(date_col)建立索引,或者使用区间比较运算符来查询(也许这是更容易的做法)。下面来看对日期使用大于或小于运算符的一个例子。可以认识到以下条件: trunc(date_col)=trunc(sysdate) 与下面的条件是一样的: date_col>= trunc(sysdate) and date_col create table t3 (x,y null , primary key (x) ) as select rownum x,object_name y from all_objects; Table created. zx@ORCL> exec dbms_stats.gather_table_stats( USER , 'T3' , cascade => true ); PL/SQL procedure successfully completed. zx@ORCL> set autotrace traceonly explain --运行一个查询查询相对较少的数据 zx@ORCL> select count (y) from t3 where x |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |