星型轉換實現方法
星型轉換是一個非常強大的最佳化技術,它是透過對原來的SQL語句的隱式的改寫來實現的。終端使用者並不需要知道有關星型轉換的任何細節。資料庫最佳化器會在合適的時候進行星型轉換。
要獲得星型轉換的最大效能,需要遵循以下基本的指導:
- 在事實表的每個外來鍵列上都應該建立bitmap索引。
- 需要將初始化引數STAR_TRANSFORMATION_ENABLED設定為true,這可以獲得star query的重要的最佳化特性。預設為false,是為了向下相容。
資料庫進行星型查詢時,會使用兩個基本的階段:
- 第一個階段從事實表(或者說結果集)裡獲取所有必要的記錄行。由於這是透過bitmap索引來檢索資料,因此比較高效。
- 第二個階段將該結果集與維度表進行關聯。這叫做semi-join(也就是exists和in寫法)。
注意:只有oracle企業版才有bitmap索引。標準版不支援bitmap索引和星型轉換。
SQL> connect sh/sh
SQL> set autotrace traceonly exp;
SQL> SELECT ch.channel_class, c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sales s,times t,customers c,channels ch
5 WHERE s.time_id = t.time_id AND
6 s.cust_id = c.cust_id AND
7 s.channel_id = ch.channel_id AND
8 c.cust_state_province = 'CA' AND
9 ch.channel_desc IN ('Internet','Catalog') AND
10 t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=786 Card=1 Bytes=73)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6602_F1E23'
3 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE) (Cost=332 Card=383 Bytes=9958)
4 1 HASH (GROUP BY) (Cost=455 Card=1 Bytes=73)
5 4 HASH JOIN (Cost=454 Card=1 Bytes=73)
6 5 HASH JOIN (Cost=450 Card=2 Bytes=104)
7 6 HASH JOIN (Cost=448 Card=32 Bytes=1184)
8 7 TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=183 Bytes=2928)
9 7 PARTITION RANGE (SUBQUERY) (Cost=432 Card=254 Bytes=5334)
10 9 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (TABLE) (Cost=432 Card=254 Bytes=5334)
11 10 BITMAP CONVERSION (TO ROWIDS)
12 11 BITMAP AND
13 12 BITMAP MERGE
14 13 BITMAP KEY ITERATION
15 14 BUFFER (SORT)
16 15 TABLE ACCESS (FULL) OF 'CHANNELS' (TABLE) (Cost=3 Card=2 Bytes=42)
17 14 BITMAP INDEX (RANGE SCAN) OF 'SALES_CHANNEL_BIX' (INDEX (BITMAP))
18 12 BITMAP MERGE
19 18 BITMAP KEY ITERATION
20 19 BUFFER (SORT)
21 20 TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=183 Bytes=2928)
22 19 BITMAP INDEX (RANGE SCAN) OF 'SALES_TIME_BIX' (INDEX (BITMAP))
23 12 BITMAP MERGE
24 23 BITMAP KEY ITERATION
25 24 BUFFER (SORT)
26 25 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_F1E23' (TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)
27 24 BITMAP INDEX (RANGE SCAN) OF 'SALES_CUST_BIX' (INDEX (BITMAP))
28 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6602_F1E23'(TABLE (TEMP)) (Cost=2 Card=383 Bytes=5745)
29 5 TABLE ACCESS (FULL) OF 'CHANNELS' (TABLE) (Cost=3 Card=2 Bytes=42)
這就是一個星型轉換的例子。對於這個例子來說,資料庫會進行兩個階段:
第一個階段,資料庫使用sales表的外來鍵列的bitmap索引來從sales表裡標識並檢索出所有必須的資料行,得到一個結果集。會使用如下的SQL來進行改寫:
SELECT s.amount_sold
FROM sales s
WHERE time_id IN (SELECT time_id
FROM times
WHERE calendar_quarter_desc IN('1999-Q1','1999-Q2'))
AND cust_id IN (SELECT cust_id
FROM customers
WHERE cust_state_province = 'CA')
AND channel_id IN(SELECT channel_id
FROM channels
WHERE channel_desc IN ('Internet','Catalog'));
這是演算法中的轉換步驟,因為原來的星型查詢已經轉換為這樣一個子查詢的表現形式。在這個星型查詢中,先掃描times表,獲得符合1999-Q1和1999-Q2的time_id。然後根據這些time_id,再使用sales表上的time_id上的bitmap索引來找到事實表裡對應到1999-Q1的資料行集。這個資料行集以bitmap裡的bit位來表現,也就是1和0的組合,這些1和0就對應到事實表裡符合該條件的、在該資料行集裡的rowid。執行計劃裡的BITMAP KEY ITERATION步驟就體現了這個過程。然後使用類似的方法獲得事實表裡對應到1999-Q2的資料行集。然後採用OR運算子,將Q1的結果集與Q2的結果集進行合併,也就是BITMAP MERGE步驟。
對於customer維度和product維度,也會採用類似的額外的方法獲得資料行集。
在該查詢彙總,我們得到三個以bitmap所展現的資料行集。
每個bitmap對應到一個單獨的維度表,每個bitmap表現了事實表裡符合單個維度的限制。這三個bitmap透過使用bitmap AND操作組合成一個bitmap。
最終的bitmap就表現了事實表裡符合所有條件的行集。這也就是最終的rowid結果集,然後透過該bitmap得到最終的結果。注意,在這個過程中,並沒有實際訪問過事實表,所有這些操作都是在維度表和bitmap索引上實現的。由於bitmap索引對資料進行了壓縮,因此基於結果集的操作特別高效。
該查詢的第二個階段,就是將事實表所返回的資料行與維度表關聯。在與維度表關聯時,也就是在掃描維度表的時候,CBO會自動判斷,如果維度表很小,則全表掃描,如果維度表很大,CBO可能會建立臨時表,用來包含這兩個階段所要用到的記錄。比如這裡再掃描customers維度表時,就把customers表裡的記錄取出,並放到了一個臨時表裡。在關聯維度時,通常會採用hash join的方式。
這基本就是星型轉換的過程。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27661381/viewspace-1457779/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 小議星型轉換
- 如何實現隱式型別轉換型別
- 【型別轉換】使用c#實現簡易的型別轉換(Emit,Expression,反射)型別C#MITExpress反射
- Oracle優化器:星型轉換(Star Query Transformation )Oracle優化ORM
- WORD及EXCEL行列轉換實現方法收藏(轉)Excel
- flask實現python方法轉換服務FlaskPython
- 試驗Oracle中實現行列轉換的方法(轉)Oracle
- 使用vue實現行列轉換的一種方法。Vue
- 工程座標轉換方法C#程式碼實現C#
- PHP 型別轉換&&型別強制轉換PHP型別
- 2. Spring早期型別轉換,基於PropertyEditor實現Spring型別
- java型別轉換與強制型別轉換(轉)Java型別
- JPG轉PDF如何實現?圖片批次轉換PDF的快捷方法
- 如何實現圖片轉換成文字?快速錄入方法
- Java 實現 Map 和 Object 互相轉換的幾種方法JavaObject
- Mybatis實踐(一)型別轉換器MyBatis型別
- 型別轉換型別
- 資料倉儲優化中什麼是星型轉換(Star Transformation)?優化ORM
- java 實現文字格式轉換Java
- 《Afterlight》中泰坦星環境的實現方法
- java- 型別-轉換:基本型別以及包裝型別的轉換Java型別
- js型別轉換JS型別
- 字元型別轉換字元型別
- 3.2 型別轉換型別
- JavaScript 型別轉換JavaScript型別
- Golang型別轉換Golang型別
- JS中的資料型別轉換:String轉換成Number的3種方法JS資料型別
- Gson轉換 — json資料轉換為Object實體公共方法JSONObject
- C++中的向上型別轉換和向下型別轉換C++型別
- pivot、unpivot實現oracle行列轉換Oracle
- sql 實現表的行列轉換SQL
- 資料型別,型別轉換資料型別
- c# API實現簡繁體轉換的方法(kernel32.dll)C#API
- interface{} 型別的轉換型別
- JNI常用型別轉換型別
- 資料型別轉換資料型別
- go interface{}型別轉換Go型別
- 型別轉換注意點型別