关闭

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

213人阅读 评论(0) 收藏 举报
分类:

文章来自: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








0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:146677次
    • 积分:3187
    • 等级:
    • 排名:第11222名
    • 原创:163篇
    • 转载:87篇
    • 译文:4篇
    • 评论:5条
    最新评论