bitmap index點陣圖索引系列(一)

bitmap index
 1,bitmap index為每個過資訊化鍵儲存一個點陣圖，而在b-tree index中,一個索引條目對應一條記錄；
   但對點陣圖索引條目（或鍵值）卻指向多條記錄
 2,bitmap index適用於dw和查詢引用多條列
                     低基數的列
 3,點陣圖中每個bit對應不同的rowid.如配置了bit,相應的rowid對應的記錄會包括這個鍵值.
 4,oracle有個對映函式把特定位置上的bit轉化為實際的rowid.
 5,bitmap index和b-tree index實現相同的功能，但採用不同的實現
 6,如果更新某一行的索引列時，資料庫鎖住索引鍵值條目（比如性別列：男，女),
   上面說了，點陣圖索引每個鍵對應多條記錄.所以會一下鎖定多條記錄，所以點陣圖索引不適應大量的oltp
  
 7,bitmap index可用於null的統計，而b-tree index則不能
 
 8，sample bitmap
     如一個表有3行記錄，且性別列建立點陣圖索引
     性別列值：男，女
     則採用2個點陣圖
     每個點陣圖採用bit設定，配置為1或0對應男和女
    
     如表有3條記錄則為：
    
     點陣圖索引列的值  記錄1  記錄2 記錄3 
     男              1       0     1
     女              0       1     0
    
     然後oracle對映函式把上面點陣圖中的每個bit轉換為錶行對應的rowid

--點陣圖索引如索引列為null依舊可以走索引    
SQL> explain plan for select count(sex) from t_detail where sex is null;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2526431793
--------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     2 |     1   (
|   1 |  SORT AGGREGATE               |              |     1 |     2 |
|   2 |   BITMAP CONVERSION TO ROWIDS |              |     3 |     6 |     1   ( --用oracle internal function轉化bitmap為rowid
|*  3 |    BITMAP INDEX FAST FULL SCAN| IDX_T_DETAIL |       |       |           --先掃描到點陣圖索引的索引塊
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("SEX" IS NULL)    
                          
                          
---bitmap join index
1,用於多表關聯
2,建立於單表之上
3,用於避免多表關聯產生的資料量或查詢成本；
4,示例：
SELECT COUNT(*)
FROM   employees, jobs
WHERE  employees.job_id = jobs.job_id
AND    jobs.job_title = 'Accountant';

--可以建立bitmap join index
CREATE BITMAP INDEX employees_bm_idx
ON     employees (jobs.job_title) --基於表employee但括號內卻是另一個join table的where條件即jobs.job_titile
FROM   employees, jobs --bitmap join index的資料來源，即employees,jobs
WHERE  employees.job_id = jobs.job_id; ---資料來源filter,即二表關聯列

bitmap join index的好處，不用再關聯二表因為上述索引已經具備這種資料了

bitmap join index在dw環境下，可用於代替join mv

--------------示例
--二表結構如下

SQL> desc emp;
Name  Type          Nullable Default Comments
----- ------------- -------- ------- --------
EMPNO INTEGER       Y                        
ENAME VARCHAR2(100) Y                        
JOBNO INTEGER                                
 
SQL> desc job;
Name    Type          Nullable Default Comments
------- ------------- -------- ------- --------
JOBNO   INTEGER       Y                        
JOBNAME VARCHAR2(100) Y

--報錯因為維度表job(dw概念，即可以用連線列jobno唯一定位記錄的表，在此為job表,必須建立唯一索引或主鍵
SQL> create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno;
 
create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno
 
ORA-25954: missing primary key or unique constraint on dimension
 
SQL> alter table job add primary key(jobno);
 
Table altered
 
SQL> create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno;
 
Index created
 

SQL> truncate table emp;
 
Table truncated

--對事實表，即與維度表相反，用連線列不能唯一確定表記錄的表，在此為emp表
SQL> alter table emp add primary key(jobno);
 
Table altered

--對事實表建立唯一索引無用
SQL> create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno;
 
create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno
 
ORA-25954: missing primary key or unique constraint on dimension

Bitmap join index的一些限制條件
只支援CBO
 
只能是equi-inner連線，任何外連線都無法使用bitmap join index
 
多個連線條件只能是AND關係
 
只能在fact table上執行並行DML。如果在dimension table上執行並行DML，會導致索引變為unusable狀態。
 
不同的事務中，只能併發更新一個表
 
在From字句中，任何一個表都不能出現兩次
 
在索引組織表(IOT)和臨時表上不能建立bitmap join index
 
索引只能基於dimenion table中的列
 
維度表用於連線的列只能是主鍵列或者是有唯一約束的列。

--使用bitmap join index的效果對比

--未使用執行計劃
SQL> explain plan for select count(emp.empno) from emp,job where emp.jobno=job.jobno and job.jobname='dba';
 
Explained
 

 
SQL>  select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3521430431
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    20 |            |          |
|*  2 |   HASH JOIN         |      |     1 |    20 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| JOB  |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP"."JOBNO"="JOB"."JOBNO")
   4 - filter("JOB"."JOBNAME"='dba')
 
17 rows selected

--建立bitmap join index之後執行計劃
SQL> alter table job add primary key(jobno);
 
Table altered
 
SQL> create bitmap index idx_join_test on emp(job.jobname) from emp,job where emp.jobno=job.jobno;
 
Index created

SQL> explain plan for select count(*) from emp,job where emp.jobno=job.jobno and job.jobname='dba';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1092048613
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    13 |     1   (0
|   1 |  SORT AGGREGATE             |               |     1 |    13 |
|   2 |   BITMAP CONVERSION COUNT   |               |     1 |    13 |     1   (0
|*  3 |    BITMAP INDEX SINGLE VALUE| IDX_JOIN_TEST |       |       |             --不用回表了，直接自bitmap join index獲取
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMP"."SYS_NC00004$"='dba')
 
15 rows selected

小記：但bitmap join index僅適用於dw
            且select要是count,sum等，如非此，它就用不上了