二十、oracle通过复合索引优化查询及不走索引的8种情况

1. 理解ROWID
　　ROWID是由Oracle自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储ROWID的值；你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。
SELECT t.rowid,t.* FROM DM_COMM_PREM_LIST t where LIST_ID= '3106355531';

2. SQL优化器优化方式
1）基于规则的优化器RBO（Rule-Based Optimization）
　　RBO有严格的使用规则，只要按照这套规则去写SQL语句，无论数据表中的内容怎样，也不会影响到你的执行计划；换句话说，RBO对数据“不敏感”，它要求SQL编写人员必须要了解各项细则；RBO一直沿用至ORACLE 9i，从ORACLE 10g开始，RBO已经彻底被抛弃。
2）基于成本的优化器CBO（Cost-Based Optimization）
       CBO是一种比RBO更加合理、可靠的优化器，在ORACLE 10g中完全取代RBO；CBO通过计算各种可能的执行计划的“代价”，即COST，从中选用COST最低的执行方案作为实际运行方案；它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择，也就是对数据“敏感”。

3. 基于SQL优化器创建有效复合索引
3.1）INDEX SKIP SCAN（复合索引之索引跳跃扫描）
    Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，
这时候就使用的INDEX SKIP SCAN;
3.2）什么时候会触发 INDEX SKIP SCAN
前提条件：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作为条件，并且优化器模式为CBO时当Oracle发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；
例如：
    假设表emp有ename（雇员名称）、job（职位名）、sex（性别）三个字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的复合索引；因为性别只有 '男' 和 '女' 两个值，所以为了提高索引的利用率，Oracle可将这个复合索引拆成 ('男', ename, job)，('女', ename, job)这两个复合索引；当查询 select * from emp where job = 'Programmer' 时该查询发出后：    Oracle先进入sex为'男'的入口，这时候使用到了 ('男', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目；再进入sex为'女'的入口，这时候使用到了 ('女', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目；最后合并查询到的来自两个入口的结果集。

3.3）创建满足索引跳跃扫描条件的复合索引(含日期字段)后，但是查看执行计划却发现并未走复合索引
如：创建索引跳跃扫描 create index IDX_DM_FINISHTIME_ORGANID on DM_COMM_PREM_LIST (FINISH_TIME,ORGAN_ID) ;
查询执行计划如下图，发现未会走复合索引


如何解决此问题：修改日期设定的格式T1.FINISH_TIME >= TO_DATE('2017-06-01', 'yyyy-mm-ddhh24miss') AND       T1.FINISH_TIME <= TO_DATE('2017-06-23', 'yyyy-mm-ddhh24miss');通过将传入参数格式化成对应yyyy-mm-ddhh24miss格式字符串，这样由Oracle将字符串转成Date类型，就很顺利的走索引区间扫描。效果如下：

参看博文：http://programdolt.iteye.com/blog/1186690

4.不走索引的原因，大概有如下8种：
1）建立组合索引，但查询谓词并未使用组合索引的第一列，此处有一个INDEX SKIP SCAN概念。
2）在包含有null值的table列上建立索引，当时使用select count(*) from table时不会使用索引。
3）在索引列上使用函数时不会使用索引，如果一定要使用索引只能建立函数索引。
如：Where条件中对字段增加处理函数将不使用该列的索引
select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)
select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)
4）当被索引的列进行隐式的类型转换时不会使用索引。

示例1:

select * from t where indexed_column = 5，而indexed_column列建立索引但类型是字符型，这时Oracle会产生隐式的类型转换，转换后的语句类似于select * from t where to_number(indexed_column) = 5，此时不走索引的情况类似于case3

示例2：日期转换也有类似问题
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’百分号在前
8）not in ,not exist

oracle中的索引扫描共计5种，本章只介绍INDEX SKIP SCAN（索引跳跃扫描），其他四种索引扫描会在另外章节总结。
INDEX UNIQUE SCAN（索引唯一扫描）
INDEX RANGE SCAN（索引范围扫描）
INDEX FULL SCAN（索引全扫描）
INDEX FAST FULL SCAN（索引快速扫描）
参看博文：
https://www.cnblogs.com/sthinker/p/6080307.html
https://www.cnblogs.com/Dreamer-1/p/6076440.html

转载于:https://www.cnblogs.com/jiarui-zjb/p/9551189.html