Bitmap Indexes 学习与测试

<>P300

<< Performance >>

<>

<> P448

目录

1 Overview of Bitmap index

2 Bitmap index 的特性

3 位图索引如何存储

4 Bitmap Join Indexes

5 Notes

1 Overview of Bitmap index

In a regular index, Oracle stores each key value repeatedly with each stored rowid. In a Bitmap index, a bitmap for each key values is used instead of a list of rowids. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping functions converts the bit position to an actual rowid.

(普通索引中是(keyvalue,rowid)的二元组，而位图索引中是(keyvalue, bitmap)的二元组，Oracle通过函数来将bit映射到相应的rowid)

2 Bitmap index 的特性

Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically. (合并WHERE字句中的多个条件)

1)     A substantial reduction of space use compared to other indexing techniques.(节省空间)

2)     Bitmap indexes are suitable for OLAP applications but not OLTP.(适合在OLAP系统而不是OLTP系统)

3)     Bitmap indexes are not suitable for columns that are primarily queried with less than or greater than compressions. But only useful for AND, OR, NOT or EQUALITY queries.(不适合大于，小于这类的比较操作符，但适合and, or, not, = 操作符)

4)     Bitmap indexes are greatest for lowest cardinality columns. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.

(适合Cardinality低的列，即列的重复度高. Cardinality 与Selectivity 是相反的意思。而B-tree索引则适合Cardinality高的列，即列的多样性高。一般来讲，当Cardinality 低于1%时，表示适合建Bitmap Index, 例如婚姻状况，性别这类的列就是合适的。当然所在的表不能更新太多，Cardinality只是一个必要条件，而不是一个充分条件.)

5)  Bitmap indexes include rows that have NULL values, unlike most other types of indexes. (位图索引能索引Null值)

6) Bitmap indexes for partitioned table must be local not global. (分区表上的位图索引只能是Local的，不能是Global)

7) 位图索引在读密集的环境中能很好地工作，但是对于写密集的环境则极不适用。原因在于，一个位图索引键条目指向多行。如果一个会话修改了所索引的数据，那么在大多数情况下，这个索引条目指向的所有行都会被锁定。Oracle无法锁定一个位图索引条目中的单独一位。这样将大大影响并发性。

8) We cannot create a bitmap index with the UNIQUE attribute.(不能建立唯一性的位图索引)

3 位图索引如何存储

位图索引是这样一种结构，其中有一个索引键条目存储指向多行的指针；这与B-tree结构不一样，在B-tree树结构中，索引键和表中的行存在一一对应关系。在位图索引中，可能只有很少的索引条目(等于Distinct Count)，每个索引条目指向多行.

4 Bitmap Join Indexes

简单的理解就是将几个表联结查询后的结果建索引

Create bitmap index emp_bm_idx on emp(d.dname)

From emp e, dept d

Where e.deptno = d.deptno;

这个语句在EMP 表的上下文中，对DEPT.Dname建立了索引。这样对于类似这样的查询

Select emp.*

From emp, dept

Where emp.deptno = dept.deptno

And dept.dname = ‘XXX’

就不必再访问dept表了，使用EMP上的索引就能从DEPT中合并出我们需要的数据。

一些条件限制。除了同样必须符合Bitmap索引的条件限制外，还有一些条件：

1) Joins form. either star or snowflake schema (什么意思) and all joins are through primary keys or keys with unique constraints as follows. 联结条件必须联结到另一个表中的主键或唯一键

2）  The bitmap join index is on a single table

3) No table can appear twice in the FROM clause.

4) All joins are equi-inner joins and they are connected by Ands only.

5)     A bitmap join index must not be partitioned if the fact table is not partitioned

5 Notes

关于Bitmap Index的物理结构，可通过Dump文件来研究。先暂不深究。

http://dbsnake.com/2009/08/bitmap-index-struc-con.html