执行计划中常见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

 

时间： 2024-08-03 15:52:35

执行计划中常见index访问方式(转)的相关文章

执行计划中各字段各模块描述

      在SQL语句的执行计划中,包含很多字段项和很多模块,其不同字段代表了不同的含义且在不同的情形下某些字段.模块显示或不显示,下面的描述给出了执行计划中各字段的含义以及各模块的描述.        有关执行计划中各字段模块的描述请参考: 执行计划中各字段各模块描述        有关由SQL语句来获取执行计划请参考:     使用 EXPLAIN PLAN 获取SQL语句执行计划        有关使用autotrace来获取执行计划请参考:启用 AUTOTRACE 功能       有

Sql Server中的表访问方式Table Scan, Index Scan, Index Seek

  Sql Server中的表访问方式Table Scan, Index Scan, Index Seek 0.参考文献 oracle表访问方式 Index Seek和Index Scan的区别以及适用情况 1.oracle中的表访问方式 在oracle中有表访问方式的说法,访问表中的数据主要通过三种方式进行访问: 全表扫描(full table scan),直接访问数据页,查找满足条件的数据 通过rowid扫描(table access by rowid),如果知道数据的rowid,那么直接通

《Oracle高性能SQL引擎剖析：SQL优化与调优机制详解》一2.5　执行计划中其他信息的含义

2.5 执行计划中其他信息的含义 通过DBMS_XPLAN输出执行计划,除了计划本身外,还可以获得一些其他信息帮助我们进一步分析执行计划及语句性能. 2.5.1 查询块和对象别名 在使用DBMS_XPLAN显示执行计划时,选择'ADVANCED'预定义格式作为参数或者加入'ALIAS'控制字符串,可以在输出中看到以下内容: Query Block Name / Object Alias (identified by operation id): -------------------------

FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素

导读 EXPLAIN的结果中,有哪些关键信息值得注意呢? MySQL的EXPLAIN当然和ORACLE的没法比,不过我们从它输出的结果中,也可以得到很多有用的信息. 总的来说,我们只需要关注结果中的几列: 列名 备注 type 本次查询表联接类型,从这里可以看到本次查询大概的效率 key 最终选择的索引,如果没有索引的话,本次查询效率通常很差 key_len 本次查询用于结果过滤的索引实际长度,参见另一篇分享(FAQ系列-解读EXPLAIN执行计划中的key_len) rows 预计需要扫描的记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题

今天开发的同事找到我,让我评估一个sql语句.因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优. sql语句比较长,是由几个Union连接起来的子查询. xxxxx UNION   SELECT /*+ leading (ar1_creditid_tab ar1_unapplied_credit) use_nl (ar1_creditid_tab ar1_unapplied_credit) */            UNIQUE            0,            MA

浅析SQL SERVER执行计划中的各类怪相

在查看执行计划或调优过程中,执行计划里面有些现象总会让人有些疑惑不解:     1:为什么同一条SQL语句有时候会走索引查找,有时候SQL脚本又不走索引查找,反而走全表扫描?     2:同一条SQL语句,查询条件的取值不同,它的执行计划会一致吗?     3: 同一条SQL语句,其执行计划会变化,为什么     4: 在查询条件的某个或几个字段上创建了索引,执行计划就一定会走该索引吗?     5:同时存在几个索引,SQL语句会走那个索引?      .....................

关于执行计划中的%CPU的含义

今天突然想起前段时间学习的一篇博客,是oaktable的Charles Hooper所写,链接为: https://hoopercharles.wordpress.com/2010/02/19/what-is-the-meaning-of-the-cpu-column-in-an-explain-plan/ 自己也趁机消化了一下.对于执行计划中的 列Cost (%CPU),其中的%CPU的含义很少有人能够说得清楚,于是Charles Hooper写了上面的文章来解释. 对于执行计划的信息都会放入

通过执行计划中的CONCATENATION分析sql问题

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事. 他大体上给我讲了下逻辑,表bl1_rc_rates是千万级数据量的表,autsu_subscriber 是个临时表,里面只有三百多条数据,bl1_activity_history 表的数据量略小,是百万级的.    select distinct hist.entity_id, rc.* from bl1_activity_history hist, bl1_rc_rates rc, au

asp.net开发中常见公共捕获异常方式总结(附源码)_实用技巧

本文实例总结了asp.net开发中常见公共捕获异常方式.分享给大家供大家参考,具体如下: 前言:在实际开发过程中,对于一个应用系统来说,应该有自己的一套成熟的异常处理框架,这样当异常发生时,也能得到统一的处理风格,将异常信息优雅地反馈给开发人员和用户.我们都知道,.net的异常处理是按照"异常链"的方式从底层向高层逐层抛出,如果不能尽可能地早判断异常发生的边界并捕获异常,CLR会自动帮我们处理,但是这样系统的开销是非常大的,所以异常处理的一个重要原则是"早发现早抛出早处理&q