oracle 索引访问的几种方法

索引的全扫描跟索引的快速全扫描
索引快速全扫描：从索引段头开始多块读HWM以下的所有根块、枝块、叶块，所以通过FFS读出来的数据读取顺序是由物理存储位置决定的、可能是无序的。因此在这类操作中，我们可以看到会话会大量的出现db file scattered read等待。

索引全扫描：索引全扫描是根据叶节点链来进行的。进行索引全扫描首先要从根开始，找到叶节点链上的第一个数据块，然后沿着叶节点链进行扫描，由于叶节点链是根据索引键值排序的，因此这样扫描出来的数据本身就是排序的，数据读出后不需要再次排序。这种扫描方式和索引快速全扫描相比，首先要找到索引的根，然后通过枝节点找到第一个叶节点，然后再顺着叶节点链扫描整个索引。索引全扫描的IO成本比索引快速全扫描要大很多，读取根节点和叶节点的成本相对不大，不过由于顺着叶节点链扫描整个索引的时候无法使用多块读，而只能使用单块读，因此这种扫描方式的IO开销要远大于索引快速全扫描。这种索引扫描，我们如果对会话进行跟踪，会发现大量的db file sequential read等待
20:44:29 scott@orcl> create table test as select * from emp;

Table created.

Elapsed: 00:00:00.30
20:44:39 scott@orcl> alter table test add constraint pk_test primary key(empno);

Table altered.

Elapsed: 00:00:00.36
20:45:12 scott@orcl> select * from test;

        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM    DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
         7369 SMITH      CLERK              7902 1980-12-17 00:00:00           800             20
         7499 ALLEN      SALESMAN           7698 1981-02-20 00:00:00          1600           300        30
         7521 WARD       SALESMAN           7698 1981-02-22 00:00:00          1250           500        30
         7566 JONES      MANAGER            7839 1981-04-02 00:00:00          2975             20
         7654 MARTIN     SALESMAN           7698 1981-09-28 00:00:00          1250          1400        30
         7698 BLAKE      MANAGER            7839 1981-05-01 00:00:00          2850             30
         7782 CLARK      MANAGER            7839 1981-06-09 00:00:00          2450             10
         7788 SCOTT      ANALYST            7566 1987-04-19 00:00:00          3000             20
         7839 KING       PRESIDENT               1981-11-17 00:00:00          5000             10
         7844 TURNER     SALESMAN           7698 1981-09-08 00:00:00          1500             0        30
         7876 ADAMS      CLERK              7788 1987-05-23 00:00:00          1100             20
         7900 JAMES      CLERK              7698 1981-12-03 00:00:00           950             30
         7902 FORD       ANALYST            7566 1981-12-03 00:00:00          3000             20
         7934 MILLER     CLERK              7782 1982-01-23 00:00:00          1300             10

14 rows selected.

Elapsed: 00:00:00.01
20:45:22 scott@orcl> create table t as select * from emp where empno=7900;

Table created.

Elapsed: 00:00:00.03
20:46:54 scott@orcl> begin
20:47:00   2  for i in 1..7200 loop
20:47:12   3  update t set empno=i;
20:47:24   4  insert into test select * from t;
20:47:40   5  commit;
20:47:42   6  end loop;
20:47:47   7  end;
20:47:48   8  /

PL/SQL procedure successfully completed.

scott@orcl> select empno from test;

7214 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 850129961

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  7214 | 21642 |     8   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| PK_TEST |  7214 | 21642 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        509  consistent gets
          0  physical reads
          0  redo size
     125803  bytes sent via SQL*Net to client
       5800  bytes received via SQL*Net from client
        482  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7214  rows processed

scott@orcl> select empno from emp;

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


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

索引的跳跃式扫描
21:22:06 scott@orcl> select distinct id from test2;

           ID
-------------
            1
            2
            
21:16:40 scott@orcl> create index idx_test2 on test2(id,empno);

Index created.

21:19:16 scott@orcl> analyze table test2 compute statistics;

Table analyzed.

Elapsed: 00:00:00.10
21:19:24 scott@orcl> select * from test2 where empno=7788;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3100316192

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    33 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2     |     1 |    33 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST2 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7788)
       filter("EMPNO"=7788)


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

从上面可以看到，在没有指定前导列的情况下还能够用上该索引，就是因为orcle帮助我们对该索引的前导列的所有的distinct的值做了个遍历。
所谓的对目标的distinct的值做了遍历。其含义就是对目标的sql做了等价的改写（即把要用的目标索引所有前导列的distinct的值都加进来。我们看到
索引的前导列id的值只有1，2两个。所以我们就能简单的理解oracle把
select * from test2 where empno=7788;
改写成了
select * from test2 where id=1 and empno=7788 union all select * from test2 where id=2 and empno=7788;
所以我们看到 oracle的索引的跳跃式扫描仅仅适用于那些目标的前导列的distinct只数量比较少的情况，后续非前导列的值的选择性有非常好的情况下。

索引范围扫描
21:35:15 scott@orcl> select * from test where empno<300;

299 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 115135762

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   272 |  8976 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |   272 |  8976 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TEST |   272 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("EMPNO"<300)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
      17688  bytes sent via SQL*Net to client
        728  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        299  rows processed
        
适用于谓词条件有选择范围的