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

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

今天我们讨论下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 /