当前位置： 首页 > news >正文

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

news 来源：原创 2024/4/29 9:56:21

近期有朋友对于单个表上的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

 

转载于:https://www.cnblogs.com/softidea/p/3949797.html

相关文章：

  • 下一代Asp.net开发规范OWIN（1）—— OWIN产生的背景以及简单介绍
  • Sql语句执行顺序
  • Eclipse Java注释模板设置详解
  • 利用泛型减少Dao方法的数量
  • curl 浏览器模拟请求实战
  • 【BZOJ】1673: [Usaco2005 Dec]Scales 天平（dfs背包）
  • BM和KMP字符串匹配算法学习
  • MySql数据库3【优化1】表的优化
  • PHP学习路线图
  • JavaScript Cookie
  • 大数据时代，统计学方法有多大的效果？
  • 第三章：推荐系统冷启动与CB
  • 再学 GDI+[29]: TGPPen - 自定义复合画笔 - SetCompoundArray
  • WinAPI: PolyBezierTo - 绘制贝塞尔线(更新当前位置)
  • Delphi 与 DirectX 之 DelphiX(44): TDIB.DoAddColorNoise();
  • 【跃迁之路】【641天】程序员高效学习方法论探索系列（实验阶段398-2018.11.14）...
  • angular学习第一篇-----环境搭建
  • classpath对获取配置文件的影响
  • Iterator 和 for...of 循环
  • Java深入 - 深入理解Java集合
  • Python3爬取英雄联盟英雄皮肤大图
  • Wamp集成环境 添加PHP的新版本
  • 测试开发系类之接口自动化测试
  • 道格拉斯-普克 抽稀算法 附javascript实现
  • 前端性能优化——回流与重绘
  • 如何学习JavaEE,项目又该如何做？
  • 一、python与pycharm的安装
  • 一道面试题引发的“血案”
  • MyCAT水平分库
  • 小白应该如何快速入门阿里云服务器,新手使用ECS的方法 ...
  • # 数论-逆元
  • #我与Java虚拟机的故事#连载05：Java虚拟机的修炼之道
  • （2.2w字）前端单元测试之Jest详解篇
  • （Mac上）使用Python进行matplotlib 画图时，中文显示不出来
  • （初研） Sentence-embedding fine-tune notebook
  • (附源码)计算机毕业设计ssm基于Internet快递柜管理系统
  • (附源码)计算机毕业设计SSM疫情社区管理系统
  • （含react-draggable库以及相关BUG如何解决）固定在左上方某盒子内（如按钮）添加可拖动功能，使用react hook语法实现
  • （论文阅读40-45）图像描述1
  • （三十五）大数据实战——Superset可视化平台搭建
  • ***linux下安装xampp,XAMPP目录结构（阿里云安装xampp）
  • .Net Web项目创建比较不错的参考文章
  • .NET 解决重复提交问题
  • .net反编译的九款神器
  • @html.ActionLink的几种参数格式
  • @selector(..)警告提示
  • [1]-基于图搜索的路径规划基础
  • [2016.7 day.5] T2
  • [20180224]expdp query 写法问题.txt
  • [BZOJ 3282] Tree 【LCT】
  • [C++][数据结构][算法]单链式结构的深拷贝
  • [cb]UIGrid+UIStretch的自适应
  • [gdc19]《战神4》中的全局光照技术
  • [java] 23种设计模式之责任链模式
  • [java]删除数组中的某一个元素