[[420019]]

第一章 Oracle索引位圖轉(zhuǎn)換介紹

 1.1 索引位圖轉(zhuǎn)換

首先介紹一下索引位圖轉(zhuǎn)換概念：

索引位圖轉(zhuǎn)換是優(yōu)化器對目標(biāo)表上的一個或多個目標(biāo)索引執(zhí)行位圖布爾運算。Oracle數(shù)據(jù)庫里有一個映射函數(shù)(Mapping Function)，它可以實現(xiàn)B樹索引中ROWID和對應(yīng)位圖索引中的位圖之間互相轉(zhuǎn)換。目的是對相同ROWID做AND、OR等連接運算。

當(dāng)執(zhí)行計劃中出現(xiàn)“BITMAP CONVERSION FROM/TO ROWIDS”、“BITMAP AND”，說明Oracle對應(yīng)的索引將其中的ROWID轉(zhuǎn)換成了位圖，然后對轉(zhuǎn)換后的位圖執(zhí)行了BITMAP AND(位圖按位與)布爾運算。最后將布爾運算的結(jié)果再次用映射函數(shù)轉(zhuǎn)換成了ROWID并回表得到最終的結(jié)果。

1.2 性能分析

根據(jù)我們以往的經(jīng)驗，用映射函數(shù)將ROWID轉(zhuǎn)換成位圖，這期間可能訪問了多個索引，甚至一個索引會訪問N多次。然后在執(zhí)行位圖布爾運算。最后再將運算結(jié)果轉(zhuǎn)換為ROWID并回表，這個過程在實際生產(chǎn)環(huán)境中的執(zhí)行效率往往是有問題的，我們可以通過隱藏參數(shù)_b_tree_bitmap_plans禁掉該過程中從ROWID到位圖的轉(zhuǎn)換。

但實際上當(dāng)我們看到“BITMAP CONVERSION FROM/TO ROWIDS”的執(zhí)行計劃，一定代表著存在性能問題嗎?

下面我用一個案例來說明：

創(chuàng)建測試表結(jié)構(gòu)如下：

DROP TABLE T1 PURGE; 
CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS; 
CREATE INDEX IDX_T1_ID ON T1(OBJECT_ID); 
EXEC dbms_stats.gather_table_stats(ownname=>'SZT',tabname =>'T1'); 

第二章 實驗環(huán)境測試

實驗?zāi)_本如下：

select * from ( 
select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

目的是通過單個索引，將優(yōu)化器走索引位圖轉(zhuǎn)換與否的執(zhí)行效率比較。

2.1 比較執(zhí)行效率

首先測試默認情況下的執(zhí)行計劃：

select * from ( 
select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

可以看到，優(yōu)化器沒有對索引做位圖轉(zhuǎn)換，而是使用了OR擴展的方式。分別訪問兩部分的查詢條件，并對其中的IN條件使用IN-LIST迭代的方式獲取數(shù)據(jù)。

分析這樣的優(yōu)勢：

IN條件中多個值會分別被訪問并與索引中的數(shù)據(jù)作比較，條件中的多個值也不會訪問索引多次，執(zhí)行效率較高。通過邏輯讀部分也能確定。

通過HINT，嘗試讓優(yōu)化器走出索引位圖轉(zhuǎn)換方式：

select /*+   OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") 
BITMAP_TREE(@"SEL$2" "T1"@"SEL$2" OR(1 1 ("T1"."OBJECT_ID") 2 ("T1"."OBJECT_ID") 3 ("T1"."OBJECT_ID") 4 
("T1"."OBJECT_ID") 5 ("T1"."OBJECT_ID") 6 ("T1"."OBJECT_ID") 7 ("T1"."OBJECT_ID") 8 ("T1"."OBJECT_ID"))) */* from ( 
select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

可以看到，每一次位圖訪問都只得到一個對應(yīng)的IN條件值，且相同索引訪問多次每次都消耗固定的邏輯讀，據(jù)此分析當(dāng)前場景下位圖索引轉(zhuǎn)換執(zhí)行效率不佳。原因來自于索引的多次訪問。

我們查看相應(yīng)表上的索引信息：

可以看到索引建立的原則就是唯一值與表數(shù)據(jù)1:1的情況。同時，由于采用了OBJECT_ID，其自增長特性，索引的聚簇因子比較小，屬于相對高效的索引。

得出結(jié)論：在聚簇因子較小時，通過OR擴展、IN-LIST迭代的方式其執(zhí)行效率高于索引位圖轉(zhuǎn)換。且優(yōu)化器也能準(zhǔn)確評估COST成本。

但實際生產(chǎn)環(huán)境中，大部分索引的聚簇因子沒有這么高效。下面我們降低聚簇因子值及進行測試。

2.2 降低索引的聚簇因子：

讓我們重新創(chuàng)建新表。實驗?zāi)_本如下：

CREATE TABLE T2 AS SELECT * FROM DBA_OBJECTS WHERE 1=2; 
insert into t2 select * from dba_objects order by dbms_random.value; --隨機插入 
CREATE INDEX IDX_T2_ID ON T1(OBJECT_ID); 
EXEC dbms_stats.gather_table_stats(ownname=>'SZT',tabname =>'T2'); 

通過打亂表數(shù)據(jù)的順序，降低聚簇因子值。

可以看到聚簇因子幾乎接近于表中數(shù)據(jù)行數(shù)，且索引葉子塊也有所增加。

2.2.1 比較執(zhí)行效率

select * from ( 
select * from t2  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

可以看到，默認情況下執(zhí)行計劃變?yōu)榱怂饕粓D轉(zhuǎn)換的形式。

分析其優(yōu)勢：只進行了一次回表。

通過HINT讓優(yōu)化器走回原有執(zhí)行計劃：

select * from ( 
select /*+ USE_CONCAT(@"SEL$2" 8 OR_PREDICATES(1))  */ * from t2  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

可以看到，由于回表了兩次，且聚簇因子較大，其消耗的邏輯讀已經(jīng)逐漸接近于索引位圖轉(zhuǎn)換的方式了。

且分析其回表邏輯讀：

• 位圖形式：134行回表，消耗147-16=131。
• OR擴展：128行回表，消耗130-2=128。

回表的邏輯讀十分接近。

總結(jié)：

索引位圖轉(zhuǎn)換的優(yōu)勢是減少回表次數(shù)。

OR擴展的優(yōu)勢是其IN-LIST迭代部分消耗邏輯讀較低。

分析到此，我們已經(jīng)基本明確不同方式的優(yōu)劣了，但對實際的邏輯讀消耗對比還不夠確定。

下面讓我們增大查詢的條件范圍。

2.2.2 增大查詢條件范圍

select * from ( 
select * from t2  WHERE object_id>88450 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

測試OR擴展:

select /*+        USE_CONCAT(@"SEL$2" 8 OR_PREDICATES(1))      */* from ( 
select * from t2  WHERE object_id>88450 or object_id in (1,2,3,4,5,6,7) 
order by object_id) 
where rownum<100; 

可以看到，當(dāng)增大查詢范圍值后，兩種不同執(zhí)行計劃其實際的消耗越來越接近了，最后通過索引位圖轉(zhuǎn)換的方式其執(zhí)行效率甚至高于原有的OR擴展的形式。因此我們在判斷執(zhí)行效率時，還是要具體情況具體分析。

分析回表的邏輯讀開銷：

• 位圖形式：172行回表，消耗180-16=164
• OR擴展：166行回表，消耗168-2=166

據(jù)此我們又可以確定，傳統(tǒng)的回表方式其實際的資源開銷高于索引位圖轉(zhuǎn)換后的回表方式。這又是索引位圖轉(zhuǎn)換的一大好處。

得出結(jié)論：

聚簇因子越大的索引，其越能在索引位圖轉(zhuǎn)換的方式中受益。因為其只需要回表一次。

索引位圖轉(zhuǎn)換后的回表，其消耗的資源開銷會低于傳統(tǒng)的回表方式。這也是索引位圖轉(zhuǎn)換的優(yōu)勢之一。

第三章 總結(jié)

以上，我們通過3個測試?yán)?，驗證的不同場景下的執(zhí)行計劃表現(xiàn)。

關(guān)于開頭部分我們的疑問，可以很明確做答了。

1.索引位圖轉(zhuǎn)換和傳統(tǒng)的OR擴展、IN-LIST迭代等形式、其執(zhí)行效率要具體情況具體分析。主要受影響于相關(guān)索引上的聚簇因子值。

2.索引位圖轉(zhuǎn)換的優(yōu)勢是一次性統(tǒng)一回表，ROWID回表的開銷也會略低于傳統(tǒng)的形式。

3. IN-LIST迭代的優(yōu)勢是對于IN后面條件多個值的訪問，其實際資源開銷較低。

墨天輪原文鏈接：https://www.modb.pro/db/25952(復(fù)制鏈接至瀏覽器或點擊文末閱讀原文查看)

關(guān)于作者

張程，云和恩墨SQL優(yōu)化工程師，長期服務(wù)于金融、保險行業(yè)?，F(xiàn)負責(zé)：公司Oracle、SQLServer、MySQL數(shù)據(jù)庫優(yōu)化方面的技術(shù)工作;公司SQL審核軟件SQM的審核相關(guān)工作。熱衷于性能優(yōu)化的學(xué)習(xí)與分享。