?Oracle優化器：星型轉換（Star Query Transformation ）?

---

---

Star query是一個事實表（fact table）和一些維度表（dimension）的join。每個維度表都跟事實表通過主外鍵join，且每個維度表之間不join。

星型模式設計的好處：

1）?提供了直接、直觀的業務實體與終端用戶schema設計的映射。 2）?對典型的star query提供了高性能的優化。 3）?通過大量的商業智能工具的廣泛支持，可能會期望甚至要求數據倉庫架構包含維度表。

星型模式是用于簡單的數據集市和大型數據倉庫。

?

?

雪花狀模式（snowflake schema）：

Snowflake schema是star schema的一種，但更復雜。它的維度數據被分到多個表而不是一個大表。更多的維度表，更多的外鍵joins，使查詢更復雜，查詢性能下降。

Oracle建議使用star schema替代snowflake schema，除非你有別的原因。

?

?

優化Star Query：

1）?在fact table的每個外鍵列上建立bitmap index。 2）?初始化參數STAR_TRANSFORMATION_ENABLED?應被設為TRUE，默認false。

星型轉換（Star transformation）能為star query提供高效的查詢性能。

?

?

?

合適的情況下，Oracle會自動選擇star transformation技術，隱視重寫star query SQL，提高star query效率。 ?

星型查詢2個基本步驟： 1）?從fact table中檢索出必要的結果集。（bitmap index會提高效率） 2）?結果集與維度表joins。

?

?

星型轉換(bitmap index)

通過bitmap AND操作將3個維度表bitmaps為一個單獨的bitmap，然后與fact table通過bitmap indexes邏輯JOIN。

SELECT?ch.channel_class, ?????? c.cust_city, ?????? t.calendar_quarter_desc, ???????SUM(s.amount_sold) sales_amount ??FROM?sales s, times t, customers c, channels ch ?WHERE?s.time_id = t.time_id ???AND?s.cust_id = c.cust_id ???AND?s.channel_id = ch.channel_id ???AND?c.cust_state_province =?'CA' ???AND?ch.channel_desc?in?('Internet',?'Catalog') ???AND?t.calendar_quarter_desc?IN?('1999-01',?'1999-02') ?GROUP?BY?ch.channel_class, c.cust_city, t.calendar_quarter_desc;

?

?

星型轉換（a Bitmap Join Index）

--建一個bitmap join index CREATE?BITMAP?INDEX?sales_c_state_bjix?ON?sales(customers.cust_state_province) FROM?sales, customers WHERE?sales.cust_id = customers.cust_id LOCAL?NOLOGGING?COMPUTE?STATISTICS;

即使用bitmap join index代替與表customer的bitmap join。

?

?

星型轉換的限制

1、?下面的情況不支持星型轉換 ???查詢中使用hint ???查詢包含綁定變量 ???Fact table上的bitmap indexes太少 ???遠程fact tables ???Anti-joined tables ???Fact table是一個unmerged view ???Fact table是一個partitioned view ? 2、?優化器不選擇star transformation的情況 ???表有一個好的單表訪問路徑 ???表太小不值得轉換 ? 3、臨時表在下面情況下不適用star transformation ???數據庫read-only模式 ???Star query是串行事務的一部分

?

---

---

優化星型查詢?

當你使用星型查詢時，你需要考慮以下兩點：

1. 調整星型查詢
2. 使用星型轉換

調整星型查詢
為了獲得星型查詢的最佳性能，遵循一些基本準則是非常重要的：

• 應該為事實表的每一個外鍵列都創建位圖索引。
• 初始化參數STAR_TRANSFORMATION_ENABLED應設置為TRUE。這將開啟對星型查詢的?重要優化功能。為了向下兼容，它在默認情況下設置為FALSE。

當一個數據倉庫滿足這些條件，在數據倉庫中運行的大多數星型查詢將會使用被稱為星形轉換的查詢執行策略。星型轉換為星型查詢提供了非常高效的查詢性能。

使用星型轉換

星型轉換是依靠隱式重寫（或轉換）原始星型查詢SQL的強大優化技術。最終用戶不需要知道任何關于星形轉換的細節。Oracle數據庫的查詢優化器會在合適的地方自動選擇星型轉換。

星型轉換是一個查詢轉換，旨在高效執行星型查詢。Oracle數據庫使用兩個基本階段來處理星型查詢。第一階段是從事實表（結果集）精確地檢索出必要的行。由于這種檢索使用了位圖索引，因此是非常高效的。第二階段是將一階段查到的結果集與維度表相結合。最終用戶查詢的一個例子是：“在西部和西南部地區的銷售門店的最后三個季度，食品部門的銷售額和利潤是多少？”這是一個簡單的星型查詢。

使用位圖索引的星型轉換
星型轉換的一個前提條件，即在事實表的每一個連接列上都有一個單列位圖索引。這些連接列包括所有的外鍵列。
例如，sh示例模式下的sales表，分別在TIME_ID，CHANNEL_ID，CUST_ID，PROD_ID和promo_id列上建有位圖索引。
考慮下面的星型查詢：

該查詢分兩個階段進行處理。在第一階段，Oracle數據庫使用事實表外鍵列的位圖索引從事實表中找出并檢索出必要的行。也就是說，Oracle數據庫從事實表中檢索結果集，從本質上是使用下面的查詢：

這是該算法的轉換步驟，因為原始星型查詢已被改造成子查詢表示方式。訪問事實表的這種方法利用了位圖索引的優勢。直觀地說，在關系數據庫中位圖索引提供了基于集合的處理方案。Oracle實現了非常快速的方法去處理集合操作，如AND（交集），OR（并集），MINUS和COUNT。

在這個星形查詢中，TIME_ID位圖索引用于標識事實表中銷售時間在1999年-Q1所有行的集合。這個集合被表示為位圖（一個由1和0組成的字符串，用來表示事實表中的哪些行屬于該集合）。

一個類似的位圖檢索對應sales事實表中1999年第二季度的的所有行。該位圖的或操作用于合并Q1銷售結果集與Q2銷售結果集。

另外還將在客戶維度，產品維度來完成集合操作。在星型查詢處理的這一點上，有三個位圖。每個位圖對應于一個單獨的維度表，并且每個位圖代表了事實表中滿足單獨維度約束的行的集合。

這三個位圖通過AND操作被合并成一個單獨的位圖。這個最終的位圖表示了事實表中滿足所有維度約束的行集合。這就是結果集，從評估查詢所需的事實表行的確切集合。請注意，沒有任何事實表中的實際數據被訪問。所有這些操作完全依賴位圖索引和維度表。因為位圖索引的壓縮數據表示，位圖集合操作是非常高效的。

一旦確認了結果集，可以通過位圖來訪問sales表的實際數據。從事實表中僅僅檢索需要的數據。在這一點上，Oracle數據庫，有效地將所有維度表和事實表結合了起來。這種技術提供了優異的性能，因為Oracle數據庫使用了一個邏輯的連接操作將所有維度表和事實表連接恰里，而不是將每個維度表與事實表分別進行連接。。

該查詢的第二階段是將事實表中的行（結果集）與維度表連接在一起。Oracle使用最有效的方法來訪問和連接維度表。許多維度表非常小，并且全表掃描通常是針對這些維度表的最有效的訪問方法。對于大尺寸的表，全表掃描可能不是最有效的訪問方法。在前面的例子中，product.department列的位圖索引可以用來快速識別在食品部門的所有產品。基于優化程序對每個維度表的大小和數據分布的判斷，Oracle數據庫的優化器會針對給定的維度表來自動確定哪種訪問方法是最適合的。

對于每個維度表而言，具體連接方法（以及索引方法）同樣將被優化器智能地確定。哈希連接往往是連接維度表最有效的算法。一旦連接了所有的維度表，最終的結果將返回到用戶。從一個表中檢索出匹配行，然后連接到另一個表的查詢技術通常被稱為半連接。

使用位圖索引星型轉換的執行計劃
下面這個典型的執行計劃是由帶位圖索引的星型轉換生成的：

在這個計劃中，是通過一個由三個位圖合并而來的位圖訪問路徑來訪問事實表。這三個位圖是BITMAP MERGE根據行資源樹的位圖生成的。每個這樣的行資源樹是從子查詢行資源樹的位圖鍵迭代行源組成，在這個例子是一個全表掃描。對于每一個這樣的值，位圖鍵迭代行源從位圖索引中檢索位圖。在相應的事實表行通過這種訪問路徑被檢索到以后，它們與維度表及臨時表合并產生的查詢結果。

?

使用位圖連接索引的星型轉換

除了位圖索引，您可以在星型轉換中使用位圖連接索引。假設你有以下附加索引結構：

?

使用位圖連接索引的星型查詢和之前的例子非常相似，唯一的區別是在星型查詢的第一階段，Oracle利用連接索引，而不是一個單表位圖索引，去訪問顧客數據。

?

使用位連接圖索引星型轉換的執行計劃
下面這個典型的執行計劃是由帶位連接圖索引的星型轉換生成的：

這個執行計劃和前面相比，區別在于使用位圖索引掃描顧客維度的那一部分沒有子查詢。這是因為在customer.cust_state_province的連接述語信息已經滿足了位圖連接索引sales_c_state_bjix。

Oracle如何選擇使用星型轉換

優化器可以生成并保存一個未經轉換的最優執行計劃。如果星型轉換被啟用，優化器將嘗試將其應用到查詢；如果適用，則產生一個使用轉換查詢的最優執行計劃。基于這兩個版本的執行計劃，優化器通過比較二者的成本估算，然后決定使用經過轉換的最優執行計劃或者是未經轉換的版本。

?

如果查詢需要訪問事實表中的大部分行，最好使用全表掃描，而不是使用星型轉換查詢。但是，如果維度表的約束謂詞具有充分的可選性，也就是說只會從事實表中檢索很小一部分數據，那么基于轉換的執行計劃很有可能會更好。

?

需要注意的是，優化器會根據許多標準判斷，在它任務合理的情況下才會根據維度表生成子查詢。Oracle優化器并不保證為所有維度表生成子查詢。基于表和查詢的特性，優化器還可以決定該轉換是否值得被應用到特定查詢中。在這種情況下，優化器將會使用最優計劃。

?

使用星型轉換的限制條件

具有任何以下特征的表均不支持星型轉換：

?查詢使用了與位圖訪問路徑不兼容的表提示（hint）

?查詢包含綁定變量

?表沒有位圖索引。事實表的列必須有位圖索引，優化器才能創建子查詢。

?遠程事實表。然而，子查詢中允許使用遠程維度表。

?反連接的表

?已經在子查詢中用作維度表的表

?表是unmerged視圖，并且不是分區視圖

?事實表是unmerged視圖

?事實表是分區視圖

在以下場景優化器可能不會選擇星型轉換：

?表具有良好的單表訪問路徑

?表太小，不值得進行轉換

?

此外，在下列條件下星型轉換不使用臨時表：

?數據庫處于只讀模式

?星型查詢是串行化事務的一部分

---

---

Oracle優化器:星型轉換

---

---

---

---

& ???????????