[SQL Tune] Star Transformation

Star transformation 是在EDW(Enterprise Data Warehouse)中用來Join Table的常見方式. Star Transformation 和傳統的 Nested Loop/Hash Join/Merge Join 在運算邏輯上有很大的差異. 簡單的說, Star Transformation 利用 Bitmap Index 將 Dimension Table先做Join和Filter後在去存取 Fact Table, 藉以大量的減少IO.

但如同 Oracle 的很多功能, 在使用這些功能前, 相關的需求和系統設定, 也必須先研究清楚以避免執行時出現問題, 等程式上線後再踩到地雷可別怪ORACLE沒有先跟你講喔!

0. 準備工作
-- Fact Table
-- 此 Table 筆數大約是220,000筆
CREATE TABLE STAR_FACT
(
QTY Number(6),
Package Varchar2(40),
Customer VARCHAR2(20 BYTE),
Stage VARCHAR2(25 BYTE) NOT NULL
);

-- Dimension Table 1
-- 此 Table 筆數大約是30筆
CREATE TABLE STAR_DIM_1
(
Stage VARCHAR2(20 BYTE),
section VARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 2
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_2
(
Cus_code VARCHAR2(20 BYTE),
Cus_locationVARCHAR2(25 BYTE) NOT NULL
);
-- Dimension Table 3
-- 此 Table 筆數大約是300筆
CREATE TABLE STAR_DIM_3
(
Pkg_PkgcodeVARCHAR2(20 BYTE),
Pkg_Lead_CountVARCHAR2(25 BYTE) NOT NULL
);
-- Gather statistics
exec dbms_stats.gather_table_stats('RPT','Star_Fact');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_1');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_2');
exec dbms_stats.gather_table_stats('RPT','Star_DIM_3');

-- Add constraint/Primary key at Dimension Table
alter table RPT.Star_Dim_1 add constraint Star_dim_1_pk primary key (Stage);
alter table RPT.Star_Dim_2 add constraint Star_Dim_2_pk primary key (Cus_Code);
alter table RPT.Star_Dim_3 add constraint Star_Dim_3_pk primary key (Pkg_Pkgcode);

-- Create Bitmap index on dimension key at Fact Table
create bitmap index Star_Fact_Dim_1 on RPT.Star_Fact(Stage);
create bitmap index Star_Fact_Dim_2 on RPT.Star_Fact(Customer);
create bitmap index Star_Fact_Dim_3 on RPT.Star_Fact(Package);

-- Analyze Table & Index
analyze table Star_Fact compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_1 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_2 compute statistics for table for all indexes for all indexed columns;
analyze table Star_DIM_3 compute statistics for table for all indexes for all indexed columns;

1. 使用 非Star Transformations

* 我們就先用非 Star Transformation看看Oracle 會用什麼方是去處理?
先將用以下指令確認Oracle 不會使用 Star Transformation
alter session set star_transformation_enabled='false';

* 接下來執行以下SQL, 此SQL從客戶, 產品和站點中指定一些屬性在去Fact Table中計算數量.


select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


* 執行結果如下


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 131 Bytes: 636 Cardinality: 12
8 SORT GROUP BY Cost: 131 Bytes: 636 Cardinality: 12
7 HASH JOIN Cost: 120 Bytes: 77,592 Cardinality: 1,464
1 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455
6 HASH JOIN Cost: 117 Bytes: 58,560 Cardinality: 1,464
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
5 HASH JOIN Cost: 114 Bytes: 1,558,890 Cardinality: 51,963
3 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
4 TABLE ACCESS FULL RPT.STAR_FACT Cost: 108 Bytes: 4,836,590 Cardinality: 219,845

-- AutoTrace
Description Value
sorts (memory) 3
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 509
db block gets 0
consistent gets 1104
bytes sent via SQL*Net to client 1122
bytes received via SQL*Net from client 1036
SQL*Net roundtrips to/from client 4

從結果來看, 最終的筆數是6筆, Consistent Get 是 1104, 大概花了兩秒鍾. 而看看Execution Plan, 它用了三個 Hash Join, 先從Star_Dim_1去連接 Star_Fact, 後再接續連接 Star_Dim_2, Star_Dim3. 沒有意外, 這是正常 Oracle 處理此類SQL的做法.

2. Star Transformation 的原理和步驟

如前文所說, Star Transformation 可以有效的將 Dimension Table先做Join和Filter後再去存取 Fact Table, 其步驟如下:

* 為每一個 Dimension Table建立 Fact Table的 RowID的列表

- 先根據查詢的限制條件在每個 Dimension Table得到RowID列表.

- 利用前一步驟得到列表透過 Bitmap Index(Fact Table)去存取的Fact Table, 再將得到的
ROWID存成列表.

- 再來做的就是合併的動作. 假設SQL包含三個 Dimension key, 就重複前兩個步驟三次後,
將三個結果的 RowID作合併的動作.

* 執行過濾的動作, 所謂過濾就是說從前述合併的結果中判讀所有條件都成立的RowID,所謂所有條件就是說符合三的條件的資料.
* 將前述步驟的 RowID得到實際的資料.
* 在連接回 Dimension Table去得到SQL要的一些屬性值 .
* 對結果做加總.


3. Star Transformation 的實例演練

* 先將用以下指令確認Oracle 使用 Star Transformation

alter session set star_transformation_enabled='false';

* 執行同樣的SQL

select D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE,sum(F.qty)
from star_fact F, star_DIm_1 D1, star_DIM_2 D2, star_DIM_3 D3
where F.customer=D2.cus_code
and F.stage=D1.stage
and F.package=D3.PKG_PKGCODE
and D1.section='F'
and D3.PKG_LEAD_COUNT=96
group by D2.CUS_LOCATION,D1.section ,D3.PKG_PKGCODE ;


-- Execution Plan
Plan
SELECT STATEMENT CHOOSE Cost: 54 Bytes: 53 Cardinality: 1
18 SORT GROUP BY Cost: 54 Bytes: 53 Cardinality: 1
17 HASH JOIN Cost: 53 Bytes: 53 Cardinality: 1
15 HASH JOIN Cost: 50 Bytes: 40 Cardinality: 1
13 HASH JOIN Cost: 47 Bytes: 1,320 Cardinality: 44
1 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
12 TABLE ACCESS BY INDEX ROWID RPT.STAR_FACT Cost: 43 Bytes: 4,092 Cardinality: 186
11 BITMAP CONVERSION TO ROWIDS
10 BITMAP AND
5 BITMAP MERGE
4 BITMAP KEY ITERATION
2 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
3 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_03
9 BITMAP MERGE
8 BITMAP KEY ITERATION
6 TABLE ACCESS FULL RPT.STAR_DIM_1 Cost: 2 Bytes: 40 Cardinality: 5
7 BITMAP INDEX RANGE SCAN RPT.STAR_FACT_02
14 TABLE ACCESS FULL RPT.STAR_DIM_3 Cost: 2 Bytes: 60 Cardinality: 6
16 TABLE ACCESS FULL RPT.STAR_DIM_2 Cost: 2 Bytes: 5,915 Cardinality: 455

-- AutoTrace
Description Value
sorts (memory) 2
sorts (disk) 0
redo size 0
recursive calls 0
physical reads 0
db block gets 0
consistent gets 0
bytes sent via SQL*Net to client 695
bytes received via SQL*Net from client 734
SQL*Net roundtrips to/from client 3

從結果來看, 最終的筆數是6筆, Consistent Get 是 695,比前例少了一半 時間也是. 而看看Execution Plan, 如同前文所說, 它將Dimension Table 儘可能先用 Bitmap Index 串聯後, 在去存取 Fact Table. 從 Consistent Get 大幅下降就知道效果相當明顯.

4. Star Transformation 的需求

* Fact & Dimensional Table建立 Index. 通常來說, Fact Table上的Diemension key會建立Bitmap Index. 不像前述的 Nested Loop/Hash Join/Merge Join 一次只會使用單一Index(同一 Table), Star Transformation 會儘可能使用所有可用的 Index.
* Fact & Dimensional Table的 primary & foreign key關係, 這不是必要 , 但是建議要有. 針對資料量較大的 Table, 建立 primary & foreign key關係對資料 Load一定會有影響, 要自行斟酌影響程度做適當動作(如 Load 資料時, disable primary & foreign key關係)
* Database 設定
- Metalink 有一些 Star Transformation 的 Bug 說明. 這一點倒是蠻令人失望, 因為一堆 Oracle的新功能總是隱藏一堆BUG, 而這些BUG都要歷經一段時間被發現後, 在待 Oracle原廠出 Patch解決.
- 設定參數: Star_Transformation_Enabled, 設為 True後, optimizer才會考慮 Star Transformation.
- 設定參數: Hash_Join_Enabled, Hash Join會是 Star Transformation常用的 Join.
- 調整 PGA 參數讓 Star Transformation 儘量執行在 PGA 中.
* 還有一個參數 alter session set "_always_star_transformation"=true; 是 Oracle 的內部參數, 是要 Oracle Support 建議才能使用, 如果 Optimizer 不聽話堅持不run Star Transformation, 可以試試此參數.
* 總之, Star Transformation 是做 EDW一定要具備的技巧, 有這個工具, 許多大量資料存取的SQL變得簡單. 當然水可以載舟亦可覆舟, 在使用任何新功能別忘了周全的驗證一定是需要的.


參考文件

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/schemas.htm
So How Do Star Transformations Actually Works