oracle查询不⾛索引的⼀些情况（索引失效）
Oracle建⽴索引的⽬的是为了避免全表扫描，提⾼查询的效率。
但是有些情况下，即使建⽴了索引，但是执⾏写出来的查询还是很慢，然后通过执⾏计划会发现是索引失效导致的（不⾛索引，⾛全表扫描）。所以需要了解⼀下有哪些些情况会导致索引失效，即查询不⾛索引的原因。
在写SQL的层⾯上⼀些骚操作会导致索引失效
没有写WHERE⼦句或查询条件没有建⽴索引
既然没有WHERE⼦句，那么就是查询全部数据了，相当于全表扫描，当然不⾛索引了。
⽽查询条件上没有建⽴索引的话，索引都没有还⾛个⽑索引啊。
WHERE⼦句上没有使⽤索引中的引导列
要使⽤索引，则查询条件中必须包含索引中的引导列。⽐如⼀个复合索引包含A，B，C，D四列，则A为引导列（排在第⼀位置的列）。如果WHERE⼦句中所包含的列是BCD或者BD等情况，则只能使⽤⾮匹配索引扫描。
-- 创建包含A，B，C，D四列的复合索引
CREATE INDEX INDEX_ABCD ON LETTERS(A, B, C, D);
-- 下列语句不会使⽤复合索引
SELECT*FROM LETTERS WHERE B ='b'AND C ='c';
SELECT*FROM LETTERS WHERE B ='b'AND D ='d';
SELECT*FROM LETTERS WHERE C ='c'AND D ='d';
SELECT*FROM LETTERS WHERE B ='b'AND C ='c'AND D ='d';
另外，单独引⽤复合索引⾥排第⼀位置的索引列也会导致索引失效，复合索引必须复合使⽤才能⽣效。
-- 单独使⽤复合索引中的引导列也不会触发复合索引
SELECT*FROM LETTERS WHERE A ='a'
WHERE⼦句中使⽤IS NULL或IS NOT NULL
使⽤判断空或⾮空的条件会导致该索引列失效。
-- COMM列的索引会失效
SELECT*FROM EMP WHERE COMM IS NULL;
WHERE⼦句中使⽤函数
如果没有使⽤基于函数的索引，那么WHERE⼦句中对存在索引的列使⽤函数时，会使优化器忽略这些索引。
-- 在没有建⽴基于函数索引的字段上使⽤了函数，会导致索引失效
SELECT*FROM STUFF WHERE TRUNC(BIRTHDAY) ='2019-04-01'
⾮要这样的话可以通过在索引列上建⽴基于函数的索引来解决问题。
-- 给BIRTHDAY列创建TRUNC函数的函数索引
CREATE INDEX STUFF_BIRTHDAY_FBI_IDX ON STUFF(TRUNC(BIRTHDAY));
这样的话函数索引就起作⽤了，也就能在索引列上使⽤函数了，⽽且这时候你想不⽤函数都不⾏。
但是对于MIN，MAX函数等，Oracle仍然会使⽤索引，因为Oracle对这类聚合分析函数做了相应的优化。
对索引列进⾏运算导致索引失效oracle中trunc函数用法
和使⽤函数同样的，如果使⽤运算，⽐如加减乘除⾮等，也会导致索引失效。
-- 对索引列进⾏了减法运算，导致不⾛索引
SELECT*FROM USERS WHERE AGE -1=23
-- 正确的写法（去除运算）
SELECT*FROM USERS WHERE AGE =24
使⽤前导模糊查询
前导模糊查询的写法是LIKE '%T'，即通配符【%】写在要匹配的字符前⾯，这样的情况下不⾛索引。
-- 前导模糊查询不⾛索引
SELECT*FROM LOVERS WHERE NAME LIKE'%静';
WHERE⼦句中使⽤不等于条件
不等于操作包括：
<>
!=
NOT COL >= ?
NOT COL <= ?
为什么不等于条件会不⾛索引，我们可以⽤常规的思维去推断⼀下。⾸先【不等于】这个概念可以推断成选取结果集中的⼀个⽐较⼤的部分，⽐如我们要出中国⼈⼝中不是姓杨的，要出资深程序员中不会秃头的，都可以认为是要返回结果集中⼀个很⼤的部分。因为Oracle的CBO是闭源的，我们只能推断Oracle会认为既然要返回结果集中很⼤的⼀个部分，不如直接使⽤全表扫描⽽不考虑索引。
⽽且Oracle是建议，对于这些限制条件可以使⽤OR代替，例如COL <> 0可以替换成COL > 0 OR COL < 0。
WHERE⼦句中使⽤NOT IN或NOT EXISTS
使⽤NOT IN或NOT EXISTS也会导致不⾛索引。
事实上NOT IN和NOT EXISTS也可以看做是不等于条件，不⾛索引的原因和上⾯的不等于条件相同。
另外有⼀些⼈说使⽤IN是不⾛索引的，这是不对的，IN是可以⾛索引的，只是可能效率会⽐EXISTS低。
等于和范围索引不会被合并使⽤
SELECT*FROM CARS WHERE COLOR ='yellow'AND TYPE ='B'
在上⾯的查询中，COLOR和TYPE列上都创建了⾮唯⼀索引，在这种查询条件下Oracle并不会合并索引，⽽只会使⽤第⼀个索引，即只有COLOR列上索引会⽣效。
⽐较不匹配类型的数据类型
⽐如SERIAL_CODE是⼀个VARCHAR2类型的字段，在这个字段上建⽴了索引，但是下⾯的语句将会执⾏全表扫描⽽不⾛索引。
SELECT*FROM MATERIALS WHERE SERIAL_CODE =810646874;
为什么呢？因为Oracle中有⼀个字段⾃动进⾏隐式类型转换的机制，会⾃动把类型不匹配的字段进⾏隐式类型转换，相当于：
SELECT*FROM MATERIALS WHERE TO_NUMBER(SERIAL_CODE) =810646874;
可以看出，Oracle优化器⾃动给SERIAL_CODE加上了类型转换函数，这样就限制了索引的使⽤。
所以正确的写法应该是条件和字段类型相匹配：
SELECT*FROM MATERIALS WHERE SERIAL_CODE ='810646874';
表中数据量的多少也会影响索引的使⽤
查询的数量是⼤表的⼤部分，应该是30%以上
如果查询的数量超过⼤表数量的30%，那就不⾛索引了。
对⼩表查询
举个极端的例⼦，表中只有⼀条数据，何必⾛索引呢。⽐如你看⼀本只有⼏页的书，难道你还会去看⽬录吗，给这本书建⽬录都是⼈才了，你还去这本书有没有⽬录岂不是⼈才中的⼈才（你别去上班
了，我建⽬录养你啊）。
索引失效的解决办法
下⾯这些解决办法是基于SQL写得没问题，⽽索引就是不⽣效的情况。
选⽤合适的Oracle优化器
Oracle的优化器有三种，⼀种是基于规则的（RULE），⼀种是基于成本的（COST），还有⼀种是选择性的（CHOOSE）。
在缺省的情况下（未设置），Oracle默认采⽤CHOOSE优化器。为了避免那些不必要的全表扫描（FULL TABLE SCAN），你必须尽量避免使⽤CHOOSE优化器，⽽直接采⽤基于规则或基于成本的优化器。
重建索引
和电脑出了问题的重启试试⼀样，索引出了问题也是可以重建试试的。
ALTER INDEX索引名 REBUILD
强制索引
强制索引是使⽤hint关键字。
正常使⽤的索引突然失效的对应解决办法
有些时候，同样的SQL，之前是能⾛索引的，突然有⼀天不⾛索引了，那么可能是：
1.随着表的增长，WHERE条件出来的数据太多，⼤于15%，导致CBO计算出⾛索引扫描⼤于⾛全表扫描，就会使得索引失效。这种情况⽬前我也不知道该怎么办（好难啊）。
2.统计信息失效。这种情况需要重新搜集统计信息。
3.索引本⾝失效。这种情况就很⽞乎了，只能重建索引试试。
"⼈始终要⾛到某个路⼝，看着⼀路的同伴有些往左⾛，有些往右⾛，只剩下⾃⼰⼀个左顾右盼，不知所措。要么跟着⼤部队往左往右，要么就朝前⾛，绝没有任何退后的可能。"

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