最佳化星型查詢
當你使用星型查詢時,你需要考慮以下兩點:
- 調整星型查詢
- 使用星型轉換
調整星型查詢
為了獲得星型查詢的最佳效能,遵循一些基本準則是非常重要的:
- 應該為事實表的每一個外來鍵列都建立點陣圖索引。
- 初始化引數STAR_TRANSFORMATION_ENABLED應設定為TRUE。這將開啟對星型查詢的 重要最佳化功能。為了向下相容,它在預設情況下設定為FALSE。
使用星型轉換
星型轉換是依靠隱式重寫(或轉換)原始星型查詢SQL的強大最佳化技術。終端使用者不需要知道任何關於星形轉換的細節。 Oracle資料庫的查詢最佳化器會在合適的地方自動選擇星型轉換。
星型轉換是一個查詢轉換,旨在高效執行星型查詢。 Oracle資料庫使用兩個基本階段來處理星型查詢。第一階段是從事實表(結果集)精確地檢索出必要的行。由於這種檢索使用了點陣圖索引,因此是非常高效的。第二階段是將一階段查到的結果集與維度表相結合。終端使用者查詢的一個例子是:“在西部和西南部地區的銷售門店的最後三個季度,食品部門的銷售額和利潤是多少?”這是一個簡單的星型查詢。
使用點陣圖索引的星型轉換
星型轉換的一個前提條件,即在事實表的每一個連線列上都有一個單列點陣圖索引。這些連線列包括所有的外來鍵列。
例如,sh示例模式下的sales表,分別在TIME_ID,CHANNEL_ID,CUST_ID,PROD_ID和promo_id列上建有點陣圖索引。
考慮下面的星型查詢:
點選(此處)摺疊或開啟
-
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-Q1\',\'1999-Q2\')
- GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
點選(此處)摺疊或開啟
-
SELECT ... FROM sales
-
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\'));
在這個星形查詢中,TIME_ID點陣圖索引用於標識事實表中銷售時間在1999年-Q1所有行的集合。這個集合被表示為點陣圖(一個由1和0組成的字串,用來表示事實表中的哪些行屬於該集合)。
一個類似的點陣圖檢索對應sales事實表中1999年第二季度的的所有行。該點陣圖的或操作用於合併Q1銷售結果集與Q2銷售結果集。
另外還將在客戶維度,產品維度來完成集合操作。在星型查詢處理的這一點上,有三個點陣圖。每個點陣圖對應於一個單獨的維度表,並且每個點陣圖代表了事實表中滿足單獨維度約束的行的集合。
這三個點陣圖透過AND操作被合併成一個單獨的點陣圖。這個最終的點陣圖表示了事實表中滿足所有維度約束的行集合。這就是結果集,從評估查詢所需的事實錶行的確切集合。請注意,沒有任何事實表中的實際資料被訪問。所有這些操作完全依賴點陣圖索引和維度表。因為點陣圖索引的壓縮資料表示,點陣圖集合操作是非常高效的。
一旦確認了結果集,可以透過點陣圖來訪問sales表的實際資料。從事實表中僅僅檢索需要的資料。在這一點上,Oracle資料庫,有效地將所有維度表和事實表結合了起來。這種技術提供了優異的效能,因為Oracle資料庫使用了一個邏輯的連線操作將所有維度表和事實表連線恰裡,而不是將每個維度表與事實表分別進行連線。。
該查詢的第二階段是將事實表中的行(結果集)與維度表連線在一起。 Oracle使用最有效的方法來訪問和連線維度表。許多維度表非常小,並且全表掃描通常是針對這些維度表的最有效的訪問方法。對於大尺寸的表,全表掃描可能不是最有效的訪問方法。在前面的例子中, product.department列的點陣圖索引可以用來快速識別在食品部門的所有產品。基於最佳化程式對每個維度表的大小和資料分佈的判斷,Oracle資料庫的最佳化器會針對給定的維度表來自動確定哪種訪問方法是最適合的。
對於每個維度表而言,具體連線方法(以及索引方法)同樣將被最佳化器智慧地確定。雜湊連線往往是連線維度表最有效的演算法。一旦連線了所有的維度表,最終的結果將返回到使用者。從一個表中檢索出匹配行,然後連線到另一個表的查詢技術通常被稱為半連線。
使用點陣圖索引星型轉換的執行計劃
下面這個典型的執行計劃是由帶點陣圖索引的星型轉換生成的:
點選(此處)摺疊或開啟
-
SELECT STATEMENT
-
SORT GROUP BY
-
HASH JOIN
-
TABLE ACCESS FULL CHANNELS
-
HASH JOIN
-
TABLE ACCESS FULL CUSTOMERS
-
HASH JOIN
-
TABLE ACCESS FULL TIMES
-
PARTITION RANGE ITERATOR
-
TABLE ACCESS BY LOCAL INDEX ROWID SALES
-
BITMAP CONVERSION TO ROWIDS
-
BITMAP AND
-
BITMAP MERGE
-
BITMAP KEY ITERATION
-
BUFFER SORT
-
TABLE ACCESS FULL CUSTOMERS
-
BITMAP INDEX RANGE SCAN SALES_CUST_BIX
-
BITMAP MERGE
-
BITMAP KEY ITERATION
-
BUFFER SORT
-
TABLE ACCESS FULL CHANNELS
-
BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
-
BITMAP MERGE
-
BITMAP KEY ITERATION
-
BUFFER SORT
-
TABLE ACCESS FULL TIMES
- BITMAP INDEX RANGE SCAN SALES_TIME_BIX
使用點陣圖連線索引的星型轉換
除了點陣圖索引,您可以在星型轉換中使用點陣圖連線索引。假設你有以下附加索引結構:
點選(此處)摺疊或開啟
使用點陣圖連線索引的星型查詢和之前的例子非常相似,唯一的區別是在星型查詢的第一階段,Oracle利用連線索引,而不是一個單表點陣圖索引,去訪問顧客資料。
使用位連線圖索引星型轉換的執行計劃
下面這個典型的執行計劃是由帶位連線圖索引的星型轉換生成的:
點選(此處)摺疊或開啟
-
SELECT STATEMENT
-
SORT GROUP BY
-
HASH JOIN
-
TABLE ACCESS FULL CHANNELS
-
HASH JOIN
-
TABLE ACCESS FULL CUSTOMERS
-
HASH JOIN
-
TABLE ACCESS FULL TIMES
-
PARTITION RANGE ALL
-
TABLE ACCESS BY LOCAL INDEX ROWID SALES
-
BITMAP CONVERSION TO ROWIDS
-
BITMAP AND
-
BITMAP INDEX SINGLE VALUE SALES_C_STATE_BJIX
-
BITMAP MERGE
-
BITMAP KEY ITERATION
-
BUFFER SORT
-
TABLE ACCESS FULL CHANNELS
-
BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
-
BITMAP MERGE
-
BITMAP KEY ITERATION
-
BUFFER SORT
-
TABLE ACCESS FULL TIMES
- BITMAP INDEX RANGE SCAN SALES_TIME_BIX
這個執行計劃和前面相比,區別在於使用點陣圖索引掃描顧客維度的那一部分沒有子查詢。這是因為在customer.cust_state_province的連線述語資訊已經滿足了點陣圖連線索引sales_c_state_bjix。
Oracle如何選擇使用星型轉換
最佳化器可以生成並儲存一個未經轉換的最優執行計劃。如果星型轉換被啟用,最佳化器將嘗試將其應用到查詢;如果適用,則產生一個使用轉換查詢的最優執行計劃。基於這兩個版本的執行計劃,最佳化器透過比較二者的成本估算,然後決定使用經過轉換的最優執行計劃或者是未經轉換的版本。
如果查詢需要訪問事實表中的大部分行,最好使用全表掃描,而不是使用星型轉換查詢。但是,如果維度表的約束謂詞具有充分的可選性,也就是說只會從事實表中檢索很小一部分資料,那麼基於轉換的執行計劃很有可能會更好。
需要注意的是,最佳化器會根據許多標準判斷,在它任務合理的情況下才會根據維度表生成子查詢。Oracle最佳化器並不保證為所有維度表生成子查詢。基於表和查詢的特性,最佳化器還可以決定該轉換是否值得被應用到特定查詢中。在這種情況下,最佳化器將會使用最優計劃。
使用星型轉換的限制條件
具有任何以下特徵的表均不支援星型轉換:
?查詢使用了與點陣圖訪問路徑不相容的表提示(hint)
?查詢包含繫結變數
?表沒有點陣圖索引。事實表的列必須有點陣圖索引,最佳化器才能建立子查詢。
?遠端事實表。然而,子查詢中允許使用遠端維度表。
?反連線的表
?已經在子查詢中用作維度表的表
?表是unmerged檢視,並且不是分割槽檢視
?事實表是unmerged檢視
?事實表是分割槽檢視
在以下場景最佳化器可能不會選擇星型轉換:
?表具有良好的單表訪問路徑
?表太小,不值得進行轉換
此外,在下列條件下星型轉換不使用臨時表:
?資料庫處於只讀模式
?星型查詢是序列化事務的一部分
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2145053/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 優化星型查詢優化
- /*+ START */ 強制使用星型查詢計劃
- vertica查詢最佳化
- MySQL查詢最佳化MySql
- StoneDB 子查詢最佳化
- oracle的查詢最佳化Oracle
- MySQL查詢效能最佳化MySql
- [Mysql]慢查詢最佳化MySql
- 慢查詢最佳化及分析
- 【Mysql】MySQL查詢最佳化-explainMySqlAI
- Django框架:8、聚合查詢、分組查詢、F與Q查詢、ORM查詢最佳化、ORM事務操作、ORM常用欄位型別、ORM常用欄位引數Django框架ORM型別
- MongoDB日期型別查詢MongoDB型別
- MySQL-10.索引最佳化與查詢最佳化MySql索引
- MySQL 如何最佳化大分頁查詢?MySql
- 【最佳化】模糊查詢索引問題索引
- 資料庫之查詢最佳化資料庫
- Microsoft SQL Server查詢最佳化方法ROSSQLServer
- 【GaussDB(for MySQL)】 Big IN查詢最佳化MySql
- 資料統計查詢最佳化
- 泛型化的折半查詢泛型
- ibatis查詢date型別BAT型別
- MySQL索引原理及慢查詢最佳化MySql索引
- openGauss SQL引擎(下)——查詢最佳化SQL
- Oracle臨時表最佳化查詢速度Oracle
- 大資料量資料查詢最佳化大資料
- Oracle臨時表 最佳化查詢速度Oracle
- 查詢語句(SELECT)的最佳化
- 《MySQL 進階篇》十五:索引最佳化和查詢最佳化MySql索引
- C++ 變數型別查詢C++變數型別
- 深度解讀 Cascades 查詢最佳化器
- MySQL查詢最佳化之explain的深入解析MySqlAI
- 使用預載入最佳化 Laravel Model 查詢Laravel
- 關於分頁查詢的最佳化思路
- 查詢最佳化器的引數設定
- SQL 查詢 exist join in 的用法和相應的適用場景 (最佳化查詢)SQL
- SAP 物料移動型別查詢表型別
- MySQL查詢最佳化的5個實用技巧MySql
- 透過預熱來最佳化POSTGRESQL的查詢SQL