Oracle 索引详解

设想一下，在大型的商业数据库中有一个包含数百万条记录的表，如果该表没有索引，那么查询其中的任何记录就只能通过顺序地扫描该表中的每一行记录来完成。这会产生大量的磁盘输入/输出(I/O)，因此也就会大大地降低系统的效率。

引入索引的目的就是为了加快查询的速度。Oracle索引是一个独立于表的对象，它可以存放在与表不同的表空间中。即使索引崩溃，甚至索引被删除都不会影响真正存有数据的表。在Oracle数据库中，一个索引一旦被建立就由Oracle系统自动维护，而且由Oracle系统决定什么时候使用该索引，不用在查询语句中指定使用哪个索引。使用的查询语句与未建索引时几乎完全一样，只是查询速度快多了。虽然Oracle索引是一个独立于表的对象，但是当一个表被删除时所有基于该表的索引都会被自动地删除。

1. 索引的分类

索引可以按逻辑设计分类，也可以以按物理实现分类。索引的逻辑分类是从应用的角度来划分，而物理分类是从其物理存储的角度来划分。

1.1 逻辑分类

• 单列索引：基于一列的索引，如在emp表中的ename列上的索引。
• 多列索引：也叫组合(复合)索引，是基于多列的索引，如在emp表中的job和sal两列上所创建的索引。组合索引中的列数最多为32列。
• 唯一索引：保证表中任何数据行的索引列的值都不相同。
• 基于函数的索引：利用表中的一列或多列使用函数或表达式所创建的索引。基于函数的索引预先计算函数或表达式的值并存储在该索引中。基于函数的索引既可以是B-树索引，也可以是位图索引。

1.2 物理分类

• 分区或非分区索引
• B-树
• 位图索引

3. B-树索引

Oracle通过索引机制来加速数据的存取。Oracle的B树索引(B-Tree索引)以树型结构来存放键值(key value)。每个B树索引都有一个根节点(root)，对索引进行访问时首先从根节点开始，根据和根节点上键值的比较结果，找到相应的分支节点(branch)，再和分支节点的键值进行比较，最后一直找到叶子(leaf)节点。在叶子节点上，存放索引的键值和表中对应这个键值的记录的物理地址(rowid)。通过rowid，就可以直接定位到表的记录。

对于惟一索引，一个键值和一条记录的rowid相对应，对于非惟一索引，一个键值可能对应多条记录的rowid。B树索引对于具有惟一键值或者键值很少重复的情况，具有很高的存取效率。但是对于那些键值大量重复的列，B树索引并不适用，因为即使找到对应的键值仍然要扫描大量的rowid，这种情况下使用B树索引已经没有什么意义了。

所有索引所在列的值为空(null)的数据行，Oracle将不存储与之对应的索引项。因此，如果在WHERE子句中索引所在列的值为null，则Oracle将不使用索引而进行全表扫描。

4. 位图索引

Oracle针对这种情况提供了Bitmap索引。与B-树索引相比，在某些情况下位图索引具有更多的优势。位图索引也是一种B-树结构，但是其叶子节点存放的不是ROWID，而是每一个键值的位图。

位图是从第4号文件的第14(数据)块的第0行开始到第4号文件的第16(数据)块的第8行结束。位图中的第1行记录为学士(因为位图中相应位是1)，第2行不是学士，第3行也不是，第4行也不是，但第5行是，以此类推。

Bitmap索引适用于键值大量重复的列的查询。Bitmap索引对索引列的每一个键值分别索引。对于一个键值，可能分成一到多个范围进行存储。每个键值的存储范围大致包括以下几个部分。首先是索引的键值，接着存放当前范围的起始rowid和终止rowid，最后是这个键值在这个范围内的位置编码。将这个十六进制编码转化为二进制后，编码值是1的代表记录符合索引的键值，是0则表示不符合。由于保存了起始和终止的rowid以及在这个范围内的位置变化，因此通过转换，Bitmap索引也可以对应到rowid。所以，Bitmap索引可以提供和B-Tree索引相同的功能。

键值的每个存储范围的大小和分段依据，主要与每次DML操作影响的记录数和这些键值相同的记录在数据库中的物理分布有关。不过二者比较起来，前者的影响要大得多。下面用insert操作来举个例子。如果插入一条记录(insert into values)，那么Oracle会为这条记录中的键值建立一个范围，范围的大小是8条记录(Bitmap索引中的最小范围，所有Bitmap索引的范围必然是8的倍数。其他7条记录为空，可以被后续的插入操作继续使用)。如果执行一条批量插入语句(insert into select)，对于这条插入语句的中包含的每一个键值而言，都会建立一个范围。范围的大小为CEIL(操作记录数/8)*8。实际情况由于考虑利用原有范围内的空闲空间情况，以及考虑到键值的rowid分布等因素，要远比这里描述的复杂得多，而且对于delete和update操作而言，情况更为复杂。但是有一点是明确的，即Oracle会对批量插入(修改)的数据一起进行索引。如果一次性插入1000条键值相同的数据，则只会索引一次，并生成一个包括1000条记录的范围。如果一条一条地插入1000条数据，则会索引1000次，并生成125个包括8条记录的范围。无论是从效率的角度考虑还是从空间占用角度考虑，批量操作都是对包含Bitmap索引的表的首选。

Oracle的标准版不支持Bitmap索引。首先检查Oracle的版本和选项。

SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> SELECT * FROM V$OPTION WHERE PARAMETER LIKE 'Bit%';
PARAMETER                VALUE
------------------------ --------------
Bit-mapped indexes       TRUE

其实建立Bitmap索引的语法十分简单，只不过是在CREATE INDEX之间增加了Bitmap关键字而已，其他语法和B树索引几乎完全一样。

SQL> CREATE BITMAP INDEX IND_B_OWNER ON TEST_BITMAP(OWNER);
索引已创建

5. Bitmap 索引的特点

5.1 Bitmap索引比B树索引要节省空间

一般来说，Bitmap索引要比B树索引节省空间。因为B树索引要保存表中所有键值非空记录的rowid。而Bitmap索引采用分段记录的方法，它不会记录全部的rowid，而是只记录一个范围的起始地址和终止地址。

Bitmap 存储空间大小由以下几点决定：

• 表中总的记录的大小

这一点勿庸置疑，在其他条件相同的情况下，肯定是表的记录越多，Bitmap 索引的存储空间越大

• 索引列的键值多少

因为 Bitmap 索引对不同的键值分别存储。因此索引列的键值越多，所要占用的存储空间也就越多。

• 操作的类型

Bitmap索引的范围多少与操作数量有关。同样插入1000条数据，采用批量方式和分1000次操作插入，产生的范围数相差极大。批量操作要比单条记录操作节省大量的空间。

当然，Bitmap索引节省空间也不是绝对的。如果把Bitmap索引建立在接近惟一的列上，那么它占用的空间可能会比B树索引还要大。

5.2 Bitmap 索引建立的速度比较快

由于B树索引在建立时需要排序、定位等复杂的操作，而Bitmap索引并不需要排序，而且占用存储空间也较少，因此，建立Bitmap索引通常比建立B树索引更快。

一般来说，数据量越大，二者之间的速度差异越明显。

5.3 Bitmap 索引存储 NULL 值

和B树索引不一样，Bitmap索引存储NULL值，因此对NULL进行判断的查询可以使用Bitmap索引。由于存储NULL值，所以在计算表中的记录总数时，会优先使用Bitmap索引。

由于B树索引不记录空值，因此索引列的IS NULL操作必须通过全表扫描。而对于Bitmap索引列IS NULL操作则可以访问索引完成

6. Bitmap 索引的适用范围

Bitmap索引适合于包含键值数量有限，每个键值重复记录较多的列。Bitmap索引对于采用批量DML操作或无DML操作的列具有很高的效率。Bitmap索引适合多个限定条件通过AND或OR连接。

Bitmap索引更适用于数据仓库和决策支持系统，而对于OLTP系统，Bitmap索引并不是首选。数据仓库和决策支持系统绝大多数操作是复杂的查询操作，一般都会包含较多的查询条件。导入数据的操作也是采用批量导入的方式，几乎没有update和delete操作，这些正是使用Bitmap索引的优势所在。

反观OLTP系统，存在大量的并发事务，频繁插入、修改和删除记录。而这些操作都是Bitmap索引所应避免的操作。在OLTP系统中大量使用Bitmap索引可能导致记录被锁的机会大大增加，严重影响事务的并发性。

但是，并不是说OLTP系统不能使用Bitmap索引，对于那些数据很少发生变化的表，如果包含了大量重复键值，也可以建立Bitmap索引。根据TOM的观点，建立Bitmap索引的原则是根据实际情况去测试。

在建立Bitmap索引后要注意三点，第一点是批量操作，减少维护Bitmap索引的代价。第二点是在操作结束后马上提交，减少对表的锁定，同时应经常注意v$lock视图，检查包含Bitmap索引的表是否经常被锁。最后一点是注意收集统计信息，只有使优化器得到正确的信息，它才能做出正确的判断。