SQL最佳化涉及到的基本概念（一）

轉自http://blog.itpub.net/post/26/3256

參考：

Overview of SQL Processing
1. parser 進行語意和語法分析
2. Optimizer 利用RBO,CBO等方法決定產生查詢結果的最有效路徑
3. Row Source Generator 從2中接受最佳化後的方案，併產生SQL的Execution Plan
4. SQL Execution Engine執行該執行計劃，併產生查詢結果

Features that Require the CBO
1. Partitioned tables and indexes
2. Index-organized tables
3. Reverse key indexes
4. Function-based indexes
5. SAMPLE clauses in a SELECT statement
6. Parallel query and parallel DML
7. Star transformations and star joins
8. Extensible optimizer
9. Query rewrite with materialized views
10. Enterprise Manager progress meter
11. Hash joins
12. Bitmap indexes and bitmap join indexes
13. Index skip scans
即使OPTIMIZER_MODE＝true，這些features仍然會使用CBO

Components of the CBO

Query Transformer
資料來自於parse後的SQL,是一系列的query block。其目標是測定SQL的形式是否有利於產生好的query plan。有以下4種：
1. View Merging
SQL 中的view 被擴充套件到單獨的query block中。Optimizer會單獨分析view query block，這樣通常會導致在整體最佳化上得不到最優的結果。因此query transformer會將大部分view和其他的query block 合併，這樣可以在整體上統一最佳化。
2. Predicate Pushing
針對沒有被merge的view, push the relevant predicates from the containing query block into the view query block, which can be used either to access indexes or to act as filters
3. Subquery Unnesting
子查詢是nested在主查詢中的，這樣很難得到好的最佳化結果。所以將他們unnested，變成join
4. Query Rewrite with Materialized Views
如果查詢與某個物化檢視符合的化，則會按照物化檢視重寫這個查詢，因為物化檢視的結果都是事先計算好的。

Estimator
會產生３中度量標準：
1. Selectivity
表示有多少rows可以透過謂詞被選擇出來，大小介於0.0~1.0，0表示沒有row被選擇出來。
如果沒有statistics，estimator會使用一個預設的selectivity 值，這個值根據謂詞的不同而異。比如’=’的selectivity小於’如果有statistics，比如對於last_name = 'Smith'，estimator使用last_name列的distinct 值的倒數，作為selectivity。
如果last_name列上有histogram，則使用住狀圖中'Smith'值的分佈情況作為selectivity
2. Cardinality
表示一個row set的行數。
Base cardinality：base table的行數。如果analyze了，則直接使用分析資料。如果沒有，則使用佔用的extents的多少來估計。
Effective cardinality：有效行集，指從基表中選擇出來的行數。是Base cardinality和表上組合謂詞的Selectivity的乘積。
Join cardinality：兩表join後產生的行數。是兩表cardinality的承積(Cartesian)，再承以Join謂詞的selectivity
Distinct cardinality：顧名思義，不同值資料的行數
Group cardinality：表示能分幾組
group cardinality lies between max ( dist. card. colx , dist. card. coly )
and min ( (dist. card. colx * dist. card. coly) ,
num rows in row set )
3. Cost
Disk I/O, CPU, Memory used的綜合考慮。
Clustering Factor:
index的一種屬性，表示被索引的行在資料塊中的分佈情況，表徵表中資料的儲存順序和某索引欄位順序的符合程度。直接影響使用rowid 找到row的cost。大小介於block數和rownum之間。
（以下來自biti_rainy http://blog.itpub.net/post/330/2970 ）
oracle 按照索引塊中儲存的rowid 來識別相臨的索引中記錄 在 表block中是否為相同塊,如果索引中存在記錄 rowid a,b,c,d……,若b 和 a 是同一個block，則比較 c 和 b，若這時不是同一個block，則clustering_factor + 1 ,然後比較 d 和 c，若還不是同一個 block，則clustering_factor + 1……
若clustering_factor值越小越接近block數，越好，則說明表中資料具有比較好的跟索引欄位一樣排序順序的儲存，透過索引進行 range scan 的代價比較小，需要讀取的表塊可能比較少。
若clustering_factor 接近 row數量，則說明表中資料和索引欄位排序順序差異很大，雜亂無張。則透過索引進行 range scan 的代價比較大，需要讀取的表塊可能更多。
在oracle 920 開始，對於clustering_factor 比較接近表塊數量的根據索引的大範圍查詢做了特別的處理，不再是讀一個索引記錄去搜尋一個表記錄了，而是成批處理(透過索引塊一批rowid一次去表塊中獲得一批記錄)，這樣就大大節約了讀的成本( consistent gets)。
Join Cost:
Nested loop join cost = outer access cost + (inner access cost * outer cardinality)
(注：因為outer table相當於外層迴圈，對於每個row都要遍歷一遍內表的所有row)
Sort merge join cost = outer access cost + inner access cost + sort costs (outer and inner, if sort is used)
Hash join cost = (outer access cost * # of hash partitions) + inner access cost
首先理解hash table 的資料結構：
可以把hash table 看做一個2維陣列a[200][100]，現有1000個無序數字用來被查詢。我們考慮把這1000個數字除以200，根據其餘數放在a[200][100]中，餘數就是陣列的第一維下標。這樣平均一個a[i]只放5個數字。當查詢的時候，對數字除以200（這就是一個簡單的hash 演算法），根據餘數i去a[i]中查詢，大約遍歷5次就能找到。
Inner row（小表）被hash在記憶體中，並且透過join key建立hash table（作為第一個下標），然後scan outer table，到hash table中查詢joined rows（透過hash 演算法）。
hash table會按照multiblock_IO 決定分成幾個partitions。如果hash table 太大超出了hash_area_size，則將超出部分的partitions放到temporary segments中。
可以透過10104 events 檢視hash join 的statistics
ALTER SESSION SET EVENTS '10104 trace name context forever, level 10'; 比如：
Total number of partitions:
Number of partitions which could fit in memory:
如果後者大於前者，則說明一些partitions 因為超出了hash_area，要被放置到臨時表空間中。

Plan Generator
作用是儘可能產生不同的執行計劃，選擇cost最低的一種。

[@more@]