B-树索引与位图索引

/*刚从书上看到了位图索引，自己经过了测试，于是现学现卖，希望对还不了解的人有所帮助*/

 

B-树索引在Oracle中是一个通用的索引，在创建索引时它就是默认的索引类型。最多可以包括32列。

 

位图索引Oracle为每个唯一键创建一个位图，然后把与键值所关联的ROWID保存为位图。最多可以包括30列。

 

一般情况下，大多数用户都只创建TYPE为NORMAL的B-树索引，所以对于较低基数的列我们都是不创建索引的，因为B-树索引对查询速度提升不一定会有改善，甚至会增加Insert、Update、Delete命令所消耗的时间。下面我们通过一个例子来比较B-树索引与位图索引在对查询速度提升的效果。

 

SQL>--例如下面一个测试表有534888条记录

SQL> select count(*) from henry_test;

 

  COUNT(*)

----------

534888

 

SQL> --没有索引的情况（耗时1.631秒）

SQL> select count(*) from henry_test where payment_method<'91';

 

  COUNT(*)

----------

371466

 

SQL> --建立B-树索引

SQL> create index idx_henry_test1 on henry_test(payment_method);

 

Index created

SQL> --使用提示强制使用索引

SQL> select /*+ index(henry_test IDX_HENRY_TEST1)*/count(*) from henry_test where payment_method<'91';

 

  COUNT(*)

----------

371466

SQL>--（耗时0.181秒）

SQL> --删除索引

SQL> drop index IDX_HENRY_TEST1;

 

Index dropped

 

SQL> --因为payment_method这个列的基数非常的低，只有5种值，所以非常适合使用位图索引

SQL> --建立位图索引

SQL> create bitmap index IDX_HENRY_TEST1 on HENRY_TEST (PAYMENT_METHOD);

 

Index created

 

SQL> --使用提示强制使用索引

SQL> select /*+ index(henry_test IDX_HENRY_TEST1)*/count(*) from henry_test where payment_method<'91';

 

  COUNT(*)

----------

371466

SQL>--（耗时0. 01秒）

SQL> --删除索引

SQL> drop index IDX_HENRY_TEST1;

 

Index dropped

 

大家可以看到位图索引比B-树索引的速度提高了十多倍。

 

下面2段文字是我直接从书上抄的，见笑了。

         位图索引在加载表（插入操作）时通常要比B-树索引做得好。通常，位图索引要比一个低基数（很少不同值）上的B-树索引要快3~4倍，但如果新增的值占插入行的70%以上时，B-树索引通常会更快一些。当每条记录都增加一个新值时，B-树索引要比位图索引快3倍。

         建议不要在一些联机事务处理（OLTP）应用程序中使用位图索引。B-树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引被存储为压缩的索引值，其中包含了一个范围内的ROWID，因此ORACLE必须针对一个给定值锁定所有范围内的ROWID。这种锁定可能自阿某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。

         位图索引有很多限制：

1、   基于规则的优化器不会考虑位图索引

2、   当执行ATLER TABLE语句，并修改包含有位图索引的列时，会使位图索引实效

3、   位图索引在索引块中储存了索引键的值；然而，他们并不能用户任何类型的完整性检查

4、   位图索引不能被申明为唯一索引