ITPUB SQL大賽之BUG（一）
由於SQL大賽題目相對比較困難，不但需要使用大量的特性，且SQL實現十分複雜，一般執行時間也會比較長，這些因素導致碰到bug的機率直線上升。這裡介紹SQL大賽期間碰到的幾個bug。
這一篇介紹11.2上最佳化器處理樹形查詢的問題。
下面這個SQL在10.2中執行沒有問題：
SQL> var m number
SQL> var n number
SQL> exec :m := 2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :n := 5
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Elapsed: 00:00:00.02
SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );
M N ALLCNT
---------- ---------- ----------
2 5 92
Elapsed: 00:00:16.91
而在11.2中則存在問題：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
已用時間: 00: 00: 00.02
SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );
M N ALLCNT
---------- ---------- ----------
2 5 931
已用時間: 00: 01: 29.87
檢查執行計劃，發現10.2中執行計劃為：
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TEMP TABLE TRANSFORMATION |
| 2 | LOAD AS SELECT |
| 3 | COUNT |
|* 4 | CONNECT BY WITHOUT FILTERING |
| 5 | FAST DUAL |
| 6 | LOAD AS SELECT |
|* 7 | VIEW |
|* 8 | FILTER |
|* 9 | CONNECT BY WITHOUT FILTERING |
| 10 | COUNT |
| 11 | VIEW |
| 12 | TABLE ACCESS FULL |
| 13 | SORT AGGREGATE |
| 14 | VIEW |
|* 15 | FILTER |
| 16 | HASH GROUP BY |
| 17 | VIEW |
| 18 | TEMP TABLE TRANSFORMATION |
| 19 | LOAD AS SELECT |
| 20 | MERGE JOIN CARTESIAN |
| 21 | MERGE JOIN CARTESIAN |
| 22 | VIEW |
|* 23 | FILTER |
|* 24 | CONNECT BY WITHOUT FILTERING |
| 25 | TABLE ACCESS FULL |
| 26 | BUFFER SORT |
| 27 | VIEW |
| 28 | COUNT |
|* 29 | CONNECT BY WITHOUT FILTERING|
| 30 | FAST DUAL |
| 31 | BUFFER SORT |
| 32 | VIEW |
| 33 | COUNT |
|* 34 | CONNECT BY WITHOUT FILTERING |
| 35 | FAST DUAL |
| 36 | LOAD AS SELECT |
| 37 | HASH GROUP BY |
| 38 | TABLE ACCESS FULL |
| 39 | LOAD AS SELECT |
| 40 | HASH GROUP BY |
| 41 | TABLE ACCESS FULL |
| 42 | LOAD AS SELECT |
| 43 | HASH GROUP BY |
| 44 | TABLE ACCESS FULL |
| 45 | VIEW |
| 46 | TABLE ACCESS FULL |
---------------------------------------------------
而11.2的執行計劃為：
-------------------------------------------------------
| Id | Operation |
-------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
| 2 | VIEW |
|* 3 | FILTER |
| 4 | HASH GROUP BY |
| 5 | VIEW |
| 6 | TEMP TABLE TRANSFORMATION |
| 7 | LOAD AS SELECT |
| 8 | MERGE JOIN CARTESIAN |
| 9 | MERGE JOIN CARTESIAN |
| 10 | VIEW |
|* 11 | FILTER |
|* 12 | CONNECT BY WITHOUT FILTERING |
| 13 | VIEW |
|* 14 | FILTER |
|* 15 | CONNECT BY WITHOUT FILTERING |
| 16 | VIEW |
| 17 | COUNT |
|* 18 | CONNECT BY WITHOUT FILTERING|
| 19 | FAST DUAL |
| 20 | BUFFER SORT |
| 21 | VIEW |
| 22 | COUNT |
|* 23 | CONNECT BY WITHOUT FILTERING |
| 24 | FAST DUAL |
| 25 | BUFFER SORT |
| 26 | VIEW |
| 27 | COUNT |
|* 28 | CONNECT BY WITHOUT FILTERING |
| 29 | FAST DUAL |
| 30 | LOAD AS SELECT |
| 31 | HASH GROUP BY |
| 32 | TABLE ACCESS FULL |
| 33 | LOAD AS SELECT |
| 34 | HASH GROUP BY |
| 35 | TABLE ACCESS FULL |
| 36 | LOAD AS SELECT |
| 37 | HASH GROUP BY |
| 38 | TABLE ACCESS FULL |
| 39 | VIEW |
| 40 | TABLE ACCESS FULL |
-------------------------------------------------------
顯然是11g的CBO在嘗試最佳化樹形查詢的時候出現了錯誤，解決方法也比較簡單，可以利用ROWNUM固定結果集的特性，避免11g的CBO最佳化執行計劃：
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
已用時間: 00: 00: 00.00
SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select rownum, line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );
M N ALLCNT
---------- ---------- ----------
2 5 92
已用時間: 00: 00: 25.12
除了利用ROWNUM之外，還可以使用OPTIMIZER_FEATURES_ENABLE提示，或者在會話級或例項級設定OPTIMIZER_FEATURES_ENABLE引數的方式，將最佳化器屬性降低到11.1.0.7或以下的版本。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-691346/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ITPUB SQL大賽之BUG（八）SQL
- ITPUB SQL大賽之BUG（七）SQL
- ITPUB SQL大賽之BUG（六）SQL
- ITPUB SQL大賽之BUG（五）SQL
- ITPUB SQL大賽之BUG（四）SQL
- ITPUB SQL大賽之BUG（三）SQL
- ITPUB SQL大賽之BUG（二）SQL
- ITPUB SQL大賽第一期SQL
- ITPUB SQL大賽第二期（一）SQL
- ITPUB SQL大賽第三期SQL
- ITPUB SQL大賽第二期SQL
- ITPUB SQL大賽第四期SQL
- ITPUB SQL大賽第三期（二）SQL
- ITPUB SQL大賽第二期（二）SQL
- sql大賽總結SQL
- sql大賽第四期SQL
- 一路走來之“網路技術大賽”
- 第一屆SQL大賽第一期優秀解題思路彙總SQL
- SQL資料庫程式設計大賽隨感SQL資料庫程式設計
- SQL資料庫程式設計大賽開幕SQL資料庫程式設計
- 在SQL Developer中Debug一個procedureSQLDeveloper
- itpub awr案例分析之一
- SQL指令碼生成的一些BUG(1)(轉)SQL指令碼
- SQL指令碼生成的一些BUG(2)(轉)SQL指令碼
- itpub 復活節最短sql（未完成程式碼）SQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- itpub
- upyun開發者大賽
- 積木大賽
- “盛拓傳媒杯”SQL資料庫程式設計大賽第一期程式碼SQL資料庫程式設計
- 【SQL 學習】表連線--natural join 的一個bugSQL
- 【ITPUB】ITPUB十週年感言 - Secooler
- Js逆向之猿人學比賽第一題JS
- oracle之BUG 7497640Oracle
- 2014第六屆華為程式設計大賽初賽第一輪程式設計
- 程式設計之美初賽第一場--焦距程式設計
- 程式設計之美初賽第一場--樹程式設計
- 第一屆天池 PolarDB 資料庫效能大賽資料庫