索引扫描方式实验

1. INDEX UNIQUE SCAN
创建测试表T1
JZH@ORCL>create table t1 as select object_id,object_name from dba_objects;
Table created.
创建唯一性索引
JZH@ORCL>create unique index indx_t1 on t1(object_id);
Index created.
JZH@ORCL>set autot trace exp stat
JZH@ORCL>select * from t1 where object_id=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1247488252
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    79 |     2   (0)| 00:
00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    79 |     2   (0)| 00:
00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INDX_T1 |     1 |       |     1   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1)
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          4  consistent gets
          4  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        454  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
索引唯一性扫描是针对唯一性索引的，在where条件中是等值查询，返回的结果通常只有一行数据。
2.INDEX RANGE SCAN
删除原来的索引(indx_t1)
JZH@ORCL>drop index indx_t1;
Index dropped.
创建索引
JZH@ORCL>create index indx_t1 on t1 (object_id);
Index created.
JZH@ORCL>set autot trace exp stat
JZH@ORCL>select * from t1 where object_id>1000 and object_id<2000;
999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1646002207
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   999 | 78921 |    10   (0)| 00:
00:01 |
  1 |  TABLE ACCESS BY INDEX ROWID| T1      |   999 | 78921 |    10   (0)| 00:
00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_T1 |   999 |       |     4   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
         63  recursive calls
          0  db block gets
        216  consistent gets
          6  physical reads
          0  redo size
      39177  bytes sent via SQL*Net to client
       1191  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed
索引范围扫描适用于所有的B树索引，当扫描唯一性索引时，范围一定是between,而扫描非唯一性的索引时，where条件是无限制的，可以是=,。
3.INDEX FULL SCAN
select /*+ index(t1 indx_t1) */ object_id from t1;
 OBJECT_ID
----------
     52257
     52259
     52261
     52263
     52276
     52279
     52280
Execution Plan
----------------------------------------------------------
Plan hash value: 3872582085
---------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         | 52905 |   671K|   116   (1)| 00:00:02 |
|   1 |  INDEX FULL SCAN | INDX_T1 | 52905 |   671K|   116   (1)| 00:00:02 |
----------------------------------------------------------------------------
Note
------ dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3412  consistent gets
          0  physical reads
          0  redo size
     905956  bytes sent via SQL*Net to client
      36952  bytes received via SQL*Net from client
       3319  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49760  rows processed

索引全扫描也适用于所有类型的B树索引，特别是不能并行，SQL查询列只有索引列，且查询结果是排序的。
4.INDEX FAST FULL SCAN

JZH@ORCL>select /*+ index_ffs(t1 indx_t1) */ * from t1;
 Execution Plan
----------------------------------------------------------
Plan hash value: 2048061550
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |    14 |   280 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| INDX_T1 |    14 |   280 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
------ dynamic sampling used for this statement
Statistics
----------------------------------------------------------
        196  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
        823  bytes sent via SQL*Net to client
        465  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         14  rows processed

索引快速全扫描适用于所有类弄的B树索引，特点与全扫描类似，区别是可以并行，结果不一定排序。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1163495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1163495/