與B-tree索引的聯系及區別如下：
此外：
需要重申的是：
對於B-tree索引，CBO優化器會根據數據的具體分布情況，選擇是否應用索引。
對於位圖索引，即使讀取非常大的數據，仍然會選擇索引。
-- 查看測試數據表的結構
Yumiko@sunny >desc test01; Name Null? Type --------------------------------------------- -------- ---------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)
-- 查看表的總行數 Yumiko@sunny >select count(*) from test01; COUNT(*) ---------- 22928
-- 查看owner字段的數據分布情況，可以發現owner字段的含SYS的數據非常龐大，存在明顯的數據傾斜
-- 准備為owner字段分兩次，建立b-tree索引以及位圖索引，通過相同的查詢條件，比較索引的使用情況
Yumiko@sunny >select owner,count(*) from test01 group by owner order by count(*); OWNER COUNT(*) ------------------------------ ---------- BI 8 SCOTT 10 SYS 22910
--建立b-tree索引並驗證 Yumiko@sunny >create index BTREE_OWNER on test01(owner); Index created.
Yumiko@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = 'TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ BTREE_OWNER NORMAL TEST01
--打開會話根據，已查看執行計劃 Yumiko@sunny >set autotrace trace
--查詢owner字段為BI時，由於從較多數據中檢查個別數據，執行計劃用到了索引掃描，通過索引塊，快速定位目標數據的rowid，進而訪問數據塊獲得結果。此時效率最高
Yumiko@sunny >select * from test01 where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 725909888 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 8 | 1416 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BTREE_OWNER | 8 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='BI') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 40 recursive calls 0 db block gets 84 consistent gets 4 physical reads 0 redo size 2010 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
-- 查詢owner字段為SYS時，由於數據中該字段的數據存在明顯的數據傾斜，若通過索引方式檢索，將先掃面大部分的索引塊，然後再根據rowid查找數據塊，此時代價十分大，不如直接全表掃描效率高，因此不用索引。
Yumiko@sunny >select * from test01 where owner='SYS'; 22910 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 262542483 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22064 | 3813K| 159 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST01 | 22064 | 3813K| 159 (2)| 00:00:02 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 2288 consistent gets 8 physical reads 0 redo size 1148463 bytes sent via SQL*Net to client 17266 bytes received via SQL*Net from client 1529 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22910 rows processed
-- 刪除B-tree索引，創建位圖索引並查看 Yumiko@sunny >create bitmap index bitmap_owner on test01(owner); Index created. Yumiko@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = 'TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ BITMAP_OWNER BITMAP TEST01
-- 打開會話跟蹤，以查看執行計劃 Yumiko@sunny >set autotrace trace
-- 當查看owner為BI時，與b-tree一樣，使用索引掃描的方式進行檢索。 Yumiko@sunny >select * from test01 where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3098739824 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 55 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 8 | 1416 | 55 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | | | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"='BI') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 40 recursive calls 0 db block gets 82 consistent gets 0 physical reads 0 redo size 2010 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
--當查看owner為SYS時，此時與B樹索引不同，依然選擇了位圖索引
Yumiko@sunny >select * from test01 where owner='SYS'; 22910 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3098739824 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22064 | 3813K| 55 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 22064 | 3813K| 55 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | | | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"='SYS') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 2130 consistent gets 0 physical reads 0 redo size 2526059 bytes sent via SQL*Net to client 17266 bytes received via SQL*Net from client 1529 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22910 rows processed
從上面可以看到，對於位圖索引，即使從表中讀取很多行，也會使用索引。
對於位圖索引，當一個事務更新一條記錄的索引列鍵值，且未提交事務時，其他事務對於該索引鍵值涉及的數據行的該索引列值的修改，將處於等待。
--查看當前兩個會話的事件，無特殊事件
Yumiko@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username='SCOTT'; SID USERNAME PROGRAM EVENT ------- ----------------- ------------------------------------------------------------- 147 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client 153 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client
--查看表的前三行數據 Yumiko@sunny01 >select OWNER,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from test01 where rownum < 4; OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- SYS 20 2 TABLE SYS 44 44 INDEX SYS 28 28 TABLE
--發起事務1對於位圖索引列owner為SYS值的一行數據的owner字段的更新操作，但不提交事務。 Yumiko@sunny01 >update test01 set OWNER='SCOTT' where DATA_OBJECT_ID=28; 1 row updated.
--發起事務2對於位圖索引列owner為SYS值的另一行數據庫的owner字段的更新操作，此時事務停頓。 Yumiko@sunny02 >update test01 set OWNER='BI' where DATA_OBJECT_ID=44;
--查看此時會話的事件，發現有row lock等待事件 Yumiko@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username='SCOTT'; SID USERNAME PROGRAM EVENT --------- ------------------------------ ------------------------------------------------ 147 SCOTT sqlplus@OA01 (TNS V1-V3) enq: TX - row lock contention 153 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client
通過上面的示例證明了對於位圖索引列的更新，相應鍵值的位圖向量涉及的數據行在鍵值更新事務提交前，會始終處於鎖定狀態，其他事務無法對這些數據行的該索引列進行DML操作。
因此，對於DML操作頻繁度較高的OLTP數據庫而言，位圖索引不推薦使用。
