访问索引的方法

    首先要说明一点，以下提到是oracle数据库最常用的B树索引，oracle数据其他类型的索引暂不考虑，B树索引就好像一棵倒长的树，它包含两种类型的数据块，一种是索引分支块，另一种是索引叶子块。

    索引分支块包含指向响应索引分支块/叶子块的指针和索引键值列（这里的指针是指相关分支块/叶子块的块地址RDBA，每个索引分支块都有两种类型的指针，一种是lmc,另一种是索引分支块的索引行记录的指针。lmc是left Most Child的缩写，每个索引分支块都只有一个lmc,这个Imc指向的分支块/叶子块中所有索引键值列中最大值一定下于该lmc所在索引分支块的索引索引键值列中的最小值；而索引分支块的索引行记录所记录的指针指向的分支块/叶子块的所有索引键值列的最小值一定大于或等于该行记录的索引键值列的值）在oracle里访问B数索引的操作都必须从根节点开始，即都会经历一个从根节点到分支块再到叶子块的过程。

    索引叶子块包含被索引键值和用于定位该索引键值所在的数据行在表中的实际物理存储位置的ROWID。对于唯一性B树索引而言，rowid是存储在索引行的行头，所以此时并不需要额外存储该rowid的长度。对于非唯一性B数索引而言，rowid被当做额外的列与被索引的键值列一起存储，所以此时oracle既要存储rowid，同时又要存储其长度，这意味着在同等条件下，唯一性B树索引要比非唯一性B树索引节省索引叶子块的存储空间。对于非唯一索引而言，B树索引的有序性体现在oracle会按照被索引键值和相应的rowid来联合排序。oracle里的索引叶子块时左右互联的，即相当于有一个双向指针链表把这些索引叶子块互相连接了一起。

    正是由于上述特点，oracle数据库中的B树索引才具有如下优势：

（1）所有的索引叶子块都在同一层，即他们距离索引根节点的深度是相同的，这也意味着访问索引叶子块的任何一个索引键值所花费的时间几乎相同。

 （2）oracle会保证所有B树索引都是自平衡的，即不可能出现不同索引叶子块不处于同一层的现象。

 （3）通过B树索引访问表的行记录的效率并不会随着相关表的数据量递增而显著降低，即通过走索引访问数据的时间是可控的，基本稳定的，这也是走索引和全表扫描的最大区别。全表扫描最大的劣势就在于其访问时间不可控，不稳定，即全表扫描花费的时间会随着目标表数据量的递增而递增。

    B树索引的上述结构就决定了在oracle里通过B树索引访问数据的过程是先访问相关的B树索引，然后根据访问该索引后得到的rowid再回表去访问对应的数据行记录（当然，如果目标sql所访问的数据通过访问相关的B树索引可以得到，那么就不再需要回表了）。访问相关的B树索引和回表需要消耗I/O，这意味着在oracle中访问索引的成本由两部分组成：一部分是访问相关的B树索引的成本（从根节点到相关的分支块，再定位相关的叶子块，最后对这些叶子块执行扫描操作）；另一部分是回表成本（根据得到的rowid再回表去扫描对应的数据行所在的数据块）

    下面介绍oracle中一些常见的访问B树索引的方法

（1）索引唯一扫描

    索引唯一性扫描（INDEX UNIQUE SCAN）是针对唯一性索引（UNIQUE INDEX)的扫描，它仅仅适用于where条件里是等值查询的目标sql。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录

(2)索引范围扫描

    索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，当扫描的对象是唯一性索引时，此时目标sql的where条件一定是范围查询（谓词条件为between，<，>)；当扫描的对象是非唯一索引时，对目标sql的where条件没有限制（可以是等值查询，也可以时范围查询）。索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中范围"范围“二字的本质含义。

    需要注意的是，即使是针对同条件下相同的sql，当目标索引的索引行的数量大于1时，索引范围扫描耗费的逻辑读会多于索引唯一扫描所耗费的逻辑读。这是因为所有唯一扫描结果至多只返回一条记录，所以oracle明确知道此时只需要访问相关的叶子块一次就可以直接返回了；但对于索引范围扫描而言，因为其扫描结果可能会返回多条记录，同时又因为索引的索引行数量大于1，oracle为了确定索引范围扫描的终点，就不得不去多访问一次相关的叶子块，所以在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比相应的索引唯一性的逻辑读多1.

    实验验证：

    SQL> create table emp_temp as select * from emp;

    Table created.

现在emp_temp中列empno的非null值的数量为13（这意味着如果在表emp_temp的列empno上建单键值的B树索引，则该索引的索引行的数量一定大于1）

    SQL> create unique index idx_emp_temp on emp_temp(empno);

    Index created.

然后对表emp_temp和索引idx_emp_temp收集一下统计信息：

    SQL> exec     dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>10    0,cascade=>true,method_opt=>'for all columns size 1');

    PL/SQL procedure successfully completed.

为了避免buffer cache 和数据字典缓存（DATA Dictionary Cache)对逻辑读统计结果的影响。我们清空buffer cache和数据字典缓存

    SQL> alter system flush shared_pool;

    System altered.

    SQL> alter system flush buffer_cache;

    System altered.

执行计划：

SQL> select * from emp_temp where empno=7369;

Execution Plan

----------------------------------------------------------

Plan hash value: 3451700904

--------------------------------------------------------------------------------------------

| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |   | 1 |38 | 1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP   | 1 |38 | 1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN    | IDX_EMP_TEMP | 1 |   | 0   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7369)

Statistics

----------------------------------------------------------

32  recursive calls

 0  db block gets

67  consistent gets

13  physical reads

 0  redo size

889  bytes sent via SQL*Net to client

512  bytes received via SQL*Net from client

 1  SQL*Net roundtrips to/from client

 6  sorts (memory)

 0  sorts (disk)

 1  rows processed

    从以上显示可以看出，该sql的执行计划走的是索引唯一性扫描，其耗费的逻辑读是73.

现在我们drop掉唯一索引

    SQL> drop index idx_emp_temp;

    Index dropped.

然后在表emp_temp的列empno上创建一个单键值非唯一性同名B树索引idx_emp_temp：

    SQL> create index idx_emp_temp on emp_temp(empno);

    Index created.

收集统计信息：

    SQL> exec     dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>10    0,cascade=>true,method_opt=>'for all columns size 1');

    PL/SQL procedure successfully completed.

    SQL> set autot traceonly

    SQL> alter system flush shared_pool;

    System altered.

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> select * from emp_temp where empno=7369;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 351331621

    --------------------------------------------------------------------------------------

    | Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time       |

    ---------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT    |   | 1 |38 | 2   (0)| 00:00:01     |

    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP   | 1 |38 | 2   (0)| 00:00:01 |

    |*  2 |   INDEX RANGE SCAN    | IDX_EMP_TEMP | 1 |   | 1   (0)| 00:00:01     |

    ---------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

   2 - access("EMPNO"=7369)

    Statistics

    ----------------------------------------------------------

 32  recursive calls

 0  db block gets

 68  consistent gets

 16  physical reads

 0  redo size

 1025  bytes sent via SQL*Net to client

 523  bytes received via SQL*Net from client

 2  SQL*Net roundtrips to/from client

 6  sorts (memory)

 0  sorts (disk)

 1  rows processed

    上述结果看出，此sql的执行计划已经从之前的索引唯一扫描变成现在的索引范围扫描，其耗费的逻辑读也从之前的73递增到74，这说明在同等的条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读确实知识会比相应的的索引唯一扫描多1.

(3)索引全扫描

    索引全扫描（INDEX FULL SCAN）适用于所有类型的B树索引（包括唯一性索引和非唯一性索引）。所谓“索引全扫描”，就是指要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的索引叶子块，但这并不意味着需要扫描该索引的所有分支块。在默认情况下，oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左到右依次顺序扫描该索引所有叶子块的所有索引行了。

    既然默认情况下，索引全扫描要从左到右依次顺序扫描目标索引所有叶子块的所有索引行，而索引是有序的，所以索引全扫描的执行结果也是有序的，并且是按照该索引的索引键值来排序，这也意味着走索引全扫描能够既达到排序的效果，又同时避免对该索引的索引键值列的真正排序操作。

    SQL> conn scott/scott;

    Connected.

    SQL> set autot on

    SQL> select empno from emp;

     EMPNO

    ----------

      7369

      7499

      7521

      7566

      7654

      7698

      7782

      7788

      7839

      7844

      7876

     EMPNO

    ----------

      7900

      7902

      7934

    14 rows selected.

    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

    ----------------------------------------------------------

31  recursive calls

 0  db block gets

62  consistent gets

 2  physical reads

 0  redo size

686  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

 2  SQL*Net roundtrips to/from client

  0  sorts (memory)

 0  sorts (disk)

14  rows processed

    对于上述sql，表EMP的列empno上存在一个单键值B树主键索引PK_EMP，所以列empno的属性一定是NOT NULL，而该SQL的从查询又只有empno，所以oracle此时可以走对主键索引PK_EMP的索引全扫描。

    从上述显示的内容我们可以看出，该sql的执行计划确实走的是对主键索引pk_emp的索引全扫描，而且执行结果已经按照empno排好序了。注意到上述显示内容中“统计信息”部分“sort（memory）”和“sort disk”的值均为0，这说明虽然上述sql的执行结果已经按照empno列排好序，但是实际上oracle在这里并没有执行任何排序操作，所以走索引全扫描确实能够既达到排序的结果又可以避免对目标索引的索引键值列真正的排序。

    默认情况下，索引全扫描的扫描结果是有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。

    通常情况下，索引全扫描不需要回表的，所以索引全扫描适用于目标sql的查询列全部是目标索引的索引键值列的情形。我们知道，对于oracle数据库中B树索引而言，当所有索引键值列全为NULL值时不入索引（即当所有索引键值列全为NULL时，这些NULL值不会在B树索引中存在），这意味在oracle中做索引全扫描的前提条件时目标索引只是有一个索引键值列的属性值是NOT NULL。这是很显然的事情，如果目标索引的所有索引键值列属性均为允许null值，此时如果还走索引全扫描，就会漏掉目标表中那些索引键值列均为null的记录，即此时走索引全扫描的结果就不准了，oracle显然不会允许这种事情发生。

（4）索引快速全扫描

    索引快速全扫描（INDEX FAST FULL SCAN）和索引全扫描极其类似，它适用于所有类型的B树索引（包括唯一性索引和非唯一性索引）。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

    索引快速全扫描和索引全扫描相比有以下三种区别：

    a.索引快速全扫描只适用于CBO

    b.索引快速全扫描可以使用多块读，也可以并行执行。

    c.索引快速全扫描的执行结果不一定是有序的，这是因为索引快速全扫描时，oralce是根据索引行在磁盘上的物理存储顺利来扫描的，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块的索引而言，其物理存储顺序和逻辑存储顺序一致，但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序不一定在逻辑上有序的）。

    实验验证：

SQL> create table emp_test(empno number,col1 char(2000),col2 char(2000),col3 char(2000));

Table created.

SQL> alter table emp_test add constraint pk_emp_test primary key(empno,col1,col2,col3);

Table altered.

SQL> insert into emp_test select empno,ename,job,'A' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'B' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'C' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'D' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'E' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'F' from emp;

14 rows created.

SQL> insert into emp_test select empno,ename,job,'G' from emp;

14 rows created.

SQL> select count(*) from emp_test;

  COUNT(*)

----------

98

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

这里 /*+ index_ffs(pk_emp_test) */的目的是让oracle走对主键索引pk_emp_test的索引快速全扫描。

SQL>  select /*+ index_ffs(pk_emp_test) */ empno from emp_test;

     EMPNO

----------

      7521

      7566

      7369

      7499

      7782

      7788

      7844

      7876

      7900

      7839

      7654

98 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3550420785

------------------------------------------------------------------------------------

| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time   |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |   | 98 |   392 | 28   (0)| 00:00:01 |

|   1 |  INDEX FAST FULL SCAN| PK_EMP_TEST | 98 |   392 | 28   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

 0  recursive calls

 0  db block gets

250  consistent gets

 0  physical reads

 0  redo size

       2224  bytes sent via SQL*Net to client

590  bytes received via SQL*Net from client

 8  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

98  rows processed

上述实验说明：索引快速全扫描的执行结果不一定是有序的。

（5）索引跳跃式扫描

    索引跳跃式扫描（index skip scan）使用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使哪些在where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定查询条件的目标sql依然可以用上该索引，这就像是在扫描该索引跳过了它的前导列，直接从该索引的非前导列开始扫描一样，这也是索引跳跃式扫描中“跳跃”一词的含义。

    为什么在where条件中没有对目标索引的前导列指定查询条件但oracle依然可以使用上该索引，这是因为oralce帮你对该索引的前导列的所有distinct值做了遍历。

    实验验证：

SQL> create table employee(gender varchar2(1),employee_id number);

Table created.

SQL> alter table employee modify(employee_id not null);

Table altered.

SQL> create index idx_employee on employee(gender,employee_id);

Index created.

SQL> begin

  2  for i in 1..5000 loop

  3  insert into employee values('F',i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> begin

  2  for i in 5001..10000 loop

  3  insert into employee values('F',i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> set autot off

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'employee',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> set autot on

SQL> select * from employee where employee_id=100;

G EMPLOYEE_ID

- -----------

F  100

Execution Plan

----------------------------------------------------------

Plan hash value: 461756150

---------------------------------------------------------------------------------

| Id  | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT | |     1 |     6 |     2   (0)| 00:00:01 |

|*  1 |  INDEX SKIP SCAN | IDX_EMPLOYEE |     1 |     6 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("EMPLOYEE_ID"=100)

       filter("EMPLOYEE_ID"=100)

Statistics

----------------------------------------------------------

 0  recursive calls

 0  db block gets

24  consistent gets

 0  physical reads

 0  redo size

599  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

 2  SQL*Net roundtrips to/from client

 0  sorts (memory)

 0  sorts (disk)

 1  rows processed

    从上述显示内容可以看出，oracle在执行sql时已经用了索引idx_employee，并且其执行计划走的是就是对该索引的索引跳跃式扫描。

    这里在没有指定前导列的情况下还能用上述索引，就是因为oracle帮我们对该索引的前导列的索引distinct值做了遍历。

    所谓的对目标索引的索引distinct值做了遍历，其实实际含义相当于对原sql做了等价改写（既把要用的目标索引的所有前导列的distinct值都加进来）。索引inde_employee的前导列gender的distinct值只有“F”和“M”两个值，所以这里能使用ind_employee的原因可以简单理解为以下sql：

    select * from employee where gender='F' and employee_id=100

    union all 

    select * from employee where gender='M' and employee_id=100;

    从上述分析过程可以看出，Oracle中跳跃式索引扫描仅仅适用于那些目标索引前导列的distinct值数量较少，后续非前导的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递增。