Oracle編程高手箴言：位圖索引(Bitmap Index)的故事

gunguymadman

您如果熟悉Oracle數(shù)據(jù)庫(kù)，我想您對(duì)Thomas Kyte的大名一定不會(huì)陌生。Tomas主持的asktom.oracle.com網(wǎng)站享譽(yù)Oracle界數(shù)十年，絕非幸致。最近在圖書館借到這位Oracle絕頂高手編著的《Expert Oracle Database Architecture-9i and 10g programming Techniques and Solutions》，翻閱之下，果然盛名無(wú)虛，雖然說(shuō)不上字字珠璣，但作者對(duì)Oracle架構(gòu)的理解和實(shí)踐確實(shí)已達(dá)到出神入化的境界。如果您有時(shí)間和興趣，強(qiáng)烈建議您閱讀這本書。這本書最大的特點(diǎn)是語(yǔ)言生動(dòng)活潑，說(shuō)理清楚，幾乎每講解一個(gè)原理，作者都給出了具體實(shí)例，讓人讀起來(lái)毫不氣悶。

另外，Thomas謙遜的態(tài)度讓我非常佩服，Thomas在Oracle數(shù)據(jù)庫(kù)方面工作了16年，并且參與了早期Oracle版本的開發(fā)，但他仍然謙虛地說(shuō)，他每天都能從Oracle文檔里學(xué)到新的東西。

下面從這本書里摘錄了一些精彩片段，供您欣賞，雖然不免有斷章取義之嫌。

位圖索引(Bitmap Index)的故事

一日，一群Java開發(fā)者找到Tom先生，說(shuō)他們新開發(fā)的系統(tǒng)已經(jīng)上線，但性能及其低下，他們問(wèn)Tom先生能不能替他們看看問(wèn)題到底出在什么地方。他們告訴Tom，他們的系統(tǒng)采用JSP+EJB+Oracle的典型三層架構(gòu)，其中EJB中的SQL是由第三方工具產(chǎn)生的。Tom同志一聽到EJB，就知道這個(gè)系統(tǒng)是不能采用SQL代碼跟蹤的方法來(lái)進(jìn)行性能調(diào)優(yōu)了。于是，Tom同志告訴這些心急火燎的Java開發(fā)者，你們系統(tǒng)的問(wèn)題肯定在瀏覽器到數(shù)據(jù)庫(kù)之間，但具體問(wèn)題出在什么地方，我需要看看你們的數(shù)據(jù)庫(kù)。

于是，Tom同志遠(yuǎn)程連接到他們的測(cè)試數(shù)據(jù)庫(kù)（注意不是生產(chǎn)數(shù)據(jù)庫(kù)），查看了幾個(gè)動(dòng)態(tài)性能視圖(V$LOCK和V$SQL)，最后終于發(fā)現(xiàn)了問(wèn)題的所在。Tom同志發(fā)現(xiàn)他們的數(shù)據(jù)庫(kù)中有一個(gè)位圖索引(Bitmap Index)最為可疑，這個(gè)索引是建立在一個(gè)PROCESS_FLAG的字段上。PROCESS_FLAG字段表示該記錄是否被處理了，可能值只有兩個(gè)，一個(gè)是未處理(N)，一個(gè)是已經(jīng)處理(Y)。當(dāng)記錄初次插入數(shù)據(jù)庫(kù)時(shí)，該字段的值為N，但其它進(jìn)程讀取并處理那些未處理的記錄(值為N的記錄)后，這個(gè)字段的值就更新為Y。

Tom就問(wèn)這些Java開發(fā)者，你們?yōu)槭裁匆�在這個(gè)PROCESS_FLAG字段上建立位圖索引呢？

其中有一個(gè)開發(fā)者振振有詞的說(shuō)，這是為了提高查找速度，一旦建立了位圖索引，我們的程序就能快速找到那些數(shù)值為N的記錄，然后處理。隨后，他又拿出一本大部頭的Oracle數(shù)據(jù)庫(kù)參考手冊(cè)，對(duì)Tom同志說(shuō)，這書上都是這么說(shuō)的，對(duì)那些數(shù)值非常少的字段，比如，我們的PROCESS_FLAG字段只有兩個(gè)值，就應(yīng)該建立位圖索引，這難道有什么問(wèn)題嗎？

Tom同志微微一笑，沒有直接回答。只見他打開SQL Plus，連接到他的本地Oracle實(shí)例，給這群開發(fā)者演示了下面及其簡(jiǎn)單的SQL代碼。

C:/Documents and Settings/carlwu>sqlplus scott/tiger@carl

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 23 18:15:34 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t(processed_flag varchar2(1));

Table created.

SQL> create bitmap index t_idx on t(processed_flag);

Index created.

SQL> insert into t values('N');

1 row created.

SQL>

剛才那位振振有詞的開發(fā)者不服氣的說(shuō)，這有什么，不是很正常嗎？接著Tom又打開了一個(gè)SQL Plus窗口，并連接到本地?cái)?shù)據(jù)庫(kù)，鍵入下面的SQL語(yǔ)句，奇怪的是這條SQL并不執(zhí)行，而是一直在等待。下面是這條SQL的一個(gè)截圖：

這些Java開發(fā)者看到這里，驚訝得目瞪口呆。其中一個(gè)開發(fā)者猶豫地說(shuō)，好象這個(gè)位圖索引只允許一個(gè)用戶操作，如果其它用戶想同時(shí)操作這個(gè)索引，那他必須等第一個(gè)用戶的請(qǐng)求處理完成，并且提交之后，才能進(jìn)行，如果第一個(gè)用戶不提交，那么他必須一直等待。Tom點(diǎn)頭表示贊同，然后給他們作了一番詳細(xì)的解釋：

Oracle數(shù)據(jù)庫(kù)的位圖索引(Bitmap Index)確實(shí)是針對(duì)那些數(shù)值稀疏(low-cardinality，低基數(shù))的字段，但是還應(yīng)記住的一點(diǎn)是，它是針對(duì)那些值不經(jīng)常改變的字段的。在實(shí)際應(yīng)用中，如果某個(gè)字段的值需要頻繁更新，那么就不適合在它上面創(chuàng)建位圖索引。在位圖索引中，如果你更新或插入其中一條數(shù)值為N的記錄，那么相應(yīng)表中數(shù)值為N的記錄（可能成百上千條）全部被Oracle鎖定，這就意味著其它用戶不能同時(shí)更新這些數(shù)值為N的記錄，其它用戶必須要等第一個(gè)用戶提交后，才能獲得鎖，更新或插入數(shù)據(jù)。

問(wèn)題找到了，修正就很簡(jiǎn)單了，Tom建議這些開發(fā)者去掉了這個(gè)位圖索引，然后在PROCESS_FLAG字段上建立一個(gè)函數(shù)索引，只為那些數(shù)值為N的記錄建立簡(jiǎn)單的B樹索引就可以了。

這些開發(fā)者回去后，按照Tom的指點(diǎn)，經(jīng)過(guò)一番測(cè)試，終于解決了問(wèn)題。

但故事并沒有到此結(jié)束，這些開發(fā)者并不滿足，他們給Tom寫email抱怨道，Oracle數(shù)據(jù)庫(kù)真“爛”，連這個(gè)簡(jiǎn)單的位圖索引問(wèn)題都不能處理，你看，Oracle浪費(fèi)了我們大量的時(shí)間和精力調(diào)試我們的Java程序。Tom給他們回了一封email，頗有感觸地對(duì)他們說(shuō)：

I have encountered issues such as this many times when an application is being moved from database A to database B. When an application that worked flawlessly in database A does not work, or works in an apparently bizarre fashion, on database B, the first thought is that database B is “bad” database. The simple truth is that database B just works differently. Neither database is wrong or “bad; they are just different. Knowing and understanding how they both work will help you immensely in dealing with these issues.

(當(dāng)人們把一個(gè)應(yīng)用從一種數(shù)據(jù)庫(kù)遷移到另一種數(shù)據(jù)庫(kù)時(shí)，他們常常抱怨同樣的問(wèn)題。本來(lái)這個(gè)應(yīng)用程序在數(shù)據(jù)庫(kù)A上運(yùn)行得很好，當(dāng)遷移到數(shù)據(jù)庫(kù)B時(shí)，就出問(wèn)題了。于是他們就認(rèn)定，數(shù)據(jù)庫(kù)B真爛。但事實(shí)并非如此，這只是因?yàn)閿?shù)據(jù)庫(kù)B的工作方式和原理不同于數(shù)據(jù)庫(kù)A而已。世界上沒有哪個(gè)數(shù)據(jù)庫(kù)是“爛”數(shù)據(jù)庫(kù)，關(guān)鍵是我們必須深入了解該數(shù)據(jù)庫(kù)的架構(gòu)和特點(diǎn)，這樣才能避免這類問(wèn)題。如果您理解位圖索引的適用條件，您還會(huì)說(shuō)Oracle是一個(gè)很“爛“的數(shù)據(jù)庫(kù)嗎？)

最后，Tom乘機(jī)建議他們，如果你們?cè)敢猓�我可以給你們做一次簡(jiǎn)單的為期3天的培訓(xùn)。這些Java程序員聽從了Tom同志的建議，經(jīng)過(guò)了3天的培訓(xùn)后，他們對(duì)Oracle能做的事情表示吃驚，他們紛紛表示，“我真傻，原來(lái)Oracle不適合建立臨時(shí)表呀，你看我的程序老是在那里刪除和創(chuàng)建臨時(shí)表�！�，“要是我用了物化視圖(Materalized View)，我的數(shù)據(jù)備份代碼就異常簡(jiǎn)單了�！�，“我還不知道connect by有這么強(qiáng)大的功能呢！”。

參考文獻(xiàn)：

Thomas Kyte, 2005, Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

a.a

回復(fù) 1# gunguymadman


   最近正在考慮使用join index 來(lái)解決幾張上億級(jí)表的關(guān)聯(lián)問(wèn)題，從樓主分享案例看來(lái)得謹(jǐn)慎了。大表之間的關(guān)聯(lián)真是頭疼，盡管都explain中都使用到了index rang scan 了，但是由于數(shù)據(jù)量實(shí)在太大還是得花很長(zhǎng)的時(shí)間。

renxiao2003

一直不太了解位圖索引的優(yōu)勢(shì)。