Bitmap Indexes 學習與測試_20091213

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檔案來研究。先暫不深究。