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








测试6——观察Optimizer_index_cost_adj 对执行计划的影响

在基于cbo的优化器模式下面,oracle会把sql语句的各种执行计划的成本做比较,取其最小的作为其最优执行计划,同时,在决定是用index scan 还是full table scan 访问...

ORACLE:INDEX FULL SCAN--99%是最烂的执行计划

原sql: SELECT * FROM ( SELECT c.id, c.username, c.keyword, ...

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

在PL/SQL中 执行explain plain for  select ... 查看Description 数据参考:   Oracle 常见的执行计划步骤   ...

Oracle查看执行计划的几种方式

查看SQL执行计划的几种方法: 一、 利用autotrace查看执行计划 注意:autotrace所查询的执行计划并不是真实的执行计划(这个计划是从PLAN_TABLE中来的),是CBO预估的 ...
  • moses19
  • moses19
  • 2016年12月27日 11:00
  • 384

oracle 执行计划获取的几种方式

1.set autotrace on  相信这种方法是最简单的,也是最常用的一样方法,这种方法经常用到分析一条SQL,这里贴出语法,很简单 SQL> set autotrace   Usag...

【Oracle】三种方式查看SQL语句的执行计划

查看执行计划的方式有三种: EXPLAN PLAN 、V$SQL_PLAN 、SQL*PLUS AUTOTRACE1.EXPLAN PLAN: 显示执行相应语句时可以使用的理论计划 读取执行计划...

Oracle查看SQL执行计划的方式

Oracle查看SQL执行计划的方式   获取Oracle sql执行计划并查看执行计划,是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式: 基本有以下几种...
  • lqx0405
  • lqx0405
  • 2017年02月21日 17:39
  • 271

dbms_xplan.display_awr方式获取执行计划的实验和之前的误导

《查看Oracle执行计划的几种常用方法-系列1》(http://blog.csdn.net/bisal/article/details/38919181)这篇博文中曾提到一个隐藏问题: “隐藏问题...
  • bisal
  • bisal
  • 2014年10月28日 13:33
  • 4423

oracle的执行计划中表的链接方式介绍

 在日常基于数据库应用的开发过程中,我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。那么Oracle到底存在着哪几种连接方式?优化器内部又是怎样处理这些连接的?哪种连接方式又是...

Oracle查看SQL执行计划的方式

Oracle查看SQL执行计划的方式   获取Oracle sql执行计划并查看执行计划,是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式:基本有以下几种方式:1、通过sq...
  • lqx0405
  • lqx0405
  • 2015年03月31日 12:01
  • 876
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:执行计划中常见index访问方式
举报原因:
原因补充:

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