優化星型查詢

Hoegh發表於2015-05-08
優化

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

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

調整星型查詢
為了獲得星型查詢的最佳效能，遵循一些基本準則是非常重要的：
  • 應該為事實表的每一個外來鍵列都建立點陣圖索引。
  • 初始化引數STAR_TRANSFORMATION_ENABLED應設定為TRUE。這將開啟對星型查詢的 重要優化功能。為了向下相容，它在預設情況下設定為FALSE。
當一個資料倉儲滿足這些條件，在資料倉儲中執行的大多數星型查詢將會使用被稱為星形轉換的查詢執行策略。星型轉換為星型查詢提供了非常高效的查詢效能。

使用星型轉換

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

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

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

點選(此處)摺疊或開啟

  1. SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
  2.    SUM(s.amount_sold) sales_amount
  3. FROM sales s, times t, customers c, channels ch
  4. WHERE s.time_id = t.time_id
  5. AND s.cust_id = c.cust_id
  6. AND s.channel_id = ch.channel_id
  7. AND c.cust_state_province = \'CA\'
  8. AND ch.channel_desc in (\'Internet\',\'Catalog\')
  9. AND t.calendar_quarter_desc IN (\'1999-Q1\',\'1999-Q2\')
  10. GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
該查詢分兩個階段進行處理。在第一階段，Oracle資料庫使用事實表外來鍵列的點陣圖索引從事實表中找出並檢索出必要的行。也就是說，Oracle資料庫從事實表中檢索結果集，從本質上是使用下面的查詢：

點選(此處)摺疊或開啟

  1. SELECT ... FROM sales
  2. WHERE time_id IN
  3.   (SELECT time_id FROM times
  4.    WHERE calendar_quarter_desc IN(\'1999-Q1\',\'1999-Q2\'))
  5.    AND cust_id IN
  6.   (SELECT cust_id FROM customers WHERE cust_state_province=\'CA\')
  7.    AND channel_id IN
  8.   (SELECT channel_id FROM channels WHERE channel_desc IN(\'Internet\',\'Catalog\'));
這是該演算法的轉換步驟，因為原始星型查詢已被改造成子查詢表示方式。訪問事實表的這種方法利用了點陣圖索引的優勢。直觀地說，在關聯式資料庫中點陣圖索引提供了基於集合的處理方案。 Oracle實現了非常快速的方法去處理集合操作，如AND（交集），OR（並集），MINUS和COUNT。

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

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

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

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

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

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

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

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

點選(此處)摺疊或開啟

  1. SELECT STATEMENT
  2.  SORT GROUP BY
  3.   HASH JOIN
  4.    TABLE ACCESS FULL CHANNELS
  5.    HASH JOIN
  6.     TABLE ACCESS FULL CUSTOMERS
  7.     HASH JOIN
  8.      TABLE ACCESS FULL TIMES
  9.      PARTITION RANGE ITERATOR
  10.       TABLE ACCESS BY LOCAL INDEX ROWID SALES
  11.        BITMAP CONVERSION TO ROWIDS
  12.         BITMAP AND
  13.          BITMAP MERGE
  14.           BITMAP KEY ITERATION
  15.            BUFFER SORT
  16.             TABLE ACCESS FULL CUSTOMERS
  17.            BITMAP INDEX RANGE SCAN SALES_CUST_BIX
  18.          BITMAP MERGE
  19.           BITMAP KEY ITERATION
  20.            BUFFER SORT
  21.             TABLE ACCESS FULL CHANNELS
  22.            BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
  23.          BITMAP MERGE
  24.           BITMAP KEY ITERATION
  25.            BUFFER SORT
  26.             TABLE ACCESS FULL TIMES
  27.            BITMAP INDEX RANGE SCAN SALES_TIME_BIX
在這個計劃中，是通過一個由三個點陣圖合併而來的點陣圖訪問路徑來訪問事實表。這三個點陣圖是BITMAP MERGE根據行資源樹的點陣圖生成的。每個這樣的行資源樹是從子查詢行資源樹的點陣圖鍵迭代行源組成，在這個例子是一個全表掃描。對於每一個這樣的值，點陣圖鍵迭代行源從點陣圖索引中檢索點陣圖。在相應的事實錶行通過這種訪問路徑被檢索到以後，它們與維度表及臨時表合併產生的查詢結果。

 

使用點陣圖連線索引的星型轉換

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

 

點選(此處)摺疊或開啟

  1. CREATE BITMAP INDEX sales_c_state_bjix
  2. ON sales(customers.cust_state_province)
  3. FROM sales, customers
  4. WHERE sales.cust_id = customers.cust_id
  5. LOCAL NOLOGGING COMPUTE STATISTICS;

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

 

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

點選(此處)摺疊或開啟

  1. SELECT STATEMENT
  2.  SORT GROUP BY
  3.   HASH JOIN
  4.    TABLE ACCESS FULL CHANNELS
  5.    HASH JOIN
  6.     TABLE ACCESS FULL CUSTOMERS
  7.     HASH JOIN
  8.      TABLE ACCESS FULL TIMES
  9.      PARTITION RANGE ALL
  10.       TABLE ACCESS BY LOCAL INDEX ROWID SALES
  11.        BITMAP CONVERSION TO ROWIDS
  12.         BITMAP AND
  13.          BITMAP INDEX SINGLE VALUE SALES_C_STATE_BJIX
  14.          BITMAP MERGE
  15.           BITMAP KEY ITERATION
  16.            BUFFER SORT
  17.             TABLE ACCESS FULL CHANNELS
  18.            BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
  19.          BITMAP MERGE
  20.           BITMAP KEY ITERATION
  21.            BUFFER SORT
  22.             TABLE ACCESS FULL TIMES
  23.            BITMAP INDEX RANGE SCAN SALES_TIME_BIX

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

 

Oracle如何選擇使用星型轉換

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

 

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

 

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

 

使用星型轉換的限制條件

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

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

?查詢包含繫結變數

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

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

?反連線的表

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

?表是unmerged檢視，並且不是分割槽檢視

?事實表是unmerged檢視

?事實表是分割槽檢視


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

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

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

 

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

?資料庫處於只讀模式

?星型查詢是序列化事務的一部分

hoegh
15.05.08
-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1629397/，如需轉載，請註明出處，否則將追究法律責任。

相關文章