有關Bitmap Join Indexes的精彩帖二

全文摘自http://ningoo.itpub.net/post/2149/223616

Bitmap index的主要思想就是，針對每一個可能的值x，建立一個或一組點陣圖對映，每個bit為1代表這個位置的值等於x，為0則不等於x。而每個位置都可以直接對映到某一行的rowid。由於在執行DML操作時，鎖定的是整個bitmap，而不是bitmap中的某個位，所以bitmap index對於併發DML的效能很差，而且頻繁的DML操作會使得bitmap index的空間效率大打折扣，所以OLTP系統並不合適使用bitmap index。對於基本沒有DML操作，有大量ad hoc查詢的Data warehouse環境則相當有效。

從oracle9i 起，oracle又引進了一種新的索引型別：bitmap join index。和bitmap index建立在單個table上不同，bitmap join index是基於多表連線的，連線條件要求是等於的內連線(equi-inner join)。對於資料倉儲而言，較普遍的是Fact table的外來鍵列和相關的Dimension table的主鍵列的連線操作。

Bitmap join index能夠消除查詢中的連線操作，因為它實際上已經將連線的結果儲存在索引當中了。而且，相對於在表的連線列上建普通bitmap index來說，bitmap join index需要更少的儲存空間。

同樣的基於連線的Metarialized view也可以用來消除連線操作。但bitmap join index比起物化檢視來更有效率，因為透過bitmap join index可以直接將基於索引列的查詢對應到事實表的rowid。

以oracle的sample schema SH中的sales和customers表做個例子

1.建立基於維度表中一個列的bitmap join index
create bitmap index sales_cust_gender_bjix
on sales(customers.cust_gender)
from sales,customers
where sales.cust_id=customers;

建立這樣的bitmap join index後，下面的查詢就可以從index中直接得到結果，而不再需要連線sales和custmoers兩張表來獲得結果了。相當於根據連線條件，將customers表中的cust_gender列儲存到sales表中了。

select sum(sales.amount_sold)
from sales,customers
where sales.cust_id,customers.cust_id
and customers.cust_gender='M';

透過將bitmap join index dump出來可以看到，實際上，索引是按照customers.cust_gender分成2個點陣圖，每個點陣圖對映到sales表的ROWID。所以根據customers.cust_gender來過濾連線結果時，從索引中可以直接得到目標資料在sales中的rowid，無須執行join操作了。

一個可能的執行計劃如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3751 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=3751 Card=508136 Bytes=4573220)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP INDEX (SINGLE VALUE) OF 'IX_BITMAP'

2.建立基於一個維度表中多個列的bitmap join index
create bitmap index sales_cust_ms_bjix
on sales(customers.cust_gender,customers.cust_id)
from sales,customers
where sales.cust_id=customers.cust_id;

3.建立多個維度表到一個事實表的bitmap join indexcreate bitmap index sales_c_gender_p_cat_bjix
on sales(customers.cust_gender,products.prod_category)
from sales,customers,products
where sales.cust_id=customers.cust_id
and sales.prod_id=products.prod_id;

4.建立基於snowflake schme的bitmap join index
雪花模型的維度表被規範化為多個小表，也就是建index的時候需要額外連線其他幾個表
create bitmap index sales_co_country_name_bjix
on sales(countries.country_name)
from sales,countries,customers
where sales.country_id=countries.country_id
and sales.cust_id=customers.cust_id;

Bitmap join index的一些限制條件
只支援CBO

只能是equi-inner連線，任何外連線都無法使用bitmap join index

多個連線條件只能是AND關係

只能在fact table上執行並行DML。如果在dimension table上執行並行DML，會導致索引變為unusable狀態。

不同的事務中，只能併發更新一個表

在From字句中，任何一個表都不能出現兩次

在索引組織表(IOT)和臨時表上不能建立bitmap join index

索引只能基於dimenion table中的列

維度表用於連線的列只能是主鍵列或者是有唯一約束的列。
SQL> create bitmap index sales_cust_gender_bjix
2 on sales(customers.cust_gender)
3 from sales,customers
4 where sales.cust_id=customers.cust_id;
from sales,customers
*
ERROR at line 3:
ORA-25954: missing primary key or unique constraint on dimension

如果維度表的主鍵是組合主鍵，那麼連線條件需要是全部主鍵列都參與

其他對於bitmap index的限制條件同樣使用於bitmap join index，比如在分割槽表上只能是local，不能是global。

[@more@]