oracle执⾏计划不⾛索引的原因总结
在Oracle数据库操作中，为什么有时⼀个表的某个字段明明有索引，当观察⼀些语的执⾏计划确不⾛索引呢？如何解决呢？本⽂我们主要就介绍这部分内容，接下来就让我们⼀起来了解⼀下。
⼀、
不⾛索引⼤体有以下⼏个原因：
你在Instance级别所⽤的是all_rows的⽅式
你的表的统计信息(最可能的原因)
你的表很⼩，上⽂提到过的,Oracle的优化器认为不值得⾛索引。
解决⽅法：
可以修改a中的OPTIMIZER_MODE这个参数，把它改为Rule或Choose，重起数据库。也可以使⽤4中所提的Hint。
不⾛索引的其它原因：
1、建⽴组合索引，但查询谓词并未使⽤组合索引的第⼀列，此处有⼀个INDEX SKIP SCAN概念。
2、在包含有null值的table列上建⽴索引，当时使⽤select count(*) from table时不会使⽤索引。
3、在索引列上使⽤函数时不会使⽤索引，如果⼀定要使⽤索引只能建⽴函数索引。
4、当被索引的列进⾏隐式的类型转换时不会使⽤索引。
如:select * from t where indexed_column = 5，⽽indexed_column列建⽴索引但类型是字符型，这时Oracle会产⽣隐式的类型转换，转换后的语句类似于select * from t where to_number(indexed_column) = 5，此时不⾛索引的情况类似于case3。⽇期转换也有类似问题，如: select * from t where trunc(date_col) = trunc(sysdate)其中date_col为索引列，这样写不会⾛索引，可改写成select * from t where date_col >= trunc(sysdate) and date_col <
trunc(sysdate+1)，此查询会⾛索引。
5、并不是所有情况使⽤索引都会加快查询速度，full scan table 有时会更快，尤其是当查询的数据量占整个表的⽐重较⼤时，因为full scan table采⽤的是多块读，当Oracle优化器没有选择使⽤索引时不要⽴即强制使⽤，要充分证明使⽤索引确实查询更快时再使⽤强制索引。
6、<>
7、like’%dd’百分号在前。
Oracle数据库不能使⽤索引的原因定位
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 statistics for all indexes;" 。 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 为被跟踪应⽤的操作系统进程号。应⽤程序执⾏完成后，⽤命令 tkprof 对该⽂件进⾏分析。
命令⽰例：“ tkprof tracefile outputfile explain=userid/password" 。在操作系统
ORACLE ⽤户下，键⼊“ tkprof ”，会有详细的命令帮助。分析后的输出⽂件 outputfile 中，有每⼀条 PL/SQL 语句的“执⾏计划”、
CPU 占⽤、物理读次数、逻辑读次数、执⾏时长等重要信息。根据输出⽂件的信息，我们可以很快发现应⽤中哪条 PL/SQL 语句是问题的症结所在。
关于Oracle数据库不能使⽤索引的原因定位就介绍到这⾥了，希望本次的介绍能够对您有所帮助。
⼆、
Oracle
索引使⽤（⼩技巧）
虽然索引并不总会快于全表扫描，但是很多时候我们希望Oracle使⽤索引来执⾏某些SQL，这时候我们可以通过index
hints来强制SQL使⽤index.
Index Hints的格式如下:
/
*+ INDEX ( table [index
[index]...] ) */
select /*+ index(t i_t) */ * from t where
username='EYGLE';
当WHERE条件中的字段是类似column1 is null 或者column1 is not
null是，即便column1上⾯本来有索引也不会⽤到
⽐如where column1 =‘aaa’ 是可以⽤到索引的
下⾯的情况都⽤不到索引
column1||column2=‘aaabbb’
Substr(column1,1)=‘aaa’
Column1||’b’ =‘aaab’
Column3 + 1 > :a
Like
的字符串中第⼀个字符如果是‘%’则⽤不到索引
Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’⽤不到
<>也⽤不到索引
有时可以采取适当措施改写后可以⽤到索引
例：column1
有3个值(‘A’,’B’,’C’)，三个值的分布为‘A’ 10%, ‘B’ 80%,’C’ 10%，则column1 <> ‘B’
可以改写为column1 in(‘A’,’C’)
尽管In写法要⽐exists简单⼀些，exists⼀般来说性能要⽐In要⾼的多
join和in哪个查询更快
⽤In还是⽤Exists的时机
当in的集合⽐较⼩的时候，或者⽤Exists⽆法⽤到选择性⾼的索引的时候，⽤In要好，否则就要⽤Exists
例：select count(*) from person_info where xb in (select xb_id from dic_sex);
Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1
from person_info where pid=a.pid and …);
Select * from person_info where
zjhm=3101….；将会对person_info全表扫描
Select * from person_info where zjhm
=‘3101…’才能⽤到索引
假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’⼀天的数据.下⾯的⽅法⽤不到索引
Select * from test where
to_char(dt,’yyyymmdd’) =‘20041010’;
⽽select * from test where dt
>=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) +
1 将会⽤到索引。
如果能不⽤到排序，则尽量避免排序。
⽤到排序的情况有
集合操作。Union ,minus
,intersect等，注：union all 是不排序的。
Order by
Group by
Distinct
In
有时候也会⽤到排序
确实要排序的时候也尽量要排序⼩数据量
，尽量让排序在内存中执⾏，有⽂章说，内存排序的速度是硬盘排序的1万倍。
容易引起oracle索引失效的原因很多：
1、在索引列上使⽤函数。如SUBSTR,DECODE,INSTR等，对索引列进⾏运算.需要建⽴函数索引就可以解决了。
2、新建的表还没来得及⽣成统计信息，分析⼀下就好了
3、基于cost的成本分析，访问的表过⼩，使⽤全表扫描的消耗⼩于使⽤索引。
4、使⽤<>、not in 、not exist，对于这三种情况⼤多数情况下认为结果集很⼤，⼀般⼤于5%-15%就不⾛索引⽽⾛FTS。
5、单独的>、<。
6、like "%_" 百分号在前。
7、单独引⽤复合索引⾥⾮第⼀位置的索引列。
8、字符型字段为数字时在where条件⾥不添加引号。
9、当变量采⽤的是times变量，⽽表的字段采⽤的是date变量时.或相反情况。
10、索引失效，可以考虑重建索引，rebuild online。
11、B-tree索引 is null不会⾛,is not null会⾛，位图索引 is null,is not null  都会⾛、联合索引 is not null 只要在建⽴的索引列（不分先后）都会⾛。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。