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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 新年第一賽，提交BUG獎勵多！
- 我與Itpub
- ITPUB的採訪稿
- 一個 MySQL sql 語句執行順序帶來的 bugMySql
- 1997年，她們參加了第一屆女子《雷神之錘》大賽
- 【Python】備份itpub部落格Python
- 開通ITPub部落格了！
- MySQL之SQL優化詳解（一）MySql優化
- 恭喜您成功申請ITPUB BLOG
- itpub插入超連結測試
- [SWPUCTF 2021 新生賽]easy_sqlSQL
- 有趣的BUG之Stack Overflow
- 「雜文」演算法競賽之黑話大賞演算法
- 解Bug之路-中介軟體"SQL重複執行"SQL
- Js逆向之猿人學比賽第一題JS
- 開啟ITPUB部落格專欄技能！
- 轉戰ITpub CSDN ID Dadunl43
- 解Bug之路-記一次中介軟體導致的慢SQL排查過程SQL
- [BUG反饋]AdminController類的一個小bugController
- 用一條SQL語句顯示所有可能的比賽組合SQL
- 畫江湖之SQL優化 -10大經典案例場景SQL優化
- 一個導致JVM實體記憶體消耗大的BugJVM記憶體
- 【比賽覆盤】2024第七屆“傳智杯”全國大學生計算機大賽程式設計挑戰賽（初賽第一場）計算機程式設計
- SQL語法之SQL 萬用字元SQL字元
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 積木大賽
- 第一屆天池 PolarDB 資料庫效能大賽資料庫
- http://blog.itpub.net/69994146/viewspace-2865498/HTTPView
- oracle的一個bugOracle
- TestHome 的一個 Bug
- Snakeyaml的一個bugYAML
- 一個奇怪的 Bug
- Sql 之 join 001SQL
- PostgreSQL之SQL函式介紹及實踐（一）SQL函式
- PostgreSQL技術大講堂 - 第72講：索引與SQL調優之禁忌之戀SQL索引
- CUSGA第一屆中國大學生遊戲開發創作大賽複賽名單公佈!遊戲開發
- WEB三大攻擊之—SQL隱碼攻擊與防護WebSQL
- 我的前端筆記 之 bug篇前端筆記
- ORACLE for aix 11.2.0.1 DATAPUMP expdp之BUG 9470768OracleAI