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

转载 2015年07月12日 23:24:55

文章来自:http://www.xifenfei.com/2968.html

SQL>  create table test as select object_id,object_name from dba_objects;
 create table test as select object_id,object_name from dba_objects;

Table created.
SQL>  create index i_t_object_id on test(object_id);


Index created.
SQL>  exec dbms_stats.gather_table_stats(USER,'TEST',cascade=>true);


PL/SQL procedure successfully completed.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)


table access full

SQL> SET AUTOT TRACE EXP STAT
SQL>  SELECT OBJECT_ID FROM test;

72516 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72516 |   354K|    95   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST | 72516 |   354K|    95   (0)| 00:00:02 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        128  recursive calls
          0  db block gets
       5176  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      72516  rows processed


SQL> select /*+ index(T i_t_object_id) */ object_id from test;

72516 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72516 |   354K|    95   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TEST | 72516 |   354K|    95   (0)| 00:00:02 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5160  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72516  rows processed

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


  INDEX FAST FULL SCAN

SQL> alter table test modify(object_id not null);


Table altered.


SQL> SELECT  object_id from  test;


72516 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805


--------------------------------------------------------------------------------
------


| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time
     |


--------------------------------------------------------------------------------
------


|   0 | SELECT STATEMENT     |               | 72516 |   354K|    45   (0)| 00:0
0:01 |


|   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 72516 |   354K|    45   (0)| 00:0
0:01 |


--------------------------------------------------------------------------------
------






Statistics
----------------------------------------------------------
        128  recursive calls
          0  db block gets
       5010  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      72516  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 test where object_id<10;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2197008162

--------------------------------------------------------------------------------
--

| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT |               |     8 |    40 |     2   (0)| 00:00:01
 |

|*  1 |  INDEX RANGE SCAN| I_T_OBJECT_ID |     8 |    40 |     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
        629  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


INDEX FULL SCAN

SQL>    select /*+ index(T i_t_object_id) */ object_id from test t;

72516 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 431110666

----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 72516 |   354K|   162   (0)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 72516 |   354K|   162   (0)| 00:00:02 |
----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4986  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72516  rows processed


INDEX列ORDER BY

SQL>     SELECT OBJECT_ID FROM Test order by object_id ;

72516 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 431110666

----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 72516 |   354K|   162   (0)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 72516 |   354K|   162   (0)| 00:00:02 |
----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4986  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72516  rows processed

SQL>      SELECT OBJECT_ID FROM Test order by object_id desc;

72516 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2808014233

--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               | 72516 |   354K|   162   (0)| 00:00:02 |
|   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 72516 |   354K|   162   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4987  consistent gets
          0  physical reads
          0  redo size
    1324315  bytes sent via SQL*Net to client
      53697  bytes received via SQL*Net from client
       4836  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72516  rows processed

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


SQL>      SELECT  max(object_id) FROM Test;


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 |     1 |     5 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        523  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








常见的执行计划步骤

1.表访问路径: TABLE ACCESS FULL zhim
  • u012457058
  • u012457058
  • 2014年11月09日 13:34
  • 315

分析SQL Server 给出的missing index建议

测试基于SQL Server 2008  AdventureWorks2008,TablePerson.Person.表中已经存在的索引定义:   CREATE NONCLUSTERED INDEX ...
  • SmithLiu328
  • SmithLiu328
  • 2013年12月25日 20:35
  • 1618

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

Sql Server中的表访问方式Table Scan, Index Scan, Index Seek 0.参考文献 Table Scan, Index Scan, Index S...
  • sophie_lovey
  • sophie_lovey
  • 2014年03月16日 17:33
  • 734

PostgreSQL(三)索引&执行计划

PostgreSQL(三)索引&执行计划
  • kk185800961
  • kk185800961
  • 2017年11月08日 10:54
  • 555

查看sql执行计划时的表连接方式

查看sql执行计划时的表连接方式 在查看sql执行计划时,我们会发现表的连接方式有多种,本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。 一、连接方式:     嵌套循环(Ne...
  • li19236
  • li19236
  • 2014年11月25日 18:11
  • 282

mysql 执行计划type类型及sql优化原则介绍

type:     连接类型     system          表只有一行     const            表最多只有一行匹配,通用用于主键或者唯一索引比较时     eq_ref  ...
  • q936889811
  • q936889811
  • 2017年05月20日 14:24
  • 329

查看执行计划的几种方式总结

总结了执行计划的几种方式 1、autotrace @?/rdbms/admin/utlxplan grant all on plan_table to public; @?/sqlplus/admi...
  • u013820054
  • u013820054
  • 2014年08月30日 10:16
  • 498

Oracle 常见的执行计划步骤(explain结果的Description数据参考)

在PL/SQL中 执行explain plain for  select ... 查看Description 数据参考:   Oracle 常见的执行计划步骤   ...
  • truelove12358
  • truelove12358
  • 2017年04月20日 18:05
  • 575

MySQL高级 之 explain执行计划详解

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。explain执行计划包含的信息其中最重要的字段为:id、...
  • wuseyukui
  • wuseyukui
  • 2017年05月09日 22:55
  • 928

并行查询的执行计划解读

查看表的并行度     V$pq—systat      V$pq—sesstat   参数cpu_count   并行操作间关系(执行计划中in-out部分) p-s并行发送数据给串行 p-p(有...
  • haiross
  • haiross
  • 2013年12月19日 10:02
  • 2795
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:执行计划中常见index访问方式
举报原因:
原因补充:

(最多只允许输入30个字)