Oracle不使用数据库索引分类的几种情况列举
点击联系发帖人
时间：2018-08-09 03:34
条件中带OR时不使用索引
[问题点数：40分，结帖人u]
本版专家分：0
结帖率 100%
CSDN今日推荐
本版专家分：561
本版专家分：25993
年度总版至少三次排名前十即授予名人勋章
2005年 总版技术专家分年内排行榜第一2004年 总版技术专家分年内排行榜第一
2006年 总版技术专家分年内排行榜第六2003年 总版技术专家分年内排行榜第八
2005年6月 总版技术专家分月排行榜第一2005年5月 总版技术专家分月排行榜第一2005年4月 总版技术专家分月排行榜第一2005年3月 总版技术专家分月排行榜第一2005年2月 总版技术专家分月排行榜第一2005年1月 总版技术专家分月排行榜第一2004年12月 总版技术专家分月排行榜第一2004年11月 总版技术专家分月排行榜第一2004年10月 总版技术专家分月排行榜第一2004年9月 总版技术专家分月排行榜第一2004年8月 总版技术专家分月排行榜第一2004年7月 总版技术专家分月排行榜第一2004年6月 总版技术专家分月排行榜第一2004年5月 总版技术专家分月排行榜第一2004年4月 总版技术专家分月排行榜第一2004年3月 总版技术专家分月排行榜第一2004年1月 总版技术专家分月排行榜第一2003年12月 总版技术专家分月排行榜第一
本版专家分：0
本版专家分：561
匿名用户不能发表回复！
其他相关推荐在Oracle中，哪几种情况不能用上索引？在Oracle中，哪几种情况不能用上索引？车向前百家号为何在查询中索引未被使用(文档ID、英文文档为67522.1)“为什么索引没有被使用”是一个涉及面较广的问题。有多种原因会导致索引不能被使用。首要的原因就是统计信息不准，第二原因就是索引的选择度不高，使用索引比使用全表扫描效率更差。还有一个比较常见的原因，就是对索引列进行了函数、算术运算或其他表达式等操作，或出现隐式类型转换，导致无法使用索引。还有很多其它原因会导致不能使用索引，这个问题在MOS（MOS即MyOracleSupport）“文档为何在查询中索引未被使用”中有非常详细的解释，作者已经将相关内容发布到BLOG（http://blog.itpub.net//viewspace-2113670/）上了。下面是一些非常有用的检查项目。一、快速检查●表上是否存在索引？●索引是否应该被使用？二、索引本身的问题●索引的索引列是否在WHERE条件中（PredicateList）？●索引列是否用在连接谓词中（JoinPredicates）？●连接顺序（JoinOrder）是否允许使用索引?●索引列是否在IN或者多个OR语句中？●是否对索引列进行了函数、算术运算或其他表达式等操作？●索引列是否出现了隐式类型转换（ImplicitTypeConversion）？●是否在语义（Semantically）上无法使用索引？●错误类型的索引扫描？●索引列是否可以为空？●NLS_SORT是否设置为二进制（BINARY）？●是否使用的是不可见索引（InvisibleIndexes）？三、优化器和成本计算相关问题●是否存在准确且合适的统计信息（Statistics）？●一个索引是否与其它的索引有相同的等级或者成本（Cost）？●索引的选择度是否不高？●在总体成本中，表扫描的成本是否占大部分？●访问空索引并不意味着比访问有值的索引高效？●参数设置是否正确？四、其它问题●是否使用了视图或子查询？●是否存在远程表（RemoteTable)？●是否使用了并行执行（PX）？●是否包含了子查询的UPDATE语句？●查询是否使用了绑定变量？●查询是否引用了带有延迟约束的列？●索引提示（HINT）是否不工作？●索引列是否使用了前置通配符（%）？●索引列是否使用了非等值连接符？●是否在WHERE子句中对索引列进行了ISNULL值判断？本文由百家号作者上传并发布，百家号仅提供信息发布平台。文章仅代表作者个人观点，不代表百度立场。未经作者许可，不得转载。车向前百家号最近更新：简介:只要我们努力，就会绽放出耀眼的光芒作者最新文章相关文章Oracle中查看建立索引和使用索引的注意点
Oracle中查看建立索引和使用索引的注意点
一、查看和建立索引
select * from user_indexes where table_name = 'student'
create index i_student_num on student(num)二、使用索引的注意点
①类型匹配
若student中num列是varchar类型，语句select * from student where num = 100
该语句被转化为select * from student where to_number(num) = 100，该列的索引就失效了。
一、查看和建立索引
select * from user_indexes where table_name = 'student'
create index i_student_num on student(num)二、使用索引的注意点
①类型匹配
若student中num列是varchar类型，语句select * from student where num = 100
该语句被转化为select * from student where to_number(num) = 100，该列的索引就失效了。
②避免索引列参与计算
索引失效：select * from student where num * 10 & 10000
索引有效：select * from student where num & 10000 / 10
③不要对索引列使用IS NULL或IS NOT NULL
原则上对某一个列建立索引的时候，该列就不应该允许为空。
索引失效：select * from student where num is null
用云栖社区APP，舒服~
【云栖快讯】Apache旗下顶级开源盛会 HBasecon Asia 2018将于8月17日在京举行，现场仅600席，免费赠票领取入口&&
消息队列（Message Queue，简称MQ）是阿里云商用的专业消息中间件，是企业级互联网...
是阿里云安全专家基于阿里云多年安全最佳实践经验为云上用户提供的全方位安全技术和咨询服务，为云...
云数据库PPAS版，是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效...
阿里云总监课正式启航原因定位：Oracle为何不能使用索引？
来源：互联网
作者: 佚名 编辑:
【IT168 评论】Oracle数据库有时候出现不能使用索引的现象，出现该现象的原因有很多，该怎么去定位呢?本文我们主要就介绍这一部分内容。 首先，我们要确定数据库运行在何种优化模式下，相应的参数是： optimizer_mode .可在 svrmgrl 中运行& show parameter optimizer_mode& 来查看。 ORACLE V7 以来缺省的设置应是 &choose& ,即如果对已分析的表查询的话选择 CBO ,否则选择 RBO .如果该参数设为& rule &,则不论表是否分析过，一概选用 RBO ,除非在语句中用 hint 强制。 其次，检查被索引的列或组合索引的首列是否出现在 PL/SQL 语句的 WHERE 子句中，这是&执行计划&能。 第三，看采用了哪种类型的连接方式。 ORACLE 的共有 Sort Merge Join ( SMJ )、 Hash Join ( HJ )和 Nested Loop Join ( NL )。在两张表连接，且内表的目标列上建有索引时，只有 Nested Loop 才能有效地利用到该索引。 SMJ 即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。 HJ 由于须做 HASH 运算，索引的存在对数据查询速度几乎没有影响。 第四，看连接顺序是否允许使用相关索引。假设表 emp 的 deptno 列上有索引，表 dept 的列 deptno 上无索引， WHERE 语句有 emp.deptno=dept.deptno 条件。在做NL 连接时， emp 做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描， emp.deptno 上的索引显然是用不上，最多在其上。 第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的&执行计划。 第六，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较， ORACLE 会自动将字符型用to_number() 函数进行转换，从而导致第六种现象的发生。 第七，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用 SQL 语句& analyze table xxxx compute statis& . ORACLE 掌。 第八，索引列的选择性不高。 我们假设典型情况，有表emp ,共有一百万行数据，但其中的emp.deptno列，数据只有 4 种不同的值，如 10 、 20 、 30 、 40 .虽然 emp 数据行有很多， ORACLE 缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种 deptno 值各有 25 万数据行与之对应。假设 SQL 搜索条件 DEPTNO=10 ,利用 deptno 列上的索引进行数据搜索效率，往往不比全表扫描的高， ORACLE 理不是在4 种deptno 值间平均分配，其中有99 万行对应着值10 , 5000 行对应值 20 , 3000 行对应值 30 , 2000 行对应值 40 .在这种数据分布图案中对除值为 10 外的其它 deptno 值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用 analyze 语句对该列建立直方图，对该列搜集足够的统计数据，使 ORACLE 在搜索选择性较高的值能用上索引。 第九，索引列值是否可为空( NULL )。如果索引列值可以是空值，在 SQL 语句中那些需要返回 NULL 值的操作，将不会用到索引，如 COUNT ( * )，而是用全表扫描。这是因为索引中存储值不能为全空。 第十，看是否有用到并行查询( PQO )。并行查询将不会用到索引。如我们想要用到A 表的IND_COL1 & SELECT /*+ INDEX ( A IND_COL1 ) */ * FROM A WHERE COL1 = XXX;&注意，注释符必须跟在SELECT之后，且注释中的& + &要紧跟着注释起始符& /* &或& -- &,否则 hint 就被认为是一般注释，对 PL/SQL 语句的执行不产生任何影响。 一种是 EXPLAIN TABLE 方式。 用户必须首先在自己的模式( SCHEMA )下，建立 PLAN_TABLE 表，执行计划的每一步骤都将记录在该表中，建表 SQL 脚本为在 ${ORACLE_HOME}/rdbms/admin/ 下的 utlxplan.sql 打开 SQL*PLUS ,输入& SET AUTOTRACE ON &,然后运行待调试的 SQL 语句。在给出查询结果后， ORACLE 将显示相应的&执行计划&,包括优化器类型、执行代价、连接方式、连接顺序、数据搜索路径以如果我们不能确定需要跟踪的具体 SQL 语句，比如某个应用使用一段时间后，响应速度忽然变慢。我们这时可以利用 ORACLE 提供的另一个有力工具 TKPROF ,对应用的执行过程全程跟踪。 我们要先在系统视图 V$SESSION 中，可根据 USERID 或 MACHINE ,查出相应的 SID 和 SERIAL# .以SYS 或其他有执行 DBMS_SYSTEM 程序包的用户连接数据库，执行& EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ( SID , SERIAL# , TRUE );&.然后运行应用程序，这时在服务器端，数据库参& USER_DUMP_DEST &指示的目录下，会生成 ora__xxxx.trc 文件，其中 xxxx 为被跟踪应用的操作系统进程号。应用程序执行完成后，用命令 tkprof 对该文件进行分析。 命令示例：& tkprof tracefile outputfile explain=userid/password& .在操作系统 ORACLE 用户下，键入& tkprof &,会有详细的命令帮助。分析后的输出文件 outputfile 中，有每一条 PL/SQL 语句的&执行计划&、 CPU 占用、物理读次数、逻辑读次数、执行时长等重要信息。根据输出文件的信息，我们可以很快发现应用中哪条 PL/SQL 语句是问题的症结所在。 关于Oracle数据库不能使用索引的原因定位就介绍到这里了，希望本次的介绍能够对您有所帮助。
IT168企业级
扫一扫关注
行车视线文章推荐oracle不使用索引的原因有哪些？
oracle 不使用索引的原因有哪些？
今天是，今天开始总结一下oracle不使用索引的原因有哪些。一边学习一边做笔记。
第一种：行数存在差异。
在视图user_tables存在一个num_rows字段，该字段是记录在统计信息收集后所对应对象的行数，在user_tab_columns视图中存在一个num_distinct字段，该字段记录每个字段内不同数值的个数。oracle认为当num_distinct越接近num_rows的时候索引的选择性越好，那么在执行查询的时候越容易使用索引。
第二种：聚簇因子：
什么是聚簇因子？
聚簇因子是衡量索引列数据顺序与表字段数据顺序相似性的一个值。我们都知道在创建的表中一般都是堆表，也就是数据在表中存储是无续的，那么为了更加快速的访问数据，我们通常使用索引进行数据访问，这时候没个索引都有一个聚簇因子，聚簇因子越接近对象的块数，那么选择性越好，越接近表的行数那么选择性越差。
之前听到有个朋友曾经提到这么一个问题&为什么我在测试环境查询一个数据很快和在生产环境查询数据怎么这么慢呢？表结构都一样的，数据也是一样的。&。那么不妨看看聚簇因子是多少。
聚簇因子的查看是从user_ind_statistics视图中： CLUSTERING_FACTOR 表示的。看一下官方介绍：
Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
往往聚簇因子的大小和数据获取的I/o存在一定的相似性。如果聚簇因子大，那么相对的物理或是逻辑（一般是）i/o开销很大，也就是块被频繁反复读取，一致数据获取很慢。
长查询的视图有dba_ind_statistics和dba_tab_statistics
第三种：使用不等条件：
当使用在进行查询数据的时候使用不等条件&&，那么oracle任务这个符号会需要读取大部分的数据块，那么就会跳过使用索引。eg：
SQL& select index_name,table_name,column_name from user_ind_columns where table_name='EMP';
INDEX_NAME
TABLE_NAME
COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
SQL& select *
EMPNO ENAME
MGR HIREDATE
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK
7839 09-JUN-81
7934 MILLER
7782 23-JAN-82
7369 SMITH
7902 17-DEC-80
7566 JONES
7839 02-APR-81
7788 SCOTT
7566 19-APR-87
7876 ADAMS
7788 23-MAY-87
7566 03-DEC-81
7499 ALLEN
7698 20-FEB-81
7698 22-FEB-81
7654 MARTIN
7698 28-SEP-81
7698 BLAKE
7839 01-MAY-81
7844 TURNER
7698 08-SEP-81
7900 JAMES
7698 03-DEC-81
14 rows selected.
SQL& set autotrace trace exp
SQL& select * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&EMPNO&&&7900)
SQL& select /*+index(emp EMP_IDX1)*/ * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL
(0)| 00:00:01 |
TABLE ACCESS BY LOCAL INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX FULL SCAN
| EMP_IDX1 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(&EMPNO&&&7900)
可知，当使用&&就会跳过索引，但是我们可以使用hints（提示）让强制使用索引，注意not in或是in 关键字类似&&依然会跳过索引，那么替代办法要么更改sql查询语句，要么使用case when条件，那么也需要在建立函数索引了。
第四种：统计信息过旧
在基于成本的optimizer更具数据进行估计cast，当数据已经被修改（如进行了大量的dml操作），那么统计信息肯定过旧，那么oracle在更加统计信息进行执行计划选择的时候往往可能出现选择错误的执行计划。
在10g开始oracle会有自动收集统计信息的任务在运行。
17:15:22 sys@REPDB&select * from v$
----------------------------------------------------------------
Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
10.2.0.5.0
Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
17:15:28 sys@REPDB&select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------
07-2?? -14 03.00.01.146182 &??? +08:00
purge log job
FGR$AUTOPURGE_JOB
file group auto-purge job
GATHER_STATS_JOB
Oracle defined automatic optimizer statistics collection job
AUTO_SPACE_ADVISOR_JOB
auto space advisor maintenance job
MGMT_CONFIG_JOB
06-2?? -14 10.00.02.198895 ???? +08:00
Configuration collection job.
MGMT_STATS_CONFIG_JOB
01-2?? -14 01.01.01.762793 &??? +08:00
OCM Statistics collection job.
RLM$EVTCLEANUP
07-2?? -14 04.18.33.923785 ???? +08:00
RLM$SCHDNEGACTION
07-2?? -14 05.02.43.177946 ???? +08:00
&?????8DD?￡
但在11g该内容开始取消；
SQL& select * from v$
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
11.2.0.4.0
Production
TNS for : Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL& select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ -------------------------------------------------- --------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE
07-FEB-14 12.00.00.522780 AM PST8PDT
auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT
07-FEB-14 12.00.00.242715 AM PST8PDT
auto clean job for recoverable script
FGR$AUTOPURGE_JOB
file group auto-purge job
BSLN_MAINTAIN_STATS_JOB
06-FEB-14 10.39.20.219977 PM -07:00
Oracle defined automatic moving window baseline st
atistics computation job
DRA_REEVALUATE_OPEN_FAILURES
31-JAN-14 06.00.02.807655 AM PST8PDT
Reevaluate open failures for DRA
HM_CREATE_OFFLINE_DICTIONARY
Create offline dictionary in ADR for DRA name tran
ORA$AUTOTASK_CLEAN
06-FEB-14 09.39.19.975070 PM PST8PDT
Delete obsolete AUTOTASK repository data
FILE_WATCHER
File watcher job
06-FEB-14 09.39.19.999313 PM PST8PDT
purge log job
MGMT_STATS_CONFIG_JOB
06-FEB-14 10.39.19.607560 PM -07:00
OCM Statistics collection job.
MGMT_CONFIG_JOB
07-FEB-14 01.01.01.661585 AM -07:00
Configuration collection job.
RLM$SCHDNEGACTION
07-FEB-14 04.32.12.069752 PM +08:00
RLM$EVTCLEANUP
07-FEB-14 01.49.46.407904 AM -07:00
14 rows selected.
那么当统计信息过久的时候通常使用dbms_stats包进行相应对象信息的收集。
第五种：使用通配符查询：
当谓词条件中存在%或是_通配符的时候，oracle会忽略索引，但是可以通过通配符位移解决，如下：
SQL& create index emp_idx2 on emp(ename);
Index created.
SQL& set autotrace trace exp
SQL& select * from emp where ename like '%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&ENAME& LIKE '%OT%')
SQL& SELECT * FROM EMP WHERE ENAME LIKE 'SC%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
TABLE ACCESS BY GLOBAL INDEX ROWID| EMP
(0)| 00:00:01 | ROWID | ROWID |
INDEX RANGE SCAN
| EMP_IDX2 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(&ENAME& LIKE 'SC%OT%')
filter(&ENAME& LIKE 'SC%OT%')
第六种：在谓词条件中存在函数
无论在谓词中存在隐式函数转换或是显示函数那么oracle都不会使用索引，对于隐式函数转换例子如下：
https://blog.csdn.net/rhys_oracle/article/details/
第七种：谓词列存在null值。
当在谓词条件中的列存在null值，那么oracle将跳过索引，因为null值不存在索引段中，但是如果是复合索引，其中一列为null，另外一列不为null，那么数据库依然使用索引。
第八种：跳过前导列
这是最后一种不使用索引的原因，那就是如果是在默写列上创建了复合索引，但在谓词条件中没有涉及到前导列，那么将会是跳过索引。&}
[问题点数：40分，结帖人u]
本版专家分：0
结帖率 100%
CSDN今日推荐
本版专家分：561
本版专家分：25993
年度总版至少三次排名前十即授予名人勋章
2005年 总版技术专家分年内排行榜第一2004年 总版技术专家分年内排行榜第一
2006年 总版技术专家分年内排行榜第六2003年 总版技术专家分年内排行榜第八
2005年6月 总版技术专家分月排行榜第一2005年5月 总版技术专家分月排行榜第一2005年4月 总版技术专家分月排行榜第一2005年3月 总版技术专家分月排行榜第一2005年2月 总版技术专家分月排行榜第一2005年1月 总版技术专家分月排行榜第一2004年12月 总版技术专家分月排行榜第一2004年11月 总版技术专家分月排行榜第一2004年10月 总版技术专家分月排行榜第一2004年9月 总版技术专家分月排行榜第一2004年8月 总版技术专家分月排行榜第一2004年7月 总版技术专家分月排行榜第一2004年6月 总版技术专家分月排行榜第一2004年5月 总版技术专家分月排行榜第一2004年4月 总版技术专家分月排行榜第一2004年3月 总版技术专家分月排行榜第一2004年1月 总版技术专家分月排行榜第一2003年12月 总版技术专家分月排行榜第一
本版专家分：0
本版专家分：561
匿名用户不能发表回复！
其他相关推荐在Oracle中，哪几种情况不能用上索引？在Oracle中，哪几种情况不能用上索引？车向前百家号为何在查询中索引未被使用(文档ID、英文文档为67522.1)“为什么索引没有被使用”是一个涉及面较广的问题。有多种原因会导致索引不能被使用。首要的原因就是统计信息不准，第二原因就是索引的选择度不高，使用索引比使用全表扫描效率更差。还有一个比较常见的原因，就是对索引列进行了函数、算术运算或其他表达式等操作，或出现隐式类型转换，导致无法使用索引。还有很多其它原因会导致不能使用索引，这个问题在MOS（MOS即MyOracleSupport）“文档为何在查询中索引未被使用”中有非常详细的解释，作者已经将相关内容发布到BLOG（http://blog.itpub.net//viewspace-2113670/）上了。下面是一些非常有用的检查项目。一、快速检查●表上是否存在索引？●索引是否应该被使用？二、索引本身的问题●索引的索引列是否在WHERE条件中（PredicateList）？●索引列是否用在连接谓词中（JoinPredicates）？●连接顺序（JoinOrder）是否允许使用索引?●索引列是否在IN或者多个OR语句中？●是否对索引列进行了函数、算术运算或其他表达式等操作？●索引列是否出现了隐式类型转换（ImplicitTypeConversion）？●是否在语义（Semantically）上无法使用索引？●错误类型的索引扫描？●索引列是否可以为空？●NLS_SORT是否设置为二进制（BINARY）？●是否使用的是不可见索引（InvisibleIndexes）？三、优化器和成本计算相关问题●是否存在准确且合适的统计信息（Statistics）？●一个索引是否与其它的索引有相同的等级或者成本（Cost）？●索引的选择度是否不高？●在总体成本中，表扫描的成本是否占大部分？●访问空索引并不意味着比访问有值的索引高效？●参数设置是否正确？四、其它问题●是否使用了视图或子查询？●是否存在远程表（RemoteTable)？●是否使用了并行执行（PX）？●是否包含了子查询的UPDATE语句？●查询是否使用了绑定变量？●查询是否引用了带有延迟约束的列？●索引提示（HINT）是否不工作？●索引列是否使用了前置通配符（%）？●索引列是否使用了非等值连接符？●是否在WHERE子句中对索引列进行了ISNULL值判断？本文由百家号作者上传并发布，百家号仅提供信息发布平台。文章仅代表作者个人观点，不代表百度立场。未经作者许可，不得转载。车向前百家号最近更新：简介:只要我们努力，就会绽放出耀眼的光芒作者最新文章相关文章Oracle中查看建立索引和使用索引的注意点
Oracle中查看建立索引和使用索引的注意点
一、查看和建立索引
select * from user_indexes where table_name = 'student'
create index i_student_num on student(num)二、使用索引的注意点
①类型匹配
若student中num列是varchar类型，语句select * from student where num = 100
该语句被转化为select * from student where to_number(num) = 100，该列的索引就失效了。
一、查看和建立索引
select * from user_indexes where table_name = 'student'
create index i_student_num on student(num)二、使用索引的注意点
①类型匹配
若student中num列是varchar类型，语句select * from student where num = 100
该语句被转化为select * from student where to_number(num) = 100，该列的索引就失效了。
②避免索引列参与计算
索引失效：select * from student where num * 10 & 10000
索引有效：select * from student where num & 10000 / 10
③不要对索引列使用IS NULL或IS NOT NULL
原则上对某一个列建立索引的时候，该列就不应该允许为空。
索引失效：select * from student where num is null
用云栖社区APP，舒服~
【云栖快讯】Apache旗下顶级开源盛会 HBasecon Asia 2018将于8月17日在京举行，现场仅600席，免费赠票领取入口&&
消息队列（Message Queue，简称MQ）是阿里云商用的专业消息中间件，是企业级互联网...
是阿里云安全专家基于阿里云多年安全最佳实践经验为云上用户提供的全方位安全技术和咨询服务，为云...
云数据库PPAS版，是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效...
阿里云总监课正式启航原因定位：Oracle为何不能使用索引？
来源：互联网
作者: 佚名 编辑:
【IT168 评论】Oracle数据库有时候出现不能使用索引的现象，出现该现象的原因有很多，该怎么去定位呢?本文我们主要就介绍这一部分内容。 首先，我们要确定数据库运行在何种优化模式下，相应的参数是： optimizer_mode .可在 svrmgrl 中运行& show parameter optimizer_mode& 来查看。 ORACLE V7 以来缺省的设置应是 &choose& ,即如果对已分析的表查询的话选择 CBO ,否则选择 RBO .如果该参数设为& rule &,则不论表是否分析过，一概选用 RBO ,除非在语句中用 hint 强制。 其次，检查被索引的列或组合索引的首列是否出现在 PL/SQL 语句的 WHERE 子句中，这是&执行计划&能。 第三，看采用了哪种类型的连接方式。 ORACLE 的共有 Sort Merge Join ( SMJ )、 Hash Join ( HJ )和 Nested Loop Join ( NL )。在两张表连接，且内表的目标列上建有索引时，只有 Nested Loop 才能有效地利用到该索引。 SMJ 即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。 HJ 由于须做 HASH 运算，索引的存在对数据查询速度几乎没有影响。 第四，看连接顺序是否允许使用相关索引。假设表 emp 的 deptno 列上有索引，表 dept 的列 deptno 上无索引， WHERE 语句有 emp.deptno=dept.deptno 条件。在做NL 连接时， emp 做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描， emp.deptno 上的索引显然是用不上，最多在其上。 第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的&执行计划。 第六，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较， ORACLE 会自动将字符型用to_number() 函数进行转换，从而导致第六种现象的发生。 第七，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用 SQL 语句& analyze table xxxx compute statis& . ORACLE 掌。 第八，索引列的选择性不高。 我们假设典型情况，有表emp ,共有一百万行数据，但其中的emp.deptno列，数据只有 4 种不同的值，如 10 、 20 、 30 、 40 .虽然 emp 数据行有很多， ORACLE 缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种 deptno 值各有 25 万数据行与之对应。假设 SQL 搜索条件 DEPTNO=10 ,利用 deptno 列上的索引进行数据搜索效率，往往不比全表扫描的高， ORACLE 理不是在4 种deptno 值间平均分配，其中有99 万行对应着值10 , 5000 行对应值 20 , 3000 行对应值 30 , 2000 行对应值 40 .在这种数据分布图案中对除值为 10 外的其它 deptno 值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用 analyze 语句对该列建立直方图，对该列搜集足够的统计数据，使 ORACLE 在搜索选择性较高的值能用上索引。 第九，索引列值是否可为空( NULL )。如果索引列值可以是空值，在 SQL 语句中那些需要返回 NULL 值的操作，将不会用到索引，如 COUNT ( * )，而是用全表扫描。这是因为索引中存储值不能为全空。 第十，看是否有用到并行查询( PQO )。并行查询将不会用到索引。如我们想要用到A 表的IND_COL1 & SELECT /*+ INDEX ( A IND_COL1 ) */ * FROM A WHERE COL1 = XXX;&注意，注释符必须跟在SELECT之后，且注释中的& + &要紧跟着注释起始符& /* &或& -- &,否则 hint 就被认为是一般注释，对 PL/SQL 语句的执行不产生任何影响。 一种是 EXPLAIN TABLE 方式。 用户必须首先在自己的模式( SCHEMA )下，建立 PLAN_TABLE 表，执行计划的每一步骤都将记录在该表中，建表 SQL 脚本为在 ${ORACLE_HOME}/rdbms/admin/ 下的 utlxplan.sql 打开 SQL*PLUS ,输入& SET AUTOTRACE ON &,然后运行待调试的 SQL 语句。在给出查询结果后， ORACLE 将显示相应的&执行计划&,包括优化器类型、执行代价、连接方式、连接顺序、数据搜索路径以如果我们不能确定需要跟踪的具体 SQL 语句，比如某个应用使用一段时间后，响应速度忽然变慢。我们这时可以利用 ORACLE 提供的另一个有力工具 TKPROF ,对应用的执行过程全程跟踪。 我们要先在系统视图 V$SESSION 中，可根据 USERID 或 MACHINE ,查出相应的 SID 和 SERIAL# .以SYS 或其他有执行 DBMS_SYSTEM 程序包的用户连接数据库，执行& EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ( SID , SERIAL# , TRUE );&.然后运行应用程序，这时在服务器端，数据库参& USER_DUMP_DEST &指示的目录下，会生成 ora__xxxx.trc 文件，其中 xxxx 为被跟踪应用的操作系统进程号。应用程序执行完成后，用命令 tkprof 对该文件进行分析。 命令示例：& tkprof tracefile outputfile explain=userid/password& .在操作系统 ORACLE 用户下，键入& tkprof &,会有详细的命令帮助。分析后的输出文件 outputfile 中，有每一条 PL/SQL 语句的&执行计划&、 CPU 占用、物理读次数、逻辑读次数、执行时长等重要信息。根据输出文件的信息，我们可以很快发现应用中哪条 PL/SQL 语句是问题的症结所在。 关于Oracle数据库不能使用索引的原因定位就介绍到这里了，希望本次的介绍能够对您有所帮助。
IT168企业级
扫一扫关注
行车视线文章推荐oracle不使用索引的原因有哪些？
oracle 不使用索引的原因有哪些？
今天是，今天开始总结一下oracle不使用索引的原因有哪些。一边学习一边做笔记。
第一种：行数存在差异。
在视图user_tables存在一个num_rows字段，该字段是记录在统计信息收集后所对应对象的行数，在user_tab_columns视图中存在一个num_distinct字段，该字段记录每个字段内不同数值的个数。oracle认为当num_distinct越接近num_rows的时候索引的选择性越好，那么在执行查询的时候越容易使用索引。
第二种：聚簇因子：
什么是聚簇因子？
聚簇因子是衡量索引列数据顺序与表字段数据顺序相似性的一个值。我们都知道在创建的表中一般都是堆表，也就是数据在表中存储是无续的，那么为了更加快速的访问数据，我们通常使用索引进行数据访问，这时候没个索引都有一个聚簇因子，聚簇因子越接近对象的块数，那么选择性越好，越接近表的行数那么选择性越差。
之前听到有个朋友曾经提到这么一个问题&为什么我在测试环境查询一个数据很快和在生产环境查询数据怎么这么慢呢？表结构都一样的，数据也是一样的。&。那么不妨看看聚簇因子是多少。
聚簇因子的查看是从user_ind_statistics视图中： CLUSTERING_FACTOR 表示的。看一下官方介绍：
Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
往往聚簇因子的大小和数据获取的I/o存在一定的相似性。如果聚簇因子大，那么相对的物理或是逻辑（一般是）i/o开销很大，也就是块被频繁反复读取，一致数据获取很慢。
长查询的视图有dba_ind_statistics和dba_tab_statistics
第三种：使用不等条件：
当使用在进行查询数据的时候使用不等条件&&，那么oracle任务这个符号会需要读取大部分的数据块，那么就会跳过使用索引。eg：
SQL& select index_name,table_name,column_name from user_ind_columns where table_name='EMP';
INDEX_NAME
TABLE_NAME
COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
SQL& select *
EMPNO ENAME
MGR HIREDATE
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK
7839 09-JUN-81
7934 MILLER
7782 23-JAN-82
7369 SMITH
7902 17-DEC-80
7566 JONES
7839 02-APR-81
7788 SCOTT
7566 19-APR-87
7876 ADAMS
7788 23-MAY-87
7566 03-DEC-81
7499 ALLEN
7698 20-FEB-81
7698 22-FEB-81
7654 MARTIN
7698 28-SEP-81
7698 BLAKE
7839 01-MAY-81
7844 TURNER
7698 08-SEP-81
7900 JAMES
7698 03-DEC-81
14 rows selected.
SQL& set autotrace trace exp
SQL& select * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&EMPNO&&&7900)
SQL& select /*+index(emp EMP_IDX1)*/ * from emp where empno&&7900;
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL
(0)| 00:00:01 |
TABLE ACCESS BY LOCAL INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX FULL SCAN
| EMP_IDX1 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(&EMPNO&&&7900)
可知，当使用&&就会跳过索引，但是我们可以使用hints（提示）让强制使用索引，注意not in或是in 关键字类似&&依然会跳过索引，那么替代办法要么更改sql查询语句，要么使用case when条件，那么也需要在建立函数索引了。
第四种：统计信息过旧
在基于成本的optimizer更具数据进行估计cast，当数据已经被修改（如进行了大量的dml操作），那么统计信息肯定过旧，那么oracle在更加统计信息进行执行计划选择的时候往往可能出现选择错误的执行计划。
在10g开始oracle会有自动收集统计信息的任务在运行。
17:15:22 sys@REPDB&select * from v$
----------------------------------------------------------------
Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
10.2.0.5.0
Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
17:15:28 sys@REPDB&select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------
07-2?? -14 03.00.01.146182 &??? +08:00
purge log job
FGR$AUTOPURGE_JOB
file group auto-purge job
GATHER_STATS_JOB
Oracle defined automatic optimizer statistics collection job
AUTO_SPACE_ADVISOR_JOB
auto space advisor maintenance job
MGMT_CONFIG_JOB
06-2?? -14 10.00.02.198895 ???? +08:00
Configuration collection job.
MGMT_STATS_CONFIG_JOB
01-2?? -14 01.01.01.762793 &??? +08:00
OCM Statistics collection job.
RLM$EVTCLEANUP
07-2?? -14 04.18.33.923785 ???? +08:00
RLM$SCHDNEGACTION
07-2?? -14 05.02.43.177946 ???? +08:00
&?????8DD?￡
但在11g该内容开始取消；
SQL& select * from v$
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
11.2.0.4.0
Production
TNS for : Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL& select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_
LAST_START_DATE
------------------------------ -------------------------------------------------- --------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE
07-FEB-14 12.00.00.522780 AM PST8PDT
auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT
07-FEB-14 12.00.00.242715 AM PST8PDT
auto clean job for recoverable script
FGR$AUTOPURGE_JOB
file group auto-purge job
BSLN_MAINTAIN_STATS_JOB
06-FEB-14 10.39.20.219977 PM -07:00
Oracle defined automatic moving window baseline st
atistics computation job
DRA_REEVALUATE_OPEN_FAILURES
31-JAN-14 06.00.02.807655 AM PST8PDT
Reevaluate open failures for DRA
HM_CREATE_OFFLINE_DICTIONARY
Create offline dictionary in ADR for DRA name tran
ORA$AUTOTASK_CLEAN
06-FEB-14 09.39.19.975070 PM PST8PDT
Delete obsolete AUTOTASK repository data
FILE_WATCHER
File watcher job
06-FEB-14 09.39.19.999313 PM PST8PDT
purge log job
MGMT_STATS_CONFIG_JOB
06-FEB-14 10.39.19.607560 PM -07:00
OCM Statistics collection job.
MGMT_CONFIG_JOB
07-FEB-14 01.01.01.661585 AM -07:00
Configuration collection job.
RLM$SCHDNEGACTION
07-FEB-14 04.32.12.069752 PM +08:00
RLM$EVTCLEANUP
07-FEB-14 01.49.46.407904 AM -07:00
14 rows selected.
那么当统计信息过久的时候通常使用dbms_stats包进行相应对象信息的收集。
第五种：使用通配符查询：
当谓词条件中存在%或是_通配符的时候，oracle会忽略索引，但是可以通过通配符位移解决，如下：
SQL& create index emp_idx2 on emp(ename);
Index created.
SQL& set autotrace trace exp
SQL& select * from emp where ename like '%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
PARTITION RANGE ALL|
(0)| 00:00:01 |
TABLE ACCESS FULL | EMP
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(&ENAME& LIKE '%OT%')
SQL& SELECT * FROM EMP WHERE ENAME LIKE 'SC%OT%';
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
TABLE ACCESS BY GLOBAL INDEX ROWID| EMP
(0)| 00:00:01 | ROWID | ROWID |
INDEX RANGE SCAN
| EMP_IDX2 |
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(&ENAME& LIKE 'SC%OT%')
filter(&ENAME& LIKE 'SC%OT%')
第六种：在谓词条件中存在函数
无论在谓词中存在隐式函数转换或是显示函数那么oracle都不会使用索引，对于隐式函数转换例子如下：
https://blog.csdn.net/rhys_oracle/article/details/
第七种：谓词列存在null值。
当在谓词条件中的列存在null值，那么oracle将跳过索引，因为null值不存在索引段中，但是如果是复合索引，其中一列为null，另外一列不为null，那么数据库依然使用索引。
第八种：跳过前导列
这是最后一种不使用索引的原因，那就是如果是在默写列上创建了复合索引，但在谓词条件中没有涉及到前导列，那么将会是跳过索引。&}
我要回帖
更多关于 oracle索引的使用 的文章
更多推荐
- ·山西的物流朋友，想申请网络货运平台公司注册要求的资质，这个过程有什么需要注意的？
- ·中小物流公司加盟哪个好企业考虑加盟网络货运有什么成功的加盟模式可以借鉴吗？
- ·笑微微本地生活服务类似微店的平台有哪些多少商品种类呢？
- ·在京东怕不怕12315投诉购物被欺骗，多次向京东怕不怕12315投诉投诉没用，该怎么办？
- ·请问凭一个抖音频繁查一个人会被发现吗号可以查到本人难吗？
- ·索尼klv-s40a10开机后图象正常，但声音孕22周胎动时有时无无，请问是哪里故障？
- ·罗密施耐德德watsna-63/63.4cbr怎么接线？下图对吗？
- ·P2P平台频频P2P跑路如何报案，报案有没有用
- ·mac 如何苹果怎么将网页收藏夹制作成图片
- ·微信红包撤回被骗发红包被骗能撤回吗
- ·怎么制作gif动态图制作？
- ·苏泊尔电饭煲蒸米饭多长时间这款电饭煲，煮米饭按哪个按键？
- ·滴灌带测径仪仪能做到在线测量吗？
- ·滴滴快车接了单苹果自带导航按出发没反应滴滴导航怎么没反应？
- ·磐正华硕主板接线图解现在这么样，算一线还是二线品牌？
- ·QQ阅读书评回复如何快速qq怎么翻到最上面自己的楼层（嗯，在6000楼）
- ·百度贴吧有个ID叫雪剑23的哥们，那哥们是不是已经疯了？
- ·自己买了两台织安平不锈钢丝网织机的小机子(铁丝)给人家做加工今天乡里突然来了把电表给摘走了我可咋办
- ·什么样的助听器好问0爱耳时代戴起能舒适些？
- ·Oracle不使用数据库索引分类的几种情况列举
- ·请教哪个J2ME混淆器的混淆能力海贼王之最强剑豪系统
- ·有萨顶顶的《萨顶顶左手指月资源》的资源吗？
- ·NC百度天天给我推荐历史是个什么玩意儿读后感
- ·为什么iwatch打开iwatch体能训练练总是亮绿灯。关上iwatch体能训练练以后绿灯过好久才会熄灭。请问这是怎么回事
- ·捷安特atx770d价格风标2200中轴长度是多少
- ·静安素质拓展训练心得体会哪家好
- ·自行车这个长条气球有什么用？
- ·如何区分阴性食物和阳性食物 – 闹钟健身网
- ·体育英雄那么多，为什么绿瘦产品有哪些选择了跳水梦之队
- ·哪位吧友能比较一下大致相当于和英超赛程表的哪个球员的水
- ·昨天的韩国 限制 上位篮球怎么还没结算，有同路的吗
- ·减脂减脂期饮食计划划表是什么样子的
- ·百里荒露营地需要准备什么，那边现在可以露营吗
- ·资深数码控的世界杯用球是几号看球设备，你会选哪一个
- ·石家庄激光祛斑三天后的照片几次见效
