Oracle 11g新特性之收集多列統計資訊

        通常，當我們將SQL語句提交給Oracle資料庫時，Oracle會選擇一種最優方式來執行，這是透過查詢最佳化器Query Optimizer來實現的。CBO（Cost-Based Optimizer）是Oracle預設使用的查詢最佳化器模式。在CBO中，SQL執行計劃的生成，是以一種尋找成本（Cost）最優為目標導向的執行計劃探索過程。所謂成本（Cost）就是將CPU和IO消耗整合起來的量化指標，每一個執行計劃的成本就是經過最佳化器內部公式估算出的數字值。

        我們在寫SQL語句的時候，經常會碰到where子句後面有多個條件的情況，也就是根據多列的條件篩選得到資料。預設情況下，oracle會把多列的選擇率（selectivity）相乘從而得到where語句的選擇率，這樣有可能造成選擇率（selectivity）不準確，從而導致最佳化器做出錯誤的判斷。為了能夠讓最佳化器做出準確的判斷，從而生成準確的執行計劃，oracle在11g資料庫中引入了收集多列統計資訊。本文透過對測試表的多條件查詢，介紹收集多列統計資訊的重要性。

1.環境準備

我們在Oracle 11g中進行試驗。

在hr使用者下建立測試表hoegh，重複插入資料，資料量相當於16個employees表（總行數1712＝107*16）。

2.按照常規方法收集統計量資訊；

3.檢視執行單個條件的where語句的執行計劃

從執行計劃可以看出返回了16行記錄，結果沒有問題。可是，這個16是哪兒來的呢，我們先要了解選擇率（selectivity）和返回行數是如何計算的：
選擇率（selectivity）＝在本例中是 1/唯一值
返回行數＝選擇率（selectivity）＊表記錄總數

也就是說，在這個查詢語句中，選擇率＝1/107，返回行數＝1/107*1712=16

4.檢視執行兩個條件的where語句的執行計劃
點選(此處)摺疊或開啟

1. SQL>
   
2. SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';
   
3. 
   
4. Explained.
   
5. 
   
6. SQL>
   
7. SQL> select * from table(dbms_xplan.display);
   
8. 
   
9. PLAN_TABLE_OUTPUT
   
10. --------------------------------------------------------------------------------
    
11. Plan hash value: 774871165
    
12. 
    
13. ---------------------------------------------------------------------------
    
14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
15. ---------------------------------------------------------------------------
    
16. | 0 | SELECT STATEMENT | | 1 | 69 | 8 (0)| 00:00:01 |
    
17. |* 1 | TABLE ACCESS FULL| HOEGH | 1 | 69 | 8 (0)| 00:00:01 |
    
18. ---------------------------------------------------------------------------
    
19. 
    
20. Predicate Information (identified by operation id):
    
21. ---------------------------------------------------
    
22. 
    
23. PLAN_TABLE_OUTPUT
    
24. --------------------------------------------------------------------------------
    
25. 
    
26.    1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')
    
27. 
    
28. 13 rows selected.
    
29. 
    
30. SQL>

從執行計劃可以看出返回了1行記錄，而事實又是什麼樣的呢？我們執行一下這條sql語句。

點選(此處)摺疊或開啟

1. SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\';
   
2. 
   
3.   COUNT(*)
   
4. ----------
   
5.         16
   
6. 
   
7. SQL>

由此看出，測試表hoegh符合查詢條件的資料有16行，而執行計劃提示的只有1行，出錯了。這是怎麼回事呢，也就是我們在開篇提到的選擇率（selectivity）出了問題。
在這個多列條件查詢語句中，選擇率＝1/107＊1/107，返回行數＝1/107＊1/107*1712=16/107<1；由於表中存在符合條件的記錄，並且返回行數不可能小於1，所以Oracle返回了1。

5.收集多列統計資訊，再次檢視兩個條件的where語句的執行計劃

從執行計劃的結果來看，同樣的一條sql查詢語句，在收集多列統計資訊後，Oracle的選擇率（selectivity）由錯變對，這是由於sql語句中的兩個條件是有關聯的，即employee_id和email在employees表中都是唯一的，都可以唯一標識一行記錄；而在收集多列統計資訊之前，Oracle並不知道這兩個查詢條件有關聯，所以在計算選擇率（selectivity）時，只是簡單地採取了相乘的方法。