Oracle索引访问路径因没有新的统计信息而改变

索引访问路径因没有新的统计信息而改变
在生产环境中,同一个查询的执行路径经常变化,甚至在没有任何相关变化的情况下也是如此。你确认没有收集新的统计信息(事实上,你甚至可能已经锁定了统计信息,以防止执行计划的变化)。在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列的情况,优化器仍然采用了跳跃式扫描。数据库管理员往往认为只有前导列值的个数少时,跳跃扫描才是可行的,但“少”是相对的。如果优化器认为跳跃式扫描比全表扫描的成本更低,那么优化器就将选择跳跃式扫描,最终,成本估算起着决定作用并主导优化器的选择。

发表评论

电子邮件地址不会被公开。