执行计划中常见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
最新文章
- JavaScript判断各浏览器CSS前缀的两种方式
- JQuery:JQuery的尺寸
- Project Euler 108:Diophantine reciprocals I 丢番图倒数I
- 命名空间“System.Web.Mvc”中不存在类型或命名空间名称“Ajax”(是否缺少程序集引用?)
- 想做一个整合开源安全代码扫描工具的代码安全分析平台 - Android方向调研
- Java操作XML文件 dom4j 篇
- JUnit4单元测试基础篇
- Ansible Lookup
- 与64位版本的Windows不兼容,masm运行不了
- echarts_部分图表配置_dataZoom精确控制显示数据数量
- Solve Error: MissingSchemaError: Schema hasn&#39;t been registered for model ";YourModel";.
- Kali学习笔记38:文件上传漏洞
- 普通PC安装ESXi5.5以及以上的方法
- Expm 1_3 数组中逆序对个数问题
- 服务端工程师入门与进阶 Java 版
- 在Windows下食用Linux工作环境(WSL+cmder)
- ListView与SimpleAdapter(三)
- Oracle AMERICAN改成简体中文
- IDEA15使用maven编译scala和java
- 销售人员的分析,也可以用类似RFM的思路吗?
热门文章
- [转] git config命令使用第一篇——介绍,基本操作,增删改查
- [转] Python特殊语法:filter、map、reduce、lambda
- 趣谈iOS运行时的方法调用原理
- Java序列化之Serializable
- linux下vi命令笔记
- Java多线程——其他工具类CyclicBarrier、CountDownLatch和Exchange
- C# 面向对象编程的继承性-多继承
- tomcat发布项目时,空文件夹未发布成功
- iOS CGContextRef 画图小结
- Vijos1834 NOI2005 瑰丽华尔兹 动态规划 单调双端队列优化