【索引分类】位图索引

位图索引

位图索引非常适合于决策支持系统(Decision Support SystemDSS)数据仓库,它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列,但通常它们都只用于少量的列。例如,您的表可能包含一个称为Sex的列,它有两个可能值:男和女。这个基数只为2,如果用户频繁地根据Sex列的值查询该表,这就是位图索引的基列。当一个表内包含了多个位图索引时,您可以体会到位图索引的真正威力。如果有多个可用的位图索引,Oracle就可以合并从每个位图索引得到的结果集,快速删除不必要的数据。





场合:列的基数很少,可枚举,重复值很多,数据不会被经常更新。

原理:一个键值对应很多行(rowid), 格式:键值  start_rowid   end_rowid 位图。

优点:OLAP 例如报表类数据库、重复率高的数据、特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果。

缺点:不适合重复率低的字段,还有经常DML操作(insert,update,delete),因为位图索引的锁代价极高,修改一个位图索引段影响整个位图段,例如修改一个键值,会影响同键值的多行,所以对于OLTP系统位图索引基本上是不适用的。

Bitmapt特点:

适合与决策支持系统;

做UPDATE代价非常高;

非常适合OR操作符的查询;

基数比较少的时候才能建位图索引;

技巧:对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个可能值:男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快,这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引,所以非常小,因此您可以经常检索表中超过半数的行,并且仍使用位图索引。

当大多数条目不会向位图添加新的值时,位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引,在大多数事务处理应用程序中都会发生这种情况。

示例

下面来看一个示例表PARTICIPANT,该表包含了来自个人的调查数据。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位图索引。下图显示了每个直方图中的数据平衡情况,以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并,可以看出性能得到了显著的提高。


如上图图所示,优化器依次使用4个单独的位图索引,这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1),用于指示表中的哪些行包含位图中的已知值。有了这些信息后,Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值,并且查询继续完成剩余的处理工作。注意,所有4个列都有非常低的基数,使用索引可以非常快速地返回匹配的行。

 

技巧:在一个查询中合并多个位图索引后,可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

 

下面的查询可显示索引类型。

SQL>select index_name, index_type from user_indexes;

INDEX_NAME         INDEX_TYPE

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

TT_INDEX            NORMAL

IX_CUSTADDR_TP    NORMAL

B树索引作为NORMAL列出;而位图索引的类型值为BITMAP

 

技巧:如果要查询位图索引列表,可以在USER_INDEXES视图中查询index_type列。

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

 

位图索引的使用限制:

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

当执行ALTERTABLE语句并修改包含有位图索引的列时,会使位图索引失效。

位图索引不包含任何列数据,并且不能用于任何类型的完整性检查。

位图索引不能被声明为唯一索引。

位图索引的最大长度为30

 

*技巧:不要在繁重的OLTP环境中使用位图索引

 

【实验】位图索引和B-tree索引的性能比较

set pagesize100;   设置页大小

利用dba_objects数据字典创建一个15万行的表

LS@LEO>create table leo_bm_t1 as select * from dba_objects;

Table created.

LS@LEO>insert into leo_bm_t1 select * from leo_bm_t1; 翻倍插入

9876 rowscreated.

LS@LEO> /

19752 rowscreated.

LS@LEO> /

39504 rowscreated.

LS@LEO> /

79008 rowscreated.

LS@LEO> /

158016 rowscreated.

因object_type字段重复值较高,顾在此字段上创建bitmap索引

LS@LEO>create bitmap indexleo_bm_t1_index on leo_bm_t1(object_type);

Index created.

创建一个和leo_bm_t1表结构一模一样的表leo_bm_t2,并在object_type列上创建一个B-tree索引(15万行记录)

LS@LEO>create table leo_bm_t2 as select * from leo_bm_t1;

Table created.

LS@LEO>create indexleo_bm_t2_bt_index on leo_bm_t2(object_type);

Index created.

对比位图索引和B-tree索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间,节约空间特性也是我们选择位图的理由之一

LS@LEO> selectsegment_name,bytes from user_segments where segment_type='INDEX';

SEGMENT_NAME                                                                          BYTES

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

LEO_BM_T1_INDEX                                                                      327680(327K)

LEO_BM_T2_BT_INDEX                                                                   7340032(7M)

显示执行计划和统计信息

set autotracetrace exp stat;

在创建有位图索引的表上做count操作对比执行计划

LS@LEO> selectcount(*) from leo_bm_t1 where object_type='TABLE';

ExecutionPlan  执行计划

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

Plan hash value:3251686305

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

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                 |     1 |   11 |     4  (0)| 00:00:01 |

|   1 | SORT AGGREGATE             |                 |     1 |   11 |            |          |

|   2 |  BITMAP CONVERSION COUNT   |                 | 36315 |   390K|    4   (0)| 00:00:01 |

|*  3 |    BITMAP INDEX SINGLE VALUE|LEO_BM_T1_INDEX |       |       |            |          |

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

位图索引上只扫描了一个值

PredicateInformation (identified by operation id):

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

   3 - access("OBJECT_TYPE"='TABLE')

Note

-----

   - dynamic sampling used for this statement 动态采样

Statistics   统计信息

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

          9 recursive calls

          0 db block gets

         93 consistent gets  oracle选择使用位图索引访问数据,导致93次一致性读

          7 physical reads

          0 redo size

        413 bytes sent via SQL*Net to client

        381 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

在创建有B-tree索引的表上做count操作对比执行计划

LS@LEO> selectcount(*) from leo_bm_t2 where object_type='TABLE';

ExecutionPlan   执行计划

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

Plan hash value:613433245

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

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                    |     1 |   11 |    59  (0)| 00:00:01 |

|   1 | SORT AGGREGATE   |                    |     1 |   11 |            |          |

|*  2 |   INDEX RANGE SCAN|LEO_BM_T2_BT_INDEX | 25040 |   268K|    59  (0)| 00:00:01 |

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

B-tree索引上全部扫描,cpu使用率达到了59%,比位图索引cpu使用率4%高出许多

PredicateInformation (identified by operation id):

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

   2 - access("OBJECT_TYPE"='TABLE')

Note

-----

   - dynamic sampling used for this statement 动态采样

 

Statistics   统计信息

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

         32 recursive calls

          0 db block gets

        161 consistent gets  B-tree索引表上发生了161次一致性读要远远高于位图索引表上93次一致性读,因此还是位图索引效率高

         74 physical reads

          0 redo size

        413 bytes sent via SQL*Net to client

        381 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

我们再看看等值查找where object_type='TABLE'情况下位图索引和B-tree索引的性能对比

LS@LEO> select* from leo_bm_t1 where object_type='TABLE' ;

28512 rows selected.

ExecutionPlan  执行计划

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

Plan hash value:4228542614

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

| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                 | 36315 |  6277K|  562   (0)| 00:00:07 |

|   1 | TABLE ACCESS BY INDEX ROWID | LEO_BM_T1       | 36315 |  6277K|  562   (0)| 00:00:07 |

|   2 |   BITMAP CONVERSION TO ROWIDS|位图映像->rowids |       |       |            |          |

|*  3 |    BITMAP INDEX SINGLE VALUE| LEO_BM_T1_INDEX |       |       |            |          |

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

PredicateInformation (identified by operation id):

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

   3 - access("OBJECT_TYPE"='TABLE')

Note

-----

   - dynamic sampling used for this statement动态采样

Statistics   统计信息

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

          7 recursive calls

          0 db block gets

       4407 consistent gets  使用位图索引发生了4407次一致性读

          0 physical reads

          0 redo size

    2776927 bytes sent via SQL*Net to client

      21281 bytes received via SQL*Net from client

       1902 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

      28512 rows processed

leo_bm_t2表上使用B-tree索引得到执行计划

LS@LEO> select/*+index(leo_bm_t2 leo_bm_t2_bt_index) */ * from leo_bm_t2 whereobject_type='TABLE' ;

28512 rowsselected.  我们强制使用B-tree索引扫描等值条件

ExecutionPlan  执行计划

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

Plan hash value:1334503202

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

| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                    | 25040 |  4328K| 2063   (1)| 00:00:25 |

|   1 |  TABLE ACCESS BY INDEX ROWID|LEO_BM_T2          | 25040 |  4328K| 2063   (1)| 00:00:25 |

|*  2 |   INDEX RANGE SCAN          | LEO_BM_T2_BT_INDEX | 25040 |       |   59   (0)| 00:00:01 |

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

B-tree索引上全部扫描,cpu使用率达到了2063%,比位图索引cpu使用率562%高出许多

PredicateInformation (identified by operation id):

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

   2 - access("OBJECT_TYPE"='TABLE')

Note

-----

   - dynamic sampling used for this statement

 

Statistics

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

          7 recursive calls

          0 db block gets

       6621 consistent gets 

          0 physical reads

          0 redo size

    2776927 bytes sent via SQL*Net to client

      21281 bytes received via SQL*Net from client

       1902 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

      28512 rows processed

小结:在等值查找中我们可以看出位图索引的效率依言高于B-tree索引


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值