Oracle編程高手箴言：位元影像索引的故事

您如果熟悉Oracle資料庫，我想您對ThomasKyte的大名一定不會陌生。Tomas主持的asktom.oracle.com網站享譽Oracle界數十年，絕非幸致。最近在圖書館借到這位Oracle絕頂高手編著的《ExpertOracleDatabaseArchitecture-9iand10gprogrammingTechniquesandSolutions》，翻閱之下，果然盛名無虛，雖然說不上字字珠璣，但作者對Oracle架構的理解和實踐確實已達到出神入化的境界。如果您有時間和興趣，強烈建議您閱讀這本書。這本書最大的特點是語言生動活潑，說理清楚，幾乎每講解一個原理，作者都給出了具體執行個體，讓人讀起來毫不氣悶。

另外，Thomas謙遜的態度讓我非常佩服，Thomas在Oracle資料庫方面工作了16年，並且參與了早期Oracle版本的開發，但他仍然謙虛地說，他每天都能從Oracle文檔裡學到新的東西。

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

位元影像索引(BitmapIndex)的故事

一日，一群Java開發人員找到Tom先生，說他們新開發的系統已經上線，但效能及其低下，他們問Tom先生能不能替他們看看問題到底出在什麼地方。他們告訴Tom，他們的系統採用JSP+EJB+Oracle的典型三層架構，其中EJB中的SQL是由第三方工具產生的。Tom同志一聽到EJB，就知道這個系統是不能採用SQL代碼跟蹤的方法來進行效能調優了。於是，Tom同志告訴這些心急火燎的Java開發人員，你們系統的問題肯定在瀏覽器到資料庫之間，但具體問題出在什麼地方，我需要看看你們的資料庫。

於是，Tom同志遠端連線到他們的測試資料庫注意不是生產資料庫），查看了幾個動態效能檢視(V$LOCK和V$SQL)，最後終於發現了問題的所在。Tom同志發現他們的資料庫中有一個位元影像索引(BitmapIndex)最為可疑，這個索引是建立在一個PROCESS_FLAG的欄位上，PROCESS_FLAG欄位表示該記錄是否被處理了，可能值只有兩個，一個是未處理(N)，一個是已經處理(Y)。當記錄初次插入資料庫時，該欄位的值為N，但其它進程讀取並處理那些未處理的記錄(值為N的記錄)後，這個欄位的值就更新為Y。

Tom就問這些Java開發人員，你們為什麼要在這個PROCESS_FLAG欄位上建立位元影像索引呢？

其中有一個開發人員振振有詞的說，這是為了提高尋找速度，一旦建立了位元影像索引，我們的程式就能快速找到那些數值為N的記錄，然後處理。隨後，他又拿出一本大部頭的Oracle資料庫參考手冊，對Tom同志說，這書上都是這麼說的，對那些數值非常少的欄位，比如，我們的PROCESS_FLAG欄位只有兩個值，就應該建立位元影像索引，這難道有什麼問題嗎？

Tom同志微微一笑，沒有直接回答。只見他開啟SQLPlus，串連到他的本地Oracle執行個體，給這群開發人員示範了下面及其簡單的SQL代碼。

C:\DocumentsandSettings\carlwu>sqlplusscott/tiger@carl SQL*Plus:Release11.1.0.6.0-ProductiononWedApr2318:15:342008 Copyright(c)1982,2007,Oracle.Allrightsreserved. Connectedto: OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-Production WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions SQL>createtablet(processed_flagvarchar2(1)); Tablecreated. SQL>createbitmapindext_idxont(processed_flag); Indexcreated. SQL>insertintotvalues('N'); 1rowcreated. SQL>

剛才那位振振有詞的開發人員不服氣的說，這有什麼，不是很正常嗎？接著Tom又開啟了一個SQLPlus視窗，並串連到本機資料庫，鍵入下面的SQL語句，奇怪的是這條SQL並不執行，而是一直在等待。下面是這條SQL的一個：

'800')this.width='800';if(this.height>'600')this.height='600';"border=0>

這些Java開發人員看到這裡，驚訝得目瞪口呆。其中一個開發人員猶豫地說，好象這個位元影像索引只允許一個使用者操作，如果其它使用者想同時操作這個索引，那他必須等第一個使用者的請求處理完成，並且提交之後，才能進行，如果第一個使用者不提交，那麼他必須一直等待。Tom點頭表示贊同，然後給他們作了一番詳細的解釋：

Oracle資料庫的位元影像索引(BitmapIndex)確實是針對那些數值稀疏(low-cardinality，低基數)的欄位，但是還應記住的一點是，它是針對那些值不經常改變的欄位的。在實際應用中，如果某個欄位的值需要頻繁更新，那麼就不適合在它上面建立位元影像索引。在位元影像索引中，如果你更新或插入其中一條數值為N的記錄，那麼相應表中數值為N的記錄可能成百上千條）全部被Oracle鎖定，這就意味著其它使用者不能同時更新這些數值為N的記錄，其它使用者必須要等第一個使用者提交後，才能獲得鎖，更新或插入資料。

問題找到了，修正就很簡單了，Tom建議這些開發人員去掉了這個位元影像索引，然後在PROCESS_FLAG欄位上建立一個函數索引，只為那些數值為N的記錄建立簡單的B樹索引就可以了。

這些開發人員回去後，按照Tom的指點，經過一番測試，終於解決了問題。

但故事並沒有到此結束，這些開發人員並不滿足，他們給Tom寫email抱怨道，Oracle資料庫真“爛”，連這個簡單的位元影像索引問題都不能處理，你看，Oracle浪費了我們大量的時間和精力調試我們的Java程式。Tom給他們回了一封email，頗有感觸地對他們說：

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.

(當人們把一個應用從一種資料庫遷移到另一種資料庫時，他們常常抱怨同樣的問題。本來這個應用程式在資料庫A上運行得很好，當遷移到資料庫B時，就出問題了。於是他們就認定，資料庫B真爛。但事實並非如此，這隻是因為資料庫B的工作方式和原理不同於資料庫A而已。世界上沒有哪個資料庫是“爛”資料庫，關鍵是我們必須深入瞭解該資料庫的架構和特點，這樣才能避免這類問題。如果您理解位元影像索引的適用條件，您還會說Oracle是一個很“爛“的資料庫嗎？)

最後，Tom乘機建議他們，如果你們願意，我可以給你們做一次簡單的為期3天的培訓。這些Java程式員聽從了Tom同志的建議，經過了3天的培訓後，他們對Oracle能做的事情表示吃驚，他們紛紛表示，“我真傻，原來Oracle不適合建立暫存資料表呀，你看我的程式老是在那裡刪除和建立暫存資料表。”，“要是我用了物化視圖(MateralizedView)，我的資料備份代碼就異常簡單了。”，“我還不知道connectby有這麼強大的功能呢！”。

1. 詳解Oracle的幾種分頁查詢語句
2. Oracle中視圖的建立和處理方法
3. Oracle DBA職責及日常工作分析