索引访问路径因没有新的统计信息而改变
在生产环境中，同一个查询的执行路径经常变化，甚至在没有任何相关变化的情况下也是如此。你确认没有收集新的统计信息（事实上，你甚至可能已经锁定了统计信息，以防止执行计划的变化）。在Oracle Database 11g之前的版本中，基数据反馈功能和自动查询调优功能，如果没有任何东西真正改变（即统计信息，实例参数等），对目前直方图的绑定窥视之类的东西没有起作用，那么执行计划几乎保持不变。然而，尽管你绝对肯定优化器不应该改变任何的执行计划，但它确实改变了。这怎么可能呢，嗯，大多数数据库都是“活体”，由于不断进行的交易数据而不断变化。事实上，如果基础数据发生了重大修改，但没有收集新的统计信息以反映这些变化，因为优化器根据不同访问路径的成本做出决定，而成本可能因为数据的变化而改变，所以执行可能将改变。
如果不随着数据的变化对优化器统计信息进行更新，在某些情况下，优化器将很可能错误地估计基数，也就是预期查询返回的行数。如果应用程序往表中添加了大量数据，而没有收集新的统计信息，你可能会认为，因为优化器不知道有新的数据，所以它会继续使用原来的执行计划（计划目前有效）。实际上，如果基于成本的优化器算错了查询真正的选择性和基数，那么随首时间的推移，它有时会改变其执行计划。在输入数量非常大的新数据后，为了执行相同的查询，优化器可能使用不同的计划，因为它低估了查询现在将检索的行数。如果在加入大量的数据之前，全表扫描是最优策略，那么在加入另外的数据后，优化器可能误以为使用索引效果会更好，而实际上全表扫描可能仍然是最好的方法。
1 使用不等条件
使用不等条件的语句，比如select * from emp where department_id<>10有时可能会导致优化器不使用索引。原因是如果优化器猜测某个查询将从表中选择大多数的值，它往往就不会使用索引。如果是这种情况，对表执行全表扫描可能是更有效的。通常情况下，优化器使用公式(1-(1/num_distinct))* num_rows估算基数。换句话说，如果某个列有1000行，其中不同的值有4个，那么基数为750。我们假设，表中大部分行满足为不等条件指定的值。在这种情况下，你可能会认为优化器会使用索引扫描，因为，毕竟所要检索的是不包含在不等条件中指定的值的所有行。然而，在指定<>操作符时，优化器会简单地忽略任何可能有的索引，即使数据分布不均，且<>条件将导致检索表中很小一部分行也是如此。在这种情况下，优化器只是倾向于执行全表扫描，而不是”正确“地选择索引。
运行这个例子中的查询，结果如下:
SQL> select * from emp where department_id<>10;
105 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 7245 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 105 | 7245 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"<>10)
<>子句导致优化器跳过department_id列上的索引。可以尝试用index提供示强制使用索引，如下所示:
SQL> select /*+ index(emp,dept_idx) */ * from emp where department_id<>10;
105 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 169023637
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 7245 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 105 | 7245 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | DEPT_IDX | 104 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPARTMENT_ID"<>10)
index提示使优化器用全索引扫描替换了全表扫描，虽然这是较好的，但不如索引范围扫描那么好。全索引扫描必须读取包含<>操作符中指定的值的所有叶节点，因此它不是非常有效的方法。（然而，在其他情况下，优化器可能会因为指定index提示而执行索引范围扫描。）指定<>条件的问题是，优化器甚至可能在查询返回表中很小一部分行时也跳过索引，它只是忽略where谓词中出现的列的索引。忽略索引意味着优化器在生成”最优“的计划之前，甚至不会计算索引扫描的成本，它认为这样会节省所有因此（计算索引扫描成本）花费的开销，因为它假设<>条件将导致提取表中很大一部分行。在这样的情况下，可以尝试重写查询以避免使用<>运算符。
注意：无法强制数据库执行并行索引范围扫描。但是，数据库在执行燕行嵌套循环连接时，确实执行了并行索引查找。
这里的唯一选择就是重写查询，以消除不等于子句。如果有多个谓词需要处理，就更有必要这么做。请记住，当指定not in子句时，优化器的行为与指定不等于子句是相同的。在某些情况下，另一个很好的解决方案是用case结构取代不等于谓词。请记住，如果使用case结构，那么还需要与之相匹配的基于函数的索引。
2 使用通配符查询
如果执行一个包含前导通配符搜索的查询，优化器可能会忽略索引而执行全表扫描。例如对于下面的查询:
SQL> select * from emp where last_name like '%hen';
如果匹配模式中的前导字符不是”%“或下划线(_)，优化器更有可能使用索引。like运算符的初始字符使用通配符”%“或”_”，意味着数据库可能必须读取表中大部分行。如果使用索引，也需要访问每个索引块，对索引的读取完成后，可能还需要扫描大多数的表块。在这种情况下，全表扫描可能会更有效。优化器跳过last_name列的索引，因为它必须检查列中的每个值，以确定这些值是否以”hen”值结束。它最后选择了全表扫描，如下面语句的解释计划所示:
SQL> select * from emp where last_name like '%hen';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 345 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME" LIKE '%hen')
如果把上述语句修改为如下所示的语句，优化器就选择了索引扫描。
SQL> select * from emp where last_name like 'hen%';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2522206107
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_LAST_NAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME" LIKE 'hen%')
filter("LAST_NAME" LIKE 'hen%')
请注意，第一个例子使用前导通配符搜索。事实上，为了强制使用索引，在这个例子中，只要把通配符(%)从前导位置向后移动哪怕一个字符的位置(select * from emp where last_name like ‘h%en%)，都会使用优化器使用emp表中的emp_last_name索引。我们经常使用这个例子演示，在搜索字符串中推迟使用通配符不会阻止使用索引。那么，在“%”前有多少字符就将允许使用该索引呢？当额外的字符被添加到搜索字符串中通配符（%）规范前时，优化器自然会预计该数据库将读取更少的索引和表中更少的行，因此它更倾向于选择使用索引。因此，如果搜索“ABC%”，而不是“%ABC”，那么数据库将更有可能使用索引范围扫描，因为它意识到，这时采用全表扫描的成本更高。
3 在谓词中引用空值
假设有个只有两列的表，这两列都允许NULL值。我们还假设，事实上，表中有几行在两列中都是NULL。如果执行以下语句:
SQL> select * from mytable where a is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYTABLE | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A" IS NULL)
数据库的优化器将不会使用此表上的唯一索引，因为假设有一行数据，它的某个唯一索引的所有列都是NULL值，数据库允许把这样的行添加到表中,但不会把它包括在索引中。事实上，即使表上有唯一索引，也可以向表中添加所有列都是NULL值的多少行，因为Oracle数据库认为所有列都是NULL值的两行是不同的，所以认为它们仍然符合唯一性。其结果是该表将比索引有更多的行，因为所有列都是NULL值的行不会被插入到索引中。当执行先前的查询时，数据库忽略了索引，因为该索引不包括所有的列都是NULL值的行。为了避免得出错误的答案，该数据库忽略了索引。
在这个例子中，让数据库使用索引的唯一方法是，确保在此表的两列中至少有一列被定义为
SQL> create table mytab1(a int,b int not null);
Table created.
SQL> create unique index mytab1_idx on mytab1(a,b);
Index created.
SQL> select * from mytab1 where a is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3315145109
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| MYTAB1_IDX | 1 | 26 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A" IS NULL)
如果某行所有的索引列都是空的，Oracle数据库就不会把该行包含到索引中。然而，实际上可以简单地增加另一列到索引中，对NULL值进行索引，
如下所示:
SQL>create index with_null on employees(nullable_column,'1');
4 在查询中包含函数
如果某个查询在where子句中包含索引列上的函数，优化器也会跳过索引。请注意，无论是你在查询中显式地应用了函数，还是数据库在你不知道的情况下隐式地应用了函数，优化器都会跳过索引。下面先讨论显式应用函数。比如，执行以下查询:
SQL>select * from emp where upper(last_name)='CHEN';
在这种情况下，由于upper函数的存在，优化器跳过了last_name列的索引。（第5章介绍了如何使用基于函数的索引来解决这个问题）。如果应用程序需要频繁地把函数应用于索引列，那么最好创建基于函数的索引，以便允许数据库使用索引。
请注意，即使查询没有显式地把函数应用到某个列，在某些情况下它也可能隐式地这样做。例如，如果在表中定义了一个数值型的列，然后通过指定一个字符，而不是一个数值来查询列值，优化器就会忽略索引。也就是说，如果执行语句select * from emp where department_id=’10’,而不是select * from emp where department_id=10，优化器需要在幕后应用to_number函数才能得到答案。再次强调，使用函数意味着优化器将不使用索引。在这种情况下，即使指定了index提示，优化器也可能会执行全索引扫描，但不会执行索引唯一扫描。因为全索引扫描必须扫描整个索引，所以它比索引唯一扫描慢得多。
日期型数据经常会引起隐式转换，从而阻止使用索引。下面的表达式是非常常见的:
SQL> select * from emp where trunc(hire_date)=trunc(sysdate);
在写这样的查询时，人们往往下意识地使用trunc函数。我们把包含时间的日期截断，以消除一天中的时间，往往不考虑查询执行的后果。然而，对Oracle数据库而言，它只是注意到并没有真正对表达式trunc(hire_date)进行索引，而只索引了hire_date列。因此，数据库忽略了索引。
列trunc(hire_date)上的基于函数的索引会使优化器选择访问索引，但还有一种更简单的方法可以解决这个问题，而无需创建基于函数的索引。
只要对sysdate值使用trunc函数，并把等于运算符替换为范围比较运算符（大于或小于），就可以不必在hire_date列上应用trunc函数。也就是说，对于如下的语句:
select * from emp where trunc(hire_date)=trunc(sysdate);
可把它改写为下面这个语句:
select * from emp where hire_date>=trunc(sysdate) and hire_date因为对索引列hire_date移除了trunc函数，所以优化器会使用该列上的索引。
5 跳过索引的前导部分
如果在两个或多个列上有一个复合索引，但没有在查询中使用索引的前导部分，那么优化器很可能会忽略表上的索引，而执行全表扫描。比如，在表mytab1的列(a,b)上有一个索引，a是前导列。如果执行select * from mytab1 where b=99这样的SQL语句，数据库会忽略列(a,b)上的索引，因为它必须在表中的每个单独索引项上检查列a所有可能的值。请注意，如果执行select a,b from mytab1这样的查询语句，优化器更可能使用(a,b)上的索引，因为它意识到这两列都是索引的一部分。因为索引比表更紧凑，而且数据库可以从索引本身得到查询所请求的所有值，所以优化器可能会执行索引快速全扫描。
即使某个查询不含索引的前导部分，但如果一个复合索引的前导列的不同值很少，数据库仍然可以使用索引。在这种情况下，该数据库将执行索引跳跃式扫描，如在第5章中所解释的那样。
在优化器选择了跳跃式扫描时，在谓词中使用的列前可以有多个前导的索引列。我们所见过的案例中，有谓词列前多达6列的情况，优化器仍然采用了跳跃式扫描。数据库管理员往往认为只有前导列值的个数少时，跳跃扫描才是可行的，但“少”是相对的。如果优化器认为跳跃式扫描比全表扫描的成本更低，那么优化器就将选择跳跃式扫描，最终，成本估算起着决定作用并主导优化器的选择。