当前位置:  数据库>oracle

Oracle不使用索引的几种情况列举

    来源: 互联网  发布时间:2017-06-26

    本文导语: 本文介绍了几种不使用索引的情况,本文实验的Oracle数据库版本均为11.2.0.4情况1: 我们在使用一个B*树索引,而且谓词中没有使用索引的最前列。如果这种情况,可以假设有一个表T,在T(x,y)上有一个索引。要做以下查询:select *...

本文介绍了几种不使用索引的情况,本文实验的Oracle数据库版本均为11.2.0.4
情况1:

我们在使用一个B*树索引,而且谓词中没有使用索引的最前列。
如果这种情况,可以假设有一个表T,在T(x,y)上有一个索引。要做以下查询:select * from t where y=5。此时,优化器就不打算使用T(x,y)上的索引,因为谓词中不涉及X列。在这种情况下,倘若使用索引,可能就必须查看每个索引条目,而优化器通常更倾向于对T表做一个全表扫描。

zx@ORCL>create table t as select rownum x,rownum+1 y,rownum+2 z from dual connect by level < 100000;
 
Table created.
 
zx@ORCL>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能使用这个访问路径。
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。
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的可取值,如下:
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的行不会建立相应的索引条目,所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的,如若不然,倘若它使用索引来统计行数,则可能会得到一个错误的答案。

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)的值建索引。在此不能使用这个索引。如果愿意,可以另外对函数建立索引。
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所述,这就会禁止使用这个索引。
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)

可以看到,它会全面扫描表;另外即使我们对查询给出了以下提示:
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设置会有不同的控制),所以应当把串转为数据。而这样一样(由于应用也函数),就无法使用索引来快速地查找这一行了。如果只是执行串与串的比较:
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_colcreate 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


    
 
 

您可能感兴趣的文章:

  • Oracle与Mysql主键、索引及分页的区别小结
  • 从Oracle的约束到索引
  • Oracle 9i轻松取得建表和索引的DDL语句
  • Oracle9i取得建表和索引的DDL语句
  • oracle10g全文索引自动同步语句使用方法
  • Oracle建立二进制文件索引的方法
  • 在Oracle 10g中如何获得索引的专家建议
  • Oracle全文索引设置
  • 用Oracle 9i全索引扫描快速访问数据
  • Oracle中如何把表和索引放在不同的表空间里
  • Oracle索引存储关系到数据库的运行效率
  • Oracle索引聚簇表的数据加载
  • 在Oracle中监控和跟踪索引使用情况
  • oracle 索引的相关介绍(创建、简介、技巧、怎样查看) .
  • Oracle中检查是否需要重构索引的sql
  • 轻松取得Oracle 9i建表和索引DDL语句
  • 深度揭露Oracle索引使用中的限制
  • Oracle索引(B*tree与Bitmap)的学习总结
  • oracle 索引不能使用深入解析
  • SQL Server和Oracle数据库索引介绍
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 8x监控sysdba角色用户登陆情况
  • oracle中不知道某数据库用户密码的情况下也能登陆
  • 查看Oracle CPU情况
  • Oracle10g使用sql获得ADDM报告以及利用ADDM监控表的dml情况
  • Oracle 数据库开发工具 Oracle SQL Developer iis7站长之家
  • oracle查询锁表与解锁情况提供解决方案
  • [Oracle] 解析在没有备份的情况下undo损坏怎么办
  • 有关Oracle数据库的备份情况
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3