Oracle最常用的B树索引的5种访问方法

今天我们讨论下Oracle数据库中最常用的B树索引，首先我们先来看一下Oracle数据库里B树索引的结构。

从图中我们可以看出，Oracle数据库里的B树索引就好像一颗倒长的树，它包含两种类型的数据块。 一种是索引分支块（L1-1,L1-2），另一种是索引叶子块（L0-1,L0-2,L0-3,L0-4,L0-5,L0-6）。 索引分支块包含指向相应索引分支块和叶子块的指针和索引键值列.索引键值列不一定是完整的索引键值， 它可能只是索引键值的前缀，只要Oracle 能通过这些前缀区分相应的索引分支块，叶子块就行，这样Oracle就能够既节省索引分支块的存储空间，又可以快速定位其下层的索引分支块，叶子块。索引分支块最上层的那个块就是所谓的索引根节点。就是图中的最上层root包含BC的块。在Oracle里访问B书索引的操作都必须从根节点开始，都会经历一个根节点到分支块再到叶子块的过程。

索引叶子块包含索引键值和用于定位该索引键值实际的数据行在表中的实际物理存储位置的rowid。

对于唯一性B树索引而言，ROWID是存储在索引行的行头，所以此时Oracle不需要存储该rowid的长度。

而对于非唯一性B树索引而言，ROWID被当作额外的列与索引键值列一起存储，所以此时Oracle既要存储rowid，同时又要存储其长度，这意味着在同等条件下，唯一性B树索引要比非唯一性B树索引节省索引叶子块的存储空间。对于非唯一性索引而言，B树索引的有序性体现在Oracle会按照索引键值和rowid来联合排序。Oracle索引叶子块是双向指针链表，它能把左右的索引叶子块相互连接起来，而无须经历一个根节点到分支块再到叶子块的过程遍历。

正是由于B树索引的结构特点，Oracle 数据库中的B树索引有以下优势。

1.所有的索引叶子块都在同一层，他们索引深度是相同的。这意味着访问索引叶子块的任何一个索引键值所花费的时间几乎相同。

2.Oracle能保证所有的B树索引是自平衡的，不可能出现不同的索引叶子块处于同一层的现象。

3.通过B树索引访问表里行记录的效率不会随着相关表的数据量的递增而明显降低。

 B树索引的结构决定了在通过B树索引访问数据的过程是先访问相关的B树索引，然后根据访问该索引后得到的rowid，再去访问表对应的数据行记录。如果访问需要的数据通过B树索引就可以得到，就不需要再访问表了。访问B树索引和表都需要消耗IO。这就意味着在oracle中访问索引的成本有两部分组成，一部分是访问B树索引的成本（从根节点定位到分支块，再定位到相关的叶子块，最后对叶子块执行扫描操作）
 另一部分是方位表的成本（根据B树索引得到ROWID再去表扫描对应的数据行所对应的数据块）。
 B树索引有5种访问方法。
1.索引唯一性扫描
 索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)的扫描，它仅仅适用于where 条件里是等值查询的SQL，因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。
2.索引范围扫描
 索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，当扫描的对象是唯一性索引时，SQL的where条件一定是范围查询(between,<>);当扫描的对象是非唯一性索引时，SQL的where条件没有限制，可以是=，between,  <>等。索引范围扫描的结果可能返回多条记录。
 在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比相应的索引唯一性扫描的逻辑读多1.
  我们做个实验验证下结论。
 SQL>create table test as select * from emp;
  SQL>select count(empno) from test;
  COUNT(EMPNO)
  -----------------
      13
 
  表test中列empno的非null值的数量为13，意味着在test表的列empno上建立B树索引，索引行的数量一定大于1
 
  在表test列empno上创建唯一性B树索引idx_empno
  SQL> create unique index idx_empno on test(empno);

 Index created.
  收集下test表和idx_empno索引
 SQL> begin
  2  dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
  3  end;
  4  /

 PL/SQL procedure successfully completed.
  SQL> alter system flush shared_pool;
  SQL> alter system flush buffer_cache;
  SQL>set autotrace traceonly
  SQL>select * from test where empno=7369;
  Execution Plan
 ----------------------------------------------------------
 Plan hash value: 3039750644

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

| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| T
 ime |

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

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

|  1 |  TABLE ACCESS BY INDEX ROWID| TEST |    1 |    37 |    1  (0)| 0
 0:00:01 |

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

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

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

  2 - access("EMPNO"=7369)

 Statistics
 ----------------------------------------------------------
        1088  recursive calls
    0  db block gets
  164  consistent gets
  23  physical reads
    0  redo size
  822  bytes sent via SQL*Net to client
  385  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
  12  sorts (memory)
    0  sorts (disk)
    1  rows processed
   
从上述执行计划的内容可以看到，执行计划走的是索引唯一性扫描，消耗的逻辑读为164.
第二步骤，我们删除唯一性索引idx_empno
 SQL> DROP INDEX IDX_EMPNO;
创建非唯一性的B树索引。
SQL> CREATE INDEX IDX_EMPNO ON TEST(EMPNO);
 Index dropped.
再次收集统计信息
 SQL> begin
  2  dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
  3  end;
  4  /

 PL/SQL procedure successfully completed.
再次清空buffer cache 和shared pool,千万别在生产环境中执行。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
 SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
 SQL> select * from test where empno=7369;

Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1320605699

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

| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| T
 ime |

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

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

|  1 |  TABLE ACCESS BY INDEX ROWID| TEST |    1 |    37 |    2  (0)| 0
 0:00:01 |

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

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

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

  2 - access("EMPNO"=7369)

 Statistics
 ----------------------------------------------------------
  813  recursive calls
    0  db block gets
  165  consistent gets
  27  physical reads
    0  redo size
  822  bytes sent via SQL*Net to client
  385  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    9  sorts (memory)
    0  sorts (disk)
    1  rows processed
   
从执行计划的内容看，sql的执行计划从之前的索引唯一性扫描变成索引范围扫描，逻辑读的值由164变为了165，比原来多扫描1次。

3.索引全扫描
 索引全扫描(INDEX FULL SCAN)适用于所有的B树索引。索引全扫描要扫描索引所有叶子块的所有索引。在默认情况下，索引全扫描从左到右依次顺序扫描索引所有叶子块的所有索引，索引是有序，所以索引全扫描执行的结果也是有序的。
 SQL> select empno from test
  2  ;

    EMPNO
 ----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

    EMPNO
 ----------
      7900
      7902
      7934

14 rows selected.

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 654388723

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

 Statistics
 ----------------------------------------------------------
    1  recursive calls
    0  db block gets
    2  consistent gets
    0  physical reads
    0  redo size
  556  bytes sent via SQL*Net to client
  385  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
  14  rows processed
 
从执行计划中，我们看到统计信息部分的“sorts(memory)”和“sorts(disk)”的值都为0.说明 SQL执行结果已经按照列empno排好序了。
 
4.索引快速全扫描
  索引快速全扫描（INDEX FAST FULL SCAN ）和索引全扫描非常类似，它适用所有B树索引。
  索引快速全扫描也要扫码所有叶子块的所有索引行。
  他们区别如下：：
  1)索引快速全扫描只适用于CBO
  2)索引快速全扫描可以使用多块读，也可以并行执行。
  3)索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时，oracle是根据索引行在磁盘上的物理存储顺序来扫描的，而不是根据索引行的逻辑顺序来扫描的。
5.索引跳跃式扫描
 索引跳跃式扫描（INDEX SKIP SCAN）适用所有复合B树索引。它使那些在where 条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引。这就像是在扫描索引时跳过了他的前导列，直接从该索引的非前导列开始扫描一样。
 看一个例子：
 SQL>create table test1(name varchar2(10),id number not null);
  创建一个复合B树索引
 SQL>create index idx_naid on test1(name,id);
  插入10000条记录，5000条name列为test,5000条name列为prot
  begin
  for i in 1..5000 loop
  insert into test1 values('test',i);
  end loop;
  commit;
  end;
  /
  begin
  for i in 1..5000 loop
  insert into test1 values('prot',i);
  end loop;
  commit;
  end;
  /
  SQL>SET AUTOTRACE TRACEONLY
  SQL>exec dbms_stats.gather_table_stats('SCOTT','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
  SQL>select * from test1 where id=200;
  Execution Plan
 ----------------------------------------------------------
 Plan hash value: 4123651466

-----------------------------------------------------------------------------
 | Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
 -----------------------------------------------------------------------------
 |  0 | SELECT STATEMENT |    |  2 |  16 |  3  (0)| 00:00:01 |
 |*  1 |  INDEX SKIP SCAN | IDX_NAID |  2 |  16 |  3  (0)| 00:00:01 |
 -----------------------------------------------------------------------------

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

  1 - access("ID"=200)
        filter("ID"=200)

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

例子中的where条件是id=200,只对复合B树索引的第二列id指定了查询条件，并没有对索引的前导列name指定任何查询条件。这里没有指定前导列的情况下还能使用上索引，是因为oracle自动对该索引的前导列的所有distinct值做了遍历。

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