由於版本升級引發的SQL語句故障（續）

dbhelper發表於2014-11-29
SQL

 

在之前的文章《由於版本升級引發的SQL語句故障》一文中，筆者介紹了由於版本從9i上升到10g，引起原先報表group by語句行為差異問題和解決方法。一個朋友提出另一種策略，筆者也在這裡面討論一下。

 

1、索引分組列

 

筆者文章中介紹的方法，其實大都是透過修改SQL語句或者隱含引數的策略。一個朋友提出建議將分組列加索引，從而實現執行計劃恢復到sort group by。

那位朋友提出的SQL語句案例是：select empno, count(*) from scott.emp group by empno;。

筆者剛開始想到這樣的策略有一些合理之處。當加入索引之後，對分組列的檢索可以放在索引物件。索引本身葉子節點是有序的，這樣時候Hash Group By的需求就不是那麼大。也許會出現sort group by的動作。

如果這樣的策略能夠成功，我們也就不需要修改隱含引數這個大動作了。Oracle中，確定任何結論一定建立在特定版本下的實驗基礎上。

下面進行實驗。

 

2、環境準備設定

 

我們選擇Oracle 10g進行實驗，建立更有代表性的資料表T。

 

SQL> select * from v$version;

BANNER

------------------------

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

 

SQL> desc t;

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

（篇幅原因，有省略……）

 

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     52677

 

--收集統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

3、Group By實驗

 

我們首先看一下Oracle的預設行為。

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    22 |   132 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    22 |   132 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

和我們之前的結論相同，Oracle依據成本因素，選擇了hash group by動作，對全表資料進行內部小堆排序。

按照思路，我們給分組列新增索引。

 

--給owner列新增索引

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    23 |   138 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    23 |   138 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

問題出現了，給owner列新增索引，也沒有帶來sort group by。索引沒有走到。強制呢？

 

SQL> explain plan for select /*+index(t)*/owner, count(*) from t group by owner;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    23 |   138 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    23 |   138 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

 

使用index hint依然沒有效果。

冷靜下來，這樣是有問題的。空值null就是一個重點！空值是不會進入單鍵值索引葉子節點的，但是group by是會group空值的。

如果要讓group by和index合力作用，需要讓Oracle得到“背書”，也就是not null描述！

 

 

SQL> alter table t modify owner not null;

Table altered

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3139300238

--------------------------------------------------------------------------------

| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |             |    23 |   138 |    34  (18)| 00:00

|   1 |  HASH GROUP BY        |             |    23 |   138 |    34  (18)| 00:00

|   2 |   INDEX FAST FULL SCAN| IDX_T_OWNER | 52677 |   308K|    29   (4)| 00:00

--------------------------------------------------------------------------------

 

9 rows selected

 

 

Oracle得到認可，說這個列上不能有空。形成的新執行計劃出現索引，而且進行的是index fast full scan。Oracle認為：既然承認這個欄位不能出現空值，那麼所有的列值一定會出現在葉子節點上！所以“數數葉子節點，沒問題！”。

但是後面，“數”的動作，Oracle還是選擇了Hash Group By。沒有出現Sort Group By。

至此，筆者感覺結論已經有了：在一般情況下，如果沒有額外的宣告“非空、主鍵”，即使已經給分組列加索引，Oracle路徑也不會走。即使走到Oracle索引路徑，sort group by也不會出現。

 

4、sort group by nosort

 

回到那個朋友的案例，先看看那個SQL。

 

select empno, count(*) from scott.emp group by empno;

 

這個分組欄位太特殊了，是主鍵（非空、唯一）。這個語句其實沒有什麼含義，完全可以被下面取代。

 

Select empno, 1 from scott.emp group by empno;

 

我們看看Oracle是怎麼認為的。

 

 

SQL> explain plan for select empno, count(*) from scott.emp group by empno;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1749432681

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |        |    14 |    56 |     1   (0)| 00:00:01 |

|   1 |  SORT GROUP BY NOSORT|        |    14 |    56 |     1   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN    | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

9 rows selected

 

 

的確走了索引路徑，而且我們也看到了sort group by動作。但是注意：這個sort group by後面還有一個nosort。

在很多時候，Oracle CBO是非常聰明和狡猾的。有一種籃球中的用語叫“假動作”。Oracle在這裡面好像是sort group by，但是實際上，它根本就沒有排序，因為它知道：不需要排序。

Empno列為主鍵列：非空開啟了索引路徑，唯一開啟了group by取值為1。在CBO的世界裡，統計量大於RBO規則，比統計量成本更高的是約束和宣告！主鍵、外來鍵、非空這些，都是CBO工作的硬約束，比計算的cost值優先順序高得多！

所以，這個SQL執行計劃中雖然出現了Sort Group by，但是沒有意義。

 

5、結論

 

CBO的世界是非常複雜的。過去RBO 15個規則確定執行計劃已經不復存在了。近年來，統計量、索引、執行計劃深入人心，一些朋友對執行計劃也開始認識到位。但是，資料表定義、列約束是比這些更加重要的要素！Oracle CBO越智慧，越是會向“捷徑”找出路。

這就要求我們，特別是開發設計人員一定要謹慎細心，描述好每個欄位和索引。才能形成最好的執行計劃。

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

相關文章