首页 > 代码库 > 访问索引的方法
访问索引的方法
首先要说明一点，以下提到是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.
访问索引的方法