Oracle不使用索引的几种情况列举-低调大师

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

2017-01-19 422

本文介绍了几种不使用索引的情况，本文实验的数据库版本均为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_col<trunc(sysdate+1)

如果可能的话，倘若谓词中有函数，尽量不要对数据库列应用这些函数。这样做不仅可以使用更多的索引，还能减少处理数据库所需的工作。使用转换的条件查询时只会计算一次TRUNC值，然后就能使用索引来查找满足条件的值。使用trunc(date_col)=trunc(sysdate)时，trunc(date_col)则必须对整个表(而不是索引)中的每一行计算一次。

情况5：

另一种情况，如果使用了索引，实际上反而会更慢。Oracle(对于CBO而言)只会在合理地时候才使用索引。

zx@ORCL>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<50;

Execution Plan
----------------------------------------------------------
Plan hash value: 1961899233

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C0017451 |    49 |   245 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"<50)
--运行一个查询查询相对较多的数据
zx@ORCL>select count(y) from t3 where x<50000;

Execution Plan
----------------------------------------------------------
Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |   117   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   | 50000 |  1464K|   117   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<50000)

这个例子显示出优化器不一定会使用索引，而且实际上，它会做出正确的选择。对查询调优时，如果发现你认为本该使用的某个索引实际上并没有用到，就不要冒然强制使用这个索引，而应该先做个测试，并证明使用这个索引后确实会加快速度(通过耗用时间和I/O次数来评判)，然后再考虑让CBO就范(强制它使用这个索引)。总得先给出个理由吧。

情况6：

有一段时间没有分析表了。这些表起先很小，但等到查看时，它们已经增长得非常大。现在索引就有很有意义(尽管原先并非如此)。如果此时分析这个表，就会使用索引。

如果没有正确的统计信息，CBO将无法做出正确的决定。

以上介绍了6种不使用索引的情况，归根结底原因通常就是“不能使用索引，使用索引会返回不正确的结果”，或者“不应该使用，如果使用了索引，性能会变得很糟糕”。

参考:《9I10G11G编程艺术深入数据库体系结构》

MOS文档：Diagnosing Why a Query is Not Using an Index (文档 ID 67522.1)

微信关注我们

原文链接：https://blog.51cto.com/hbxztc/1893241

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Linux NFS服务器的安装与配置详解

一、NFS服务简介 NFS是Network File System（网络文件系统）。主要功能是通过网络让不同的服务器之间可以共享文件或者目录。NFS客户端一般是应用服务器（比如web，负载均衡等），可以通过挂载的方式将NFS服务器端共享的目录挂载到NFS客户端本地的目录下。 NFS在文件传送过程中依赖与RPC（远程过程调用）协议。NFS本身是没有提供信息传送的协议和功能的，但是能够用过网络进行图片，视频，附件等分享功能。只要用到NFS的地方都需要启动RPC服务，不论是NFS的服务端还是客户端。 NFS和RPC的关系：可以理解为NFS是一个网络文件系统（比喻为租房的房主），而RPC是负责信息的传输（中介），客户端（相当于租房的租客）二、系统环境： [root@oldboy~]#cat/etc/redhat-release##查看系统版本信息 CentOSrelease6.7(Final) [root@oldboy~]#uname-r##查看内核信息 2.6.32-573.el6.x86_64 [root@oldboy~]#uname-m##查看系统是32位还是64位的...

2017-01-19

581

只解决c10k问题是不够的，单台主机的处理能力是有限的，一台主机同时处理500个动态网页请求已经是极限了。这里有两种方法提升处理性能，一，定制高性能的计算机，众所周知计算机硬件设计成本极其高，性能每提高10%成本就要增加一倍，这也是intel被称为挤牙膏公司的原因，不是其研发能力不行而是计算机硬件性能提升的难度太大，这个方法貌似不可行。二，使用两台主机处理业务，这就会引来一个问题怎么把请求分发到两个主机，这里我们引入了负载均衡器。 # 目录负载均衡 lvs工作方式和调度方法 ipvsadm的使用 lvs-nat lvs-dr 多端口绑定和持久连接 # 负载均衡使用负载均衡有两个方法，一是采用商用负载均衡硬件的，这个方法是最好的方法，能上硬件一定不要使用软件，又是硬件同样的问题也是价格，只要是涉及到基础设施的服务一定要使用。一方面是硬件的稳定性，比软件好很多，虽然软件可维护性和拓展性比较好，但是对于一些金融公司，他们玩的就是公信力和大众信心，每个故障都是竞争对手拆台的理由，一旦故障硬件背锅，理赔。常用的硬件F5的Big-IP，Citrix的Netscaler，A10的A10 另一种是...

2017-01-19

673

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。