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列的情况,优化器仍然采用了跳跃式扫描。数据库管理员往往认为只有前导列值的个数少时,跳跃扫描才是可行的,但“少”是相对的。如果优化器认为跳跃式扫描比全表扫描的成本更低,那么优化器就将选择跳跃式扫描,最终,成本估算起着决定作用并主导优化器的选择。

Oracle优化器忽略索引的原因

优化器忽略索引的原因
创建索引永远不能保证优化器在评估执行计划时一定会使用索引。如果某个查询从表中选择高比例的行,优化器可能觉得使用全表扫描而不是索引扫描,能更快地得到结果。请记住,数据库使用索引时,它首先查找索引获得rowid,然后使用这些rowid来检索所请求的行。如果查询从表中选择很大比例的行,从而导致要读取表中很大比例的块,那么数据库可能执行全表扫描来避免既读取索引又读取表,因为后者的成本可能比仅仅扫描一次表更高。

优化器是否使用索引取决于多种因素,在本章以下各节将分别对它们进行解释。

1 不同的行数
选择全表扫描或索引扫描的另一关键决定因素是,表中与给定查询谓词相匹配的不同行数占表中总行数的比率。通过查询dba_tables视图的num_rows列可以得到某个表的行数。同样,也可以通过查询dba_tab_columns视图的num_distinct列,得到任意列中不同值的数量。num_rows列与num_distinct列的值越接近,优化器越有可能倾向于访问该列的索引而非执行全表扫描,换句话说,索引的选择性越高,数据库就越有可能使用它。

索引的选择性可能会对数据库是否使用索引产生最大的影响。选择性是指每个列值有多少个不同的值。如果索引是非常有选择性的,那么每个索引条目只有很少几行。另外,如果索引的选择性不强,每个索引条目就会有许多行。

请记住,优化器用列的选择性乘以页块(leaf blocks)的统计数量,来估算在访问索引期间,数据库必须读取多少个索引块。虽然事实上,大多数情况下,高选择性的列确实会使用索引,但并不总是如此,因为使用索引还是全表扫描的最终决定因素是块的选择性。

在Oracle Database 10g版本(但不是在11g版本)中,使用dbms_stats.auto_sample_size常量估计dbms_stats包使用的行数,可能会导致对不同值的数量(NDV)产生错误的估计。如果表很大,而且有相当数量的数据是偏态的,往往就会发生这种情况。因此最好对样本大小使用自己的估计,以获得更准确的NDV值。

2 索引聚簇因子
查询dba_indexes视图的clustering_factor列,可以找到索引的聚簇因子的值。聚簇因子用来衡量表中行的有序程度,这种有序程度是与索引的行相比较而言的。如果聚簇因子接近表中的行数,行就有可能是非常随机地排列的,这种情况下,一个索引块的索引条目对应的数据行不太可能位于相同的数据块中。表的选择性(通过过滤)乘以索引聚簇因子,决定了通过索引访问表的成本。这部分计算中使用的实际上是表的选择性。虽然大部分时间,索引的选择性(乘以leaf_blocks,已在上一节讨论)和表的选择性是相同的,计算它实际上是为了确定需要访问表中的多少数据块。通常情况下,我们都假定索引聚簇因子能保证随机性,但事实并非如此。例如,虽然索引中的每行只指向两个不同的块,但条目是按rowid排序为块1,块2,块1,块2……这种(交替排列的)情况会怎么样呢? 如果索引有10000个条目,聚簇因子是10000,但实际上,将仅访问2个块。因此,聚簇因子的计算不能像大多数人认为的那样,保证数据排列的随机性。

在一个具有“好”的聚簇因子的索引中,具体索引叶块中的索引值指向分布在相同数据块中的行。另一方面,在一个具有“坏”的聚簇因子,它可以用更少的I/O读取数据。而具有相同数量的数据但组织得很差的索引,将需要更大的I/O数量,从一组更多的数据块中读取数据。在这种情况下,关键是要注意,如果每个表只有一个索引,那么它的组织可能是理想的,实际上,这只有在表中的数据按特定的顺序加载时才真正成立。举例来说,或许表是按order_date列的顺序加载的。在这种情况下,order_date上索引的聚簇因子将和表中块的数量几乎相同。但是,因为该表的存储只能按一种顺序排列,所以其他所有索引的顺序都将是“欠优化”的。因此,总而言之,“好”与“坏”更是相对而不是绝对的。这可能就是为什么使用索引的成本计算,由包括表和索引选择性的多个元素组成,以使某一个组成部分不会占非常大的比重。

一条经验法则是:良好的聚簇因子接近表的块数,而糟糕的聚簇因子接近表的行数。

除了这里讨论的两个因素,多块读取数的值对索引使用也有影响。数据库文件多块读取计数(db_file_multiblock_read_count)的值越高,从优化器的角度来看,全表扫描的成本就越低。

Oracle在索引和表扫描之间选择

在索引和表扫描之间选择
用户常常对此感到困惑,为什么在他们认为优化器应该使用索引时,它却选择全表扫描。本章后面的一节,介绍了优化器优先选择全表扫描而不是访问索引的几种情况。不过,在深入介绍这些情况之前,首先来了解一下基于成本的优化器工作的原理。

基于成本的优化器的任务是,从一组可能的计划中选择最佳或最优的执行计划。基于成本的优化器首先利用表或索引的行数,每列的不同值的数量以及更多其他信息,来估计可供选择的执行计划的成本。然后,它采用成本最低的执行计划。

出于演示的目的,让我们集中注意力研究确定查询成本最重要的因素之一:表的总行数和优化器需要从该表中读取的行数。几乎可以肯定,如果表的体积非常小,那么优化器将把它完全读入。假定某个表包含10000000(1000万)行,而表使用了100000个表块,因为每个块平均包含约100行。现在,在此表上创建索引,大约需要20000个叶块来存储每个索引列的索引条目。索引比表需要的块更少,因为它只保存一个列的值(和相关表列的rowid)。假设该索引的高度为3,这意味着它的blevel为2,并且每个索引列有100个不同的值,这些值的分布是均匀的。因此,该索引的每个索引值将出现100000次(1000万除以100)。下面用如下测试查询来演示优化器如何在多个执行计划中做选择:

SQL>select * from test_table where test_code='ABCDE';

优化器选反的到底是索引扫描还是全表扫描?为什么?让我们先来分析索引访问的成本。由于test_code列上的索引在所有可能值中是均匀分布的,优化器需要从test_code列上索引中的100个不同值里选择一个,这相当于索引数据的百分之一。为此,数据库需要先读根块和分支块(在这个例子中,blevel=2)。因此,优化器首先记录读取这两个块的成本。下一步,数据库读取索引叶块的1%,这相当于(20000 *0.01)=200个叶块。因此,对于索引读取,就需要访问202个索引块。

因为查询请求了表中的所有列值,所以接下来数据库必须读表中的行。在这里,关键的变量是该索引的聚簇因子,即表中索引列的值的聚集情况。索引列的值聚集程序越高,读所有必要的表行需要访问的块就会越少。例如,聚簇因子是所有可能值中最差的。

这意味首它与表中的行数(10000000)几乎相同。对于表中包含100行的100000个数据块中的每一个,数据库选择1行选择10,或从每个数据块选择1%。因此,访问表中数据的总成本将是选择性乘以聚簇因子,0.01*10000000=100000。因此,以索引为基础的读取操作的近似成本是,202个索引块访问加上100000个表块访问。得到100202块的巨大总成本。

在涉及全表扫描的成本时,请记住,全表扫描使用多块读,而不像索引的读取总是使用单块I/O。在这个例子中,假定每个表块包含100行,在一次全表扫描中数据库需要扫描大约100000个数据块。假设multi_block_read_count的值被设置为10。届时数据库将必须执行共100000/10次读取,即10000次读取。如果想要更精确地计算,也可以在总数中添加段头块的读取,因此它的值是10001。

显然,在这种情况下,甚至在假定可能出现最差的索引聚簇因子后,全表扫描的成本还是要低得多(10001次块读取与索引访问的100202次块读取相比)。此外,还可以并行执行全表扫描,使它执行得更快。请注意,在前面的例子中,查询只获取了一个大表的1%的数据,但全表扫描成本要低得多。这个简单的演示表明,优化器选择索引或全表扫描,并不只是依赖于查询需要检索的行占全行数据的百分比。相反,它还依赖于其他关键因素,如数据的分布,表和叶块的数量,表块中行的平均数量,索引叶块中叶条目的平均数量,该索引的聚簇因子和多块读取计数(multi_block_read_count)的大小。在优化器决定究竟使用索引还是全表扫描时,想要完全依靠某种神奇的比率(即查询所应检索的行与总行数的百分比,如1%,5%,10%,25%或50%)来决策是根本不现实的。这个简单的例子表明,即使查询要检索的行数只占某个表总行数很小的一部分(1%)时,优化器也可能执行全表扫描。

Oracle如何避免使用索引

如何避免使用索引
一般而言,人们更倾向于寻找一些方法,让成本优化器使用所创建的索引,而不是让它忽略现有的索引。不过,有些时候,你想要做的恰恰是:让优化器不使用索引。

1 在任何情况下都不使用某个索引
例如,在处理某个选择性不强的索引时,有可能希望不使用该索引。这是因为如果某个索引没有足够的选择性,扫描整个基础表有时会更有效。

如果SQL语句包含一个结构,如包括某个索引列的where子句,优化器可能会使用该列上的索引。为了阻止这种情况的发生,可以在查询语句中指定no_index提示,让优化器不得使用某个索引。例如:

SQL> select /*+ no_index(emp dept_idx) */ * from emp where department_id=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    69 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    69 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPARTMENT_ID"=10)

这个例子列出了想要让优化器忽略的具体索引。如果在表上有其他索引,优化器仍然会考虑使用那些索引。

除了指定单个索引,也可以列出一组优化器必须忽略的索引。如果只是指定no_index提示,而没有列出任何索引,优化器将忽略你指定的表的所有索引。与在本章后面将会介绍的index提示一样,no_index提示适用于B树索引,基于函数的索引,聚簇索引和域索引。

2 只避免快速扫描
no_index_ffs提示可以用来指示优化器避免对某个索引执行索引快速全扫描。请注意,与index提示的情况一样,必须在指定这个提示的同时指定特定的索引名字。例如:

SQL> select /*+ no_index_ffs(test test_i1) */   c from test;

同样,也可以使用no_index_ss提示来告诉优化器排队表中具体索引的跳跃式扫描。

3 强制表扫描
另一种避免使用索引的方法是从相反的方向解决问题,即明确要求执行表扫描。使用full提示指示优化器选择全表扫描而不是索引扫描。下面是如何指定full提示的例子:

SQL> select /*+ full(emp ) */ * from emp where department_id=10;

Oracle全索引扫描

全索引扫描
全索引扫描是读取给定索引中所有条目的操作。从这个意义上说,全索引扫描类似于全表扫描。与首先做全表扫描,然后再对数据排序相比,全索引扫描是一种很好的替代方法。Oracle数据库在下面任何一种情况下都可能使用全索引扫描。
.查询需要排序合并连接(sort merge join):查询所引用的所有列必须都在索引中存在,且前导索引列的顺序也必须与查询中指定的列顺序相同。
.查询包含order by子句:子句中的所有列必须都在该索引中存在。
.查询包含group by子句:索引和group by子句必须包含相同的列,但不要求它们的顺序一定相同。

下面的例子显示数据库如何利用全索引扫描操作检索数据,而无需执行排序操作。因为索引已经排序,所以使用全索引扫描可以不必执行排序操作。全索引扫描读取单个个数据块,而不执行多块读取操作。

SQL> select * from test where c<30000 order by c;

29999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2983339933

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 29999 |   292K|    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    | 29999 |   292K|    26   (0)| 00:00:01 |
|*  2 |   INDEX FULL  SCAN          | TEST_I1 | 29999 |       |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"<30000)

在此查询中,数据库首先执行全索引扫描,然后通过索引rowid操作执行表访问。这是因为查询请求了除索引列外的其他列(select * from …)。然而,如果查询只请求了索引列并使用了order by子句,数据库将跳过表访问,只通过访问索引得到数据,无需读取表中的值。

索引快速全扫描
当索引本身包含查询中指定的所有列时,Oracle数据库执行索引快速全扫描代替全表扫描。请注意,在下面的例子中,检索数据只用了索引快速全扫描操作,且根本没有访问表本身:

SQL> select   c from test where c<10000;

9999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3298034341

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  9999 | 49995 |   123   (1)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| TEST_I1 |  9999 | 49995 |   123   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C"<10000)

请注意,不像全索引扫描,索引快速全扫描使用多块读操作来读取索引。因此,既因为多块的I/O,又因为这种类型的扫描可以像全表扫描那样并行运行,所以这种类型的扫描往往更快。

Proudly powered by WordPress | Indrajeet by Sus Hill.