索引（三)：索引的扫描方式

1、INDEX RANGE SCAN

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  

select * from t where object_id=8;

Execution Plan
----------------------------------------------------------
Plan hash value: 2041828949

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |     98 |      2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |     98 |      2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IDX_OBJECT_ID |      1 |        |      1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=8)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
      0  physical reads
      0  redo size
       1606  bytes sent via SQL*Net to client
    525  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

02.INDEX UNIQUE SCAN

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000

select * from t where object_id=8;

Execution Plan
----------------------------------------------------------
Plan hash value: 713698563

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |    207 |      2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |    207 |      2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | IDX_OBJECT_ID |      1 |        |      1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=8)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
       1606  bytes sent via SQL*Net to client
    525  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

03.TABLE ACCESS BY USER ROWID

--请注意这个TABLE ACCESS BY USER ROWID扫描方式,直接根据rowid来访问，最快的访问方式！

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
--注意，这里连索引都没建!
--create  index idx_object_id on t(object_id);
set autotrace off
select rowid from t where object_id=8;
ROWID
------------------
AAAZxiAAGAAAB07AAH

set autotrace traceonly
set linesize 1000

select * from t where object_id=8 and rowid='AAAZxiAAGAAAB07AAH';
执行计划
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |   219 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY USER ROWID| T    |     1 |   219 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       1391  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

05.INDEX FAST FULL SCAN

---请记住这个INDEX FULL SCAN扫描方式，并体会与INDEX FAST FULL SCAN的区别

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000

select * from t  order by object_id;（索引全扫描可以消除排序（单块读），索引快速扫描是多块读（无法保证顺序））
执行计划
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 88780 |    17M|  1208   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             | 88780 |    17M|  1208   (1)| 00:00:15 |
|   2 |   INDEX FULL SCAN           | IDX_OBJECT_ID | 88780 |       |   164   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10873  consistent gets
          0  physical reads
          0  redo size
    8116181  bytes sent via SQL*Net to client
      54040  bytes received via SQL*Net from client
       4877  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73130  rows processed

05.INDEX FAST FULL SCAN

---请记住这个INDEX FAST FULL SCAN扫描方式，并体会与INDEX FULL SCAN的区别

drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000

select count(*) from t;（没有进行排序，所以以快速全扫描去读取整个表，一次读取多个块会更快。）
执行计划
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    49   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 88780 |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        170  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

06.INDEX SKIP SCAN

--请记住这个INDEX SKIP SCAN扫描方式

drop table t purge;
create table t as select * from dba_objects;
update t set object_type='TABLE' ;
commit;
update t set object_type='VIEW' where rownum<=30000;
commit;
create  index idx_type_id on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;（object_bype只有两个，可以用object_id的rowid去分开两部分去扫描 ）

执行计划
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    94 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    94 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TYPE_ID |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=8)
       filter("OBJECT_ID"=8)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1401  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed