Oracle數據庫的位圖索引原理與應用

DOI：10.19850/j.cnki.2096-4706.2021.08.045

摘? 要：指出了執行查詢優化時，效果最顯著的方法是使用B樹索引，給出了使用B樹索引的限制條件，分析了位圖索引不同于B樹索引的主要特點，以實例驗證了位圖索引存儲的內容，得出了位圖索引的結構。設計簡潔的實驗步驟，驗證了使用位圖索引對查詢速度的提高幅度，以實例給出了位圖索引所占空間的大小，最后給出了不適合使用位圖索引的情況。

關鍵詞：Oracle數據庫;位圖索引;查詢優化

中圖分類號：TP311.13? ? ? 文獻標識碼：A 文章編號：2096-4706（2021）08-0159-03

Principle and Application of Bitmap Index in Oracle Database

LI Aiwu

（Guangdong Vocational College of Post and Telecom，Guangzhou? 510630，China）

Abstract：It is pointed out that the most effective way to perform query optimization is to use B-tree index，and the restrictions of using B-tree index are given. The main features of bitmap index that are different from B-tree index are analyzed，and the storage content of bitmap indexes is verified by examples，and obtains the structure of bitmap index. A concise experimental procedure is designed to verify the magnitude of improvement in query speed by using bitmap index. The size of space occupied by bitmap index is given with examples，and finally，the cases in which the use of bitmap indexes is not suitable are given.

Keywords：Oracle database;bitmap index;query optimization

0? 引? 言

使用B樹索引要求索引列值的重復率要很低，但在實際應用中，列值重復率高的情況是很常見的，如性別字段只有“男”“女”兩個列值，日期的月份部分只有12個整數值等，季度部分只有4個值等。在查詢中，這些字段一般用作計數條件，以下為一些典型的查詢：

（1）查詢員工表中的男性員工數量。

（2）2005年入職的員工總數。

（3）5號部門的員工總數。

對于以上這些查詢，在相關字段上創建B樹索引，查詢效率不會得到顯著提高，位圖索引恰恰適合這類要求，在處理相關統計操作時，Oracle可以通過掃描位圖索引，并執行位運算，從而可以快速定位滿足條件的記錄。

在當前主流數據庫產品中，只有Oracle支持位圖索引。文中實驗使用的軟件版本為Oracle Database 12c企業版。

1? B樹索引的結構及適用場景

B樹索引在邏輯上可以看作一顆倒立的樹，這棵樹分為若干層，頂層是樹根，底層是樹葉，中部是樹枝。除樹根由一個節點構成外，其他每層都由一個或多個節點構成，每個節點是一個數據塊。根據節點所在的層次，分別稱為根節點，分支節點及葉節點。B樹索引的葉節點存儲排序后的索引列值以及列值所在記錄的地址，根節點與分支節點可以看作是下層索引的索引，存儲其下層每個節點的索引列值范圍及其所在數據塊的塊號。

記錄的地址即rowid，由記錄的文件號、數據頁號、數據頁中的槽號（slot）三個屬性構成。由此地址可以直接定位至此行的數據。

以下幾種情況，應考慮創建B樹索引：

（1）在列值重復率低的情況下，應在查詢條件包含的列上創建B樹索引，以提高查詢效率。

（2）若某個字段的函數值重復率低，而且頻繁以此函數值作為查詢字段，則應在此列上創建基于函數的索引，以提高查詢效率。

（3）在連接查詢中，大表所包含的連接字段應創建B樹索引。

2? 位圖索引的結構

相比B樹索引，位圖索引的結構很簡單，沒有使用樹形結構。在數據塊中存儲的每行索引數據主要由以下三部分構成：

（1）索引鍵值。

（2）由兩個rowid表示的記錄地址范圍。

（3）位圖。

位圖部分中的每個位依序對應第二部分所描述范圍內的一個rowid，它用于描述在以上rowid范圍內，哪些記錄的索引列取得了相應索引鍵值，如果取得鍵值則為1，否則為0。

假定表t共有8行記錄，其數據存儲于4號文件的87號數據塊，表t有一個a列，其列值只有red、green、blue三個。表中的記錄共8行，其中第1、3行的a列值為red，第2、4、5行的a列值為green，第6、7、8行的a列值為blue。若在a列上創建位圖索引，則其包含三行數據，每行數據的三部分內容如表1所示。

查詢某個位圖索引列值的行數時，只要計算位圖索引中對應此值位圖中的1的個數即可。

3? 位圖索引內容的驗證

創建測試表t，其位圖索引列為a：

SQL> create table t（a char（5），b char（3））

2? tablespace users

3? /

依次執行以下命令，對t表添加測試數據：

insert into t values（'red'，'aaa'）;

insert into t values（'green'，'bbb'）;

insert into t values（'red'，'ccc'）;

insert into t values（'green'，'ddd'）;

insert into t values（'green'，'eee'）;

insert into t values（'blue'，'fff'）;

insert into t values（'blue'，'ggg'）;

insert into t values（'blue'，'hhh'）;

創建表空間，以存儲位圖索引數據：

SQL> create tablespace idxbittbs

2? datafile 'e：＼oradata＼idxbittbs.dbf'

3? size 1m

4? /

創建位圖索引：

SQL> create bitmap index idx_bit_t on t（a）

2? tablespace idxbittbs

3? /

執行以下命令把內存修改數據寫入磁盤：

SQL> alter system flush buffer_cache;

使用十六進制原始數據查看工具（如WinHex）中打開數據文件e：＼oradata＼idxbittbs.dbf，定位至字符串red后，位圖數據如圖1框中部分所示。

取出其中的第二行數據來分析，其偏移量范圍為106 390至106 414，其內容如表2所示。

查詢t表中各記錄的地址，可以進一步驗證以上數據。

SQL> select a，

2? '（'

3? ||dbms_rowid.rowid_relative_fno（rowid）

4? ||'：'

5? ||dbms_rowid.rowid_block_number（rowid）

6? ||'：'

7? ||dbms_rowid.rowid_row_number（rowid）

8? ||'）'

9? as "rowid"

10? from t

11? /

A? ? ?rowid

----- -----------------------------------------

blue? （4：87：5）

blue? （4：87：6）

blue? （4：87：7）

green （4：87：1）

green （4：87：3）

green （4：87：4）

red? ?（4：87：0）

red? ?（4：87：2）

已選擇8行。

4? 使用位圖索進行查詢優化

測試表big_table有10 000 000行記錄，添加記錄時，已經設置其id2字段的列值只有3個不同的值：0、1、2。如果使用id2列作為計數條件，則此列應該創建位圖索引。

執行以下命令在id2字段創建位圖索引：

SQL> conn system/oracle

已連接。

SQL> create bitmap index idx_bit_id2 on big_table（id2）

2? tablespace idxtbs

3? /

設置以上位圖索引為失效狀態：

SQL> alter index idx_bit_id2 invisible;

執行以下查詢，注意其執行時間：

SQL> set timing on

SQL> select count（*） from big_table

2? where id2=1

3? /

COUNT（*）

----------

3333334

已用時間：? 00： 00： 48.25

id2上沒有索引，上述查詢只能使用全表掃描。

重新設置索引為可用狀態：

SQL> alter index idx_bit_id2 visible;

索引已更改。

再次執行以上相同的查詢，可以發現此時的執行時間大大降低：

SQL> select count（*）

2? from big_table

3? where id2=1

4? /

COUNT（*）

----------

3333334

已用時間：? 00： 00： 00.00

5? 位圖索引占用的空間

位圖索引占用空間很少，可以用以下查詢驗證以上位圖索引占用空間的大小：

SQL> select sum（bytes）/1024/1024 as "SIZE（MB）"

2? from dba_segments

3? where segment_name='IDX_BIT_ID2'

4? /

SIZE（MB）

----------

6.0625

與B樹索引相比，位圖索引占用的空間基本可以忽略不計了。

6? 位圖索引不同于B樹索引的特點

與B樹索引相比，位圖索引具備以下特點：

（1）位圖索引只存儲鍵值記錄的物理地址范圍及位圖，需要的存儲空間非常少。

（2）創建B樹索引時，需要執行排序操作，而創建位圖索引時，不需要排序，創建速度會比較快。

（3）與B樹索引不同，位圖索引存儲空鍵值，查詢索引列為空的行，可以使用索引。

（4）對表執行計數統計時，直接訪問索引即可快速得出結果。

（5）對于B樹索引，修改鍵值時，只會鎖定鍵值所在的行。而對于位圖索引，修改鍵值時，會鎖定其鍵值所在的整個位圖范圍，若其他連接也修改此范圍的鍵值，則會發生等待。

（6）位圖索引應建立在列值重復率很高的列上，一般情況下，外鍵列上應創建位圖索引。

7? 結? 論

如果需要以表的某個字段執行計數統計，并且這個字段的列值重復率高，則這個字段應該創建位圖索引。相對B樹索引，位圖索引占用空間少。修改位圖索引列值時，會同時對原值和新值所在的位圖片段加鎖，從而大幅降低操作效率，因此，在常用的OLTP應用中應避免創建位圖索引。

參考文獻：

[1] VAIDYANATHA G K，DESHPANDE K，JR J A k，et al. Oracle Performance Tuning 101 [M].Bangor：Osborne Media，2001.

[2] RICHARD N. Oracle 10g Database Performance Tuning Tips & Techniques [M].New Bangor：Osborne Media，2007.

[3] DONALD K，BURLESON. Oracle High Performance SQL Tuning [M].New York City： McGraw-Hill Education ，2001.

[4] CONWAY H，AULT M，BURLESON D. Oracle Tuning Power Tuning Scripts [M].Kittrell：Rampant Techpress，2005.

[5] LEWIS J. Cost-Based Oracle Fundamentals [M].[S.L]：Apress，2005.

[6] CELKO J. SQL for Smarties：Advanced SQL Programming [M].3rd ed.San Francisco：Morgan Kaufmann，2005.

作者簡介：李愛武（1969.07—），男，河北肅寧人，副教授，理學碩士，研究方向：數據庫技術，數據分析。

收稿日期：2021-02-12