ITPub博客

首页 > 数据库 > Oracle > 索引扫描方式实验

索引扫描方式实验

原创 Oracle 作者:parknkjun 时间:2014-05-17 23:51:51 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-11

  • 博文量
    113
  • 访问量
    514393