执行计划中常见index访问方式(转)

近期有朋友对于单个表上的index各种情况比较模糊,这里对于单个表上,单个index出现的大多数情况进行了总结性测试,给出了测试结果,至于为什么出现这样的试验结果未做过多解释,给读者留下思考的空间.本篇文章仅仅是为了测试hint对index的影响,而不是说明走各种index方式的好坏.参考: INDEX FULL SCAN vs INDEX FAST FULL SCAN
创建表模拟测试

SQL> create table t_xifenfei as select object_id,object_name from dba_objects;   Table created.     SQL>  create index i_t_object_id on t_xifenfei(object_id);   Index created.   SQL> exec dbms_stats.gather_table_stats( USER , 'T_XIFENFEI' , cascade => true );   PL/SQL procedure successfully completed.   SQL> desc t_xifenfei   Name                                      Null ?    Type   ----------------------------------------- -------- ----------------------------   OBJECT_ID                                          NUMBER   OBJECT_NAME                                        VARCHAR2(128)

TABLE ACCESS FULL

SQL> SET AUTOT TRACE EXP STAT SQL> SELECT OBJECT_ID FROM T_XIFENFEI;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 548923532   -------------------------------------------------------------------------------- | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 | |   1 |  TABLE ACCESS FULL | T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 | --------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3544  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed   SQL> SELECT /*+ INDEX (T i_t_object_id) */ OBJECT_ID FROM T_XIFENFEI;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 548923532   -------------------------------------------------------------------------------- | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 | |   1 |  TABLE ACCESS FULL | T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 | --------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3544  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed

从上面的执行计划中可知,此时走了全表扫描. 由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢？ 这是因为NULL值与索引的特性所决定的.即null值不会被存储到B树索引.因此应该为表 t_xifenfei 的列 object_id 添加 not null 约束.

INDEX FAST FULL SCAN

SQL> alter table t_xifenfei modify (object_id not null );     Table altered.   SQL> SELECT  object_id from t_xifenfei;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2036340805   -------------------------------------------------------------------------------------- | Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |               | 49838 |   243K|    27   (4)| 00:00:01 | |   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 | --------------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3432  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed

INDEX FAST FULL SCAN:当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操作.对于索引的分支结构只是简单的获取,然后扫描所有的叶结点.其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序.INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件.

INDEX RANGE SCAN

SQL> select object_id from t_xifenfei where object_id<10;   8 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2197008162   ---------------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               |     2 |    10 |     2   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| I_T_OBJECT_ID |     2 |    10 |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------       1 - access( "OBJECT_ID" <10)     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets            3  consistent gets            0  physical reads            0  redo size          499  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            8  rows processed   SQL> select /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t where object_id<10;   8 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2036340805   -------------------------------------------------------------------------------------- | Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |               |     2 |    10 |    27   (4)| 00:00:01 | |*  1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID |     2 |    10 |    27   (4)| 00:00:01 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------       1 - filter( "OBJECT_ID" <10)     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets          118  consistent gets            0  physical reads            0  redo size          499  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            8  rows processed 1 这里可以看出index_ffs已经生效,但是对于这样的情况hint index_ffs效率一般来说不会太高. <br> <strong> INDEX FULL SCAN</strong> 1 SQL> SELECT /*+ INDEX (T i_t_object_id) */ object_id  from t_xifenfei t;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 431110666   ---------------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 | |   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 | ----------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3426  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed

INDEX FULL SCAN:完全按照索引存储的顺序依次访问整个索引树.当访问到叶结点之后,按照双向链表方式读取相连节点的值.换言之,对于索引上所有的数据是按照有序的方式来读取的.如果索引块没有在高速缓存中被找到时,则需要从数据文件中单块进行读取.对于需要读取大量数据的全索引扫描而言,这将使其变得低效.INDEX FULL SCAN使用single read,故产生db file sequential reads事件.新版的Oracle支持db file parallel reads方式.
HINT INDEX不会使用INDEX FAST FULL SCAN功能.

INDEX列ORDER BY

SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id ;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 431110666   ---------------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 | |   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 | ----------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3426  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed   SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id  desc ;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2808014233   -------------------------------------------------------------------------------------------- | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |               | 49838 |   243K|   113   (2)| 00:00:02 | |   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 | --------------------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets         3427  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)        49838  rows processed   SQL> SELECT  /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t order by object_id;   49838 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2527678987   ----------------------------------------------------------------------------------------------- | Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |               | 49838 |   243K|       |   185   (4)| 00:00:03 | |   1 |  SORT ORDER BY        |               | 49838 |   243K|  1192K|   185   (4)| 00:00:03 | |   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|       |    27   (4)| 00:00:01 | -----------------------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets          117  consistent gets            0  physical reads            0  redo size       721203  bytes sent via SQL*Net to client        36927  bytes received via SQL*Net from client         3324  SQL*Net roundtrips to / from client            1  sorts (memory)            0  sorts (disk)        49838  rows processed

对于index 列排序,默认情况下会使用INDEX FULL SCAN/INDEX FULL SCAN DESCENDING而不选择使用INDEX FAST FULL SCAN,因为INDEX FAST FULL SCAN获得数据后,还需要做一次SORT ORDER BY操作

INDEX FAST FULL SCAN+SORT AGGREGATE

SQL> SELECT  count (object_id) FROM T_XIFENFEI;     Execution Plan ---------------------------------------------------------- Plan hash value: 3095383276   ------------------------------------------------------------------------------- | Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |               |     1 |    27   (4)| 00:00:01 | |   1 |  SORT AGGREGATE       |               |     1 |            |          | |   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |    27   (4)| 00:00:01 | -------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets          117  consistent gets            0  physical reads            0  redo size          421  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            1  rows processed   SQL> SELECT  /*+ INDEX (T i_t_object_id) */ count (object_id) FROM T_XIFENFEI t;     Execution Plan ---------------------------------------------------------- Plan hash value: 3079973526   -------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               |     1 |   113   (2)| 00:00:02 | |   1 |  SORT AGGREGATE  |               |     1 |            |          | |   2 |   INDEX FULL SCAN| I_T_OBJECT_ID | 49838 |   113   (2)| 00:00:02 | --------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets          111  consistent gets            0  physical reads            0  redo size          421  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            1  rows processed

sort aggregate通常发生在使用一些聚合函数的时候,sum(),avg(),min(),max(),count()等等,实际上sort aggregate不做真正的sort,并不会用到排序空间,而是通过一个全局变量＋全表或全索引扫描来实现.这样的操作在默认情况下使用INDEX FAST FULL SCAN

INDEX FULL SCAN (MIN/MAX)

SQL> SELECT  max (object_id) FROM T_XIFENFEI;     Execution Plan ---------------------------------------------------------- Plan hash value: 2939893782   -------------------------------------------------------------------------------------------- | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |               |     1 |     5 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          | |   2 |   INDEX FULL SCAN ( MIN / MAX )| I_T_OBJECT_ID | 49838 |   243K|     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets            2  consistent gets            0  physical reads            0  redo size          419  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            1  rows processed   SQL>  SELECT /*+ index_ffs(t i_t_object_id) */ max (object_id) FROM T_XIFENFEI t;     Execution Plan ---------------------------------------------------------- Plan hash value: 2939893782   -------------------------------------------------------------------------------------------- | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |               |     1 |     5 |    27   (4)| 00:00:01 | |   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          | |   2 |   INDEX FULL SCAN ( MIN / MAX )| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 | --------------------------------------------------------------------------------------------     Statistics ----------------------------------------------------------            1  recursive calls            0  db block gets            2  consistent gets            0  physical reads            0  redo size          419  bytes sent via SQL*Net to client          385  bytes received via SQL*Net from client            2  SQL*Net roundtrips to / from client            0  sorts (memory)            0  sorts (disk)            1  rows processed

对于这样的查询INDEX FULL SCAN (MIN/MAX)明显是最优,但是此处奇怪的是使用了index_ffs提示无效,如果有知道的朋友,麻烦告知原因.

http://www.xifenfei.com/2968.html