oracle位元影像索引

標籤：style   blog   http   color   使用   io   strong   資料   

以下內容主要都是關於oracle 10g位元影像索引，重點是儲存，其它優缺點，查詢機制等也略微介紹，概因為儲存機制是根基。
內容主要分四個部分：
1）來源於http://blog.chinaunix.net/uid-20687159-id-1894992.html
2）來源於http://blog.sina.com.cn/s/blog_4c6fef63010085m8.html
3）來自oracle官方文檔的和其它一些。  
4）個人的實驗，看位元影像索引的情況。
------------------------------
要想升入dw等，瞭解位元影像索引是必不可少的。
瞭解位元影像索引的關鍵是知道索引的內部結構，知道結果，自然知道它的優缺點，所以瞭解結構是核心。

一，oracle官方文檔
關於位元影像索引的概要介紹-定義、查詢原理、優點、缺點。
這裡只是一個筆記，非官方原文，摘取了綱要內容：
1.定義，什麼是位元影像索引：就是用位元影像表示的索引，oracle對於選擇度底的列的每個索引值建立一個位元影像，位元影像中的每一位可能對應多個列，位元影像中位等於1表示特定的行含有此位元影像表示的索引值。
2.查詢，由於索引是位元影像，所以很多很多時候可以對這些索引中的位元影像進行位元運算-（and 和 or),這樣的速度明顯比b樹快（某些情況下）。由於位元影像索引可以儲存null，所以可以直接通過位元影像索引計數（肯定是準確的）。後面提到的有點和位元影像的計算方式是直接相關的。
3.位元影像的優點(主要針對dw)：

•    減少即席查詢的相應時間
• 和其它類型索引比較，真正節約了索引資料空間
• 即使在非常差的硬體上，也可能會有戲劇化的效能提升
• 高效的並行DML和LOAD操作。
• 產生索引的時候更高效，首先是不排序，其次是佔用的空間少（索引空間）。
• 可以通過位元影像索引直接計數。

4.位元影像索引的缺點（其它資料），也不好說是缺點

•  不適合選擇度底的列
• 如果有比較頻繁的insert,update等操作，可能導致效能很底下，因為更新索引用的是行鎖（可能鎖定多行），而不是排它鎖。
• 可能會溢出，索引資料區塊難於放下整個索引值，這導致低效。

二，http://blog.chinaunix.net/uid-20687159-id-1894992.html
-邏輯分析位元影像
主要至關瞭解下位元影像：

 

 

 

 

需要注意的是，這隻是個，實際上每個位元影像的位元並非剛好等於記錄數，而是會根據情況來分解，否則對於居多的資料而言，位元影像未免太大了。

三，http://blog.sina.com.cn/s/blog_4c6fef63010085m8.html
深入研究--資料區塊中如何儲存
這是真正的深入瞭解。
為了省事，暫時不修改原文的樣式

一.什麼是位元影像索引 我們目前大量使用的索引一般主要是B*Tree索引,在索引結構中儲存著索引值和索引值的RowID,並且是一一對應的. 而位元影像索引主要針對大量相同值的列而建立(例如：類別，操作員，部門ID,庫房ID等), 索引塊的一個索引行中儲存索引值和起止Rowid,以及這些索引值的位置編碼, 位置編碼中的每一位表示索引值對應的資料行的有無.一個位元影像索引塊可能指向的是幾十甚至成百上千行資料的位置. 這種方式儲存資料,相對於B*Tree索引,佔用的空間非常小,建立和使用非常快. 當根據索引值查詢時,可以根據起始Rowid和位元影像狀態,快速定位元據. 當根據索引值做and,or或 in(x,y,..)查詢時,直接用索引的位元影像進行或運算,快速得出結果行資料. 當select count(XX) 時,可以直接存取索引就快速得出統計資料. 建立文法很簡單,就是在普通索引建立的文法中index前加關鍵字bitmap即可,例如: create bitmap index H病人挂號記錄_ix_執行人 on H病人挂號記錄(執行人); 二.位元影像索引的特點 1.Bitmap索引的儲存空間 相對於B*Tree索引,位元影像索引由於只儲存索引值的起止Rowid和位元影像,佔用的空間非常少. bitmap的空間佔用主要根以下4個因素相關: a.表的總記錄數 b.索引列的索引值多少,列的不同值越少,所需的位元影像就越少. c.操作的類型,批量插入比單條插入所面的位元影像要少得多,8i,9i下是這樣的,10G則沒有這種區別,詳見後面的分析. d.索引列相同索引值的物理分布,8i,9i中,不同塊上的資料,相同的索引值,會建立不同的位元影像行(段)來表示 注:本文提到的8i,9i,10g,我實驗的環境是8.1.7,9.2.0.5,10.2 2.Bitmap索引建立的速度 位元影像索引建立時不需要排序,並且按位儲存,所需的空間也少. B*Tree索引則在建立時需要排序,定位等操作,速度要慢得多. 3.Bitmap索引允許索引值為空白 B*Tree索引由於不記錄空值,當基於is null的查詢時,會使用全表掃描, 而對位元影像索引列進行is null查詢時,則可以使用索引. 4.Bitmap索引對錶記錄的高效訪問 當使用count(XX),可以直接存取索引就快速得出統計資料. 當根據位元影像索引的列進行and,or或 in(x,y,..)查詢時,直接用索引的位元影像進行或運算,在訪問資料之前可事先過濾資料. 5.Bitmap索引對批量DML操作只需進行一次索引 由於通過位元影像反映資料情況,大量操作時對索引的更新速度比B*Tree索引一行一行的處理快得多. 6.Bitmap索引的鎖機制 對於B*Tree索引,insert操作不會鎖定其它會話的DML操作. 而位元影像索引,由於用位元影像反映資料,不同會話更新相同索引值的同一位元影像段,insert、update、delete相互操作都會發鎖定。 對於oracle 8i,9i,單行插入時,由於一個位元影像行(位元影像段)只記錄8行記錄,所以最多鎖住相同索引值的8行資料的DML操作. 而批量插入時，和10G一樣,同一索引值只有一個位元影像行（位元影像段），所以，相同索引值的所有資料的DML操作都會被鎖住。 下面，針對8i,9i觀察一下鎖機制： SQL> Declare Begin For i In 1..9 Loop  Insert Into H病人挂號記錄(Id,No,號別,執行人) Values(i,‘G000001‘,1,‘張1‘); End Loop; Commit; End; / SQL> delete H病人挂號記錄 where id=1; 不提交，另開一個會話， SQL> delete H病人挂號記錄 where id=9; 操作可以進行，沒有鎖定。 SQL> delete H病人挂號記錄 where id=8; 操作等待,由於和另外一個會話操作的記錄的位元影像索引在同一個位元影像段上（一個位元影像段最多8行）,所以被鎖住了。 三.位元影像索引的適用場合 1.位元影像索引是Oracle資料庫在7.3版本中加入的,8i,9i企業版和個人版支援,標準版不支援. 2.基於規則的最佳化器無法使用Bitmap索引 3.適應於有大量重複值的列查詢 4.對於8i,9i版本，不適用於單行插入,適用於批量插入的資料,  因為單行插入時，相同索引值，每插入8行就會產生一行索引塊中的位元影像段,即使相同的值.  而批量插入時，相同索引值只產生一個位元影像段. 5.由於並發DML伺服器用戶端檔案鎖的是整個位元影像段的大量資料行，所以位元影像索引主要是用於OLAP應用,也可以用於OLTP中主要為讀操作的表. 關於bitmap的兩個參數 SQL> show parameter bitmap; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 其中bitmap_merge_area_size是bitmap索引進行合併作業時使用的記憶體地區,create_bitmap_area_size是建立時使用的記憶體地區. 8i,9i中,需要根據bitmap的大小以及常見的使用方式來調整. 9i以上,只需設定pga_aggregate_target的值,Oracle即會自動進和記憶體的調整. 四.位元影像索引儲存原理 位元影像索引對資料表的列的每一個索引值分別儲存為一個位元影像,Oracle對於不同的版本,不同的操作方式,資料產生均有差別. 對於8i,9i, 下面分3種方式來討論資料的插入: a.一次插入一行,插入多行後,一次提交; b.每插入一行,提交一次; c.批量插入方式,一次提交; 對於第一種方式,觀察位元影像索引的變化情況. a.假設插入8行相同索引值的資料,如果以每行方式插入,然後一次提交,則會產生8個位元影像 SQL> Insert Into H病人挂號記錄(Id,No,號別,執行人) Values(1,‘G000001‘,1,‘張1‘); 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> / 1 row inserted SQL> commit; Commit complete SQL> alter system dump datafile 1 block 40028; System altered row#0[7847] flag: -----, lock: 0 col 0; len 3; (3): d5 c5 31 --索引值‘張1‘ col 1; len 6; (6): 00 40 9c 54 00 00 --rowid的起始位置 col 2; len 6; (6): 00 40 9c 54 00 07 --rowid的終止位置 col 3; len 2; (2): c8 ff --位元影像編碼 row#1[7802] flag: -----, lock: 0 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 2; (2): c8 03 row#2[7780] flag: -----, lock: 0 col 0; len 3; (3): d5 c5 32 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 1; (1): 02 row#3[7758] flag: -----, lock: 0 col 0; len 3; (3): d5 c5 33 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 1; (1): 03 row#4[7736] flag: -----, lock: 2 col 0; len 3; (3): d5 c5 34 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 1; (1): 04 row#5[7714] flag: -----, lock: 2 col 0; len 3; (3): d5 c5 35 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 1; (1): 05 ----- end of leaf block dump ----- 但是,下次再插入一行相同索引值的資料時,會自動合并這8行位元影像為一行位元影像,並產生一個新的索引位元影像行存放剛插入行的索引: SQL> Insert Into H病人挂號記錄(Id,No,號別,執行人) Values(1,‘G000001‘,1,‘張1‘); 1 row inserted SQL> commit; Commit complete SQL> alter system dump datafile 1 block 40028; System altered row#0[7847] flag: -----, lock: 2 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 40 9c 54 00 00 col 2; len 6; (6): 00 40 9c 54 00 07 col 3; len 2; (2): c8 ff row#1[7825] flag: -----, lock: 2 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 40 9c 54 00 08 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 1; (1): 00 ----- end of leaf block dump ----- b.資料每行提交方式,與上面的情況相似,但有一點不一樣,每提交一行,拷貝原來的位元影像,產生新的位元影像,並標記原來的位元影像為已刪除, 標記為已刪除的位元影像,只有索引塊需要分配新的位元影像時,才會清除標記為已刪除的位元影像,重用這些空間. 在8i,9i上實驗的結果,與ITPUB的<Oracle 資料庫效能最佳化>一書378頁一致. 如果1000條相同索引值的資料插入,將產生125個包括8條記錄的位元影像行. c.第三種方式,批量插入資料,insert into H病人挂號記錄(Id,No,號別,執行人) select ***方式,  同一索引值,只產生一次位元影像,只有一個位元影像. SQL> Insert Into H病人挂號記錄(Id,No,號別,執行人) Select 1,‘G000001‘,1,‘張1‘ From dual Union All Select 2,‘G000002‘,1,‘張1‘ From dual Union All Select 3,‘G000003‘,1,‘張1‘ From dual Union All Select 4,‘G000004‘,1,‘張1‘ From dual Union All Select 5,‘G000005‘,1,‘張1‘ From dual Union All Select 6,‘G000006‘,1,‘張1‘ From dual Union All Select 7,‘G000006‘,1,‘張1‘ From dual Union All Select 8,‘G000006‘,1,‘張1‘ From dual Union All Select 9,‘G000006‘,1,‘張1‘ From dual; SQL> commit; Commit complete SQL> alter system dump datafile 1 block 40028; System altered row#0[8006] flag: -----, lock: 2 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 40 9c 54 00 00 col 2; len 6; (6): 00 40 9c 54 00 0f col 3; len 3; (3): c9 ff 01 row#1[8030] flag: ---D-, lock: 2 col 0; NULL col 1; NULL col 2; NULL col 3; NULL ----- end of leaf block dump ----- 所以,位元影像索引最好採用批量插入方式,這樣,每個索引值只產生一個位元影像.而單行資料插入方式,每個索引值將每8行資料產生一個位元影像. 10G的情況,則簡單得多. 上面3種方式,相同索引值的插入,位元影像的產生是一樣的,只有一個位元影像,並且,每次提交時,並不會刪除以前的位元影像,而是直接修改對應索引值的位元影像. 每次插入一行資料,插入9行後提交 row#0[7763] flag: ------, lock: 2, len=29 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 00 00 00 00 00 col 2; len 6; (6): 00 40 ef f2 00 0f col 3; len 8; (8): f9 e4 d5 dc bc 01 ff 01 ----- end of leaf block dump ----- 再批量插入9行資料並提交 row#0[7733] flag: ------, lock: 2, len=30 col 0; len 3; (3): d5 c5 31 col 1; len 6; (6): 00 00 00 00 00 00 col 2; len 6; (6): 00 40 ef f2 00 17 col 3; len 9; (9): fa e4 d5 dc bc 01 ff ff 03 ----- end of leaf block dump ----- 可以看出,10G對位元影像索引的儲存進行了最佳化,一個索引值在索引塊中只有一個位元影像 關於位元影像索引的一些資訊,可以參考:bitmap 的一點探究 http://www.itpub.net/114023.html 注意,其中有些結論並不是完全正確的,可以自己實驗證明,另外,該文涉及的實驗沒有標明Oracle版本,不同的版本,結果有差異