ITPUB SQL大賽之BUG（六）
由於SQL大賽題目相對比較困難，不但需要使用大量的特性，且SQL實現十分複雜，一般執行時間也會比較長，這些因素導致碰到bug的機率直線上升。這裡介紹SQL大賽期間碰到的幾個bug。
這篇繼續探討ORA-1489錯誤。
ITPUB SQL大賽之BUG（一）：http://yangtingkun.itpub.net/post/468/515815
ITPUB SQL大賽之BUG（二）：http://yangtingkun.itpub.net/post/468/515926
ITPUB SQL大賽之BUG（三）：http://yangtingkun.itpub.net/post/468/515982
ITPUB SQL大賽之BUG（四）：http://yangtingkun.itpub.net/post/468/516023
ITPUB SQL大賽之BUG（五）：http://yangtingkun.itpub.net/post/468/516219
上一篇提到了執行計劃和SQL寫法都有可能是錯誤出現的原因，這裡發現如果將一個字串連線操作||改變為CONCAT函式，則錯誤不再出現：
SQL> SET AUTOT TRACE
SQL> WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
2
(
3
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
4
FROM ROUTES
5
UNION ALL
6
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
7
FROM ROUTES R, ROUTE_ALL_S A
8
WHERE A.C2 = R.CITY1
9
),
10
ROUTE_S AS
11
(
12
SELECT C1 R, C2 T, MIN(DIS) DIS
13
FROM ROUTE_ALL_S
14
GROUP BY C1, C2
15
),
16
ROUTE_D AS
17
(
18
SELECT R, T, DIS
19
FROM ROUTE_S
20
UNION ALL
21
SELECT T, R, DIS
22
FROM ROUTE_S
23
),
24
ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS
25
(
26
SELECT R, T, R || T, DIS
27
FROM ROUTE_D
28
UNION ALL
29
SELECT SUBSTR(LINES, 1, 1), T, LINES || '-' || R || T, DIS + DISTANCE
30
FROM ROUTE_D R, ROUTE_ALL_D A
31
WHERE A.C2 = R.R
32
AND INSTR(LINES, R || T) = 0
33
AND INSTR(LINES, T || R) = 0
34
AND C1 != T
35
AND INSTR(LINES, R, 1, 2) = 0
36
AND INSTR(LINES, T, 1, 2) = 0
37
AND DISTANCE + DIS <= NVL((SELECT DISTANCE FROM ROUTE_D RS WHERE C1 =
RS.R AND R.T = RS.T), 9.9E38)
38
),
39
RESULT AS
40
(
41
SELECT C1 R, C2 T,
42 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1, C2) COST,
43 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1) COST_CITY
44
FROM ROUTE_ALL_D R, CITIES C
45
WHERE R.C2 = C.CITY_NAME(+)
46
GROUP BY C1, C2
47
)
48
SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
49
FROM RESULT
50
WHERE COST_CITY = (SELECT MIN(COST_CITY) FROM RESULT)
51
GROUP BY GROUPING SETS ((R, T), R)
52
ORDER BY DECODE(T, 'TOTAL', '0', T);
FROM ROUTE_ALL_D R, CITIES C
*
第 44 行出現錯誤:
ORA-01489: 字串連線的結果過長
已用時間:
00: 00: 03.44
SQL> SET AUTOT TRACE EXP
SQL> /
已用時間: 00: 00: 00.00
執行計劃
----------------------------------------------------------
Plan hash value: 724229468
--------------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
TEMP TABLE TRANSFORMATION | |
| 2 |
LOAD AS SELECT | SYS_TEMP_0FD9D6770_D263E88B
|
| 3 |
HASH GROUP BY | |
| 4 | VIEW | |
| 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | |
| 6 | TABLE ACCESS FULL | ROUTES |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL | ROUTES |
| 9 | RECURSIVE WITH PUMP | |
| 10 |
LOAD AS SELECT | SYS_TEMP_0FD9D6771_D263E88B
|
| 11 |
UNION-ALL | |
| 12 | VIEW | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6770_D263E88B
|
| 14 | VIEW
| |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6770_D263E88B
|
| 16 |
LOAD AS SELECT | SYS_TEMP_0FD9D6772_D263E88B
|
| 17 |
WINDOW BUFFER | |
| 18 | SORT GROUP BY | |
|* 19 | HASH JOIN OUTER | |
| 20 | VIEW | |
| 21 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| |
| 22 | VIEW | |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6771_D263E88B
|
|* 24 | FILTER | |
|* 25 | HASH JOIN | |
| 26 | VIEW | |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6771_D263E88B
|
| 28 | RECURSIVE WITH PUMP | |
|* 29 | VIEW | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6771_D263E88B
|
| 31 | TABLE ACCESS FULL | CITIES |
| 32 |
SORT ORDER BY | |
| 33 |
SORT GROUP BY ROLLUP | |
|* 34 | VIEW | |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6772_D263E88B
|
| 36 | SORT AGGREGATE | |
| 37 |
VIEW | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6772_D263E88B
|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."C2"="R"."CITY1")
19 - access("R"."C2"="C"."CITY_NAME"(+))
24 - filter("DISTANCE"+"DIS"<=NVL(
(SELECT :B1 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "R","C1" "T","C2"
"DIS" FROM "SYS"."SYS_TEMP_0FD9D6771_D263E88B" "T1")
"RS" WHERE "RS"."R"=:B2 AND "RS"."T"=:B3),9.9E38))
25 - access("A"."C2"="R"."R")
filter(INSTR("LINES","R"||"T")=0
AND INSTR("LINES","T"||"R")=0 AND "C1"<>"T"
AND INSTR("LINES","R",1,2)=0 AND INSTR("LINES","T",1,2)=0)
29 - filter("RS"."R"=:B1
AND "RS"."T"=:B2)
34 - filter("COST_CITY"= (SELECT
MIN("COST_CITY") FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "R","C1"
"T","C2" "COST","C3" "COST_CITY"
FROM "SYS"."SYS_TEMP_0FD9D6772_D263E88B" "T1") "RESULT"))
Note
-----
- dynamic sampling used for this statement
(level=2)
SQL> SET AUTOT TRACE
SQL> WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
2
(
3
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
4
FROM ROUTES
5
UNION ALL
6
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
7
FROM ROUTES R, ROUTE_ALL_S A
8
WHERE A.C2 = R.CITY1
9
),
10
ROUTE_S AS
11
(
12
SELECT C1 R, C2 T, MIN(DIS) DIS
13
FROM ROUTE_ALL_S
14
GROUP BY C1, C2
15
),
16
ROUTE_D AS
17
(
18
SELECT R, T, DIS
19
FROM ROUTE_S
20
UNION ALL
21
SELECT T, R, DIS
22
FROM ROUTE_S
23
),
24
ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS
25
(
26
SELECT R, T, R || T, DIS
27
FROM ROUTE_D
28
UNION ALL
29
SELECT SUBSTR(LINES, 1, 1), T, CONCAT(LINES, '-' || R || T), DIS + DISTANCE
30
FROM ROUTE_D R, ROUTE_ALL_D A
31
WHERE A.C2 = R.R
32
AND INSTR(LINES, R || T) = 0
33
AND INSTR(LINES, T || R) = 0
34
AND C1 != T
35
AND INSTR(LINES, R, 1, 2) = 0
36
AND INSTR(LINES, T, 1, 2) = 0
37
AND DISTANCE + DIS <= NVL((SELECT DISTANCE FROM ROUTE_D RS WHERE C1 =
RS.R AND R.T = RS.T), 9.9E38)
38
),
39
RESULT AS
40
(
41
SELECT C1 R, C2 T,
42 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1, C2) COST,
43 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1) COST_CITY
44
FROM ROUTE_ALL_D R, CITIES C
45
WHERE R.C2 = C.CITY_NAME(+)
46
GROUP BY C1, C2
47
)
48
SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
49
FROM RESULT
50
WHERE COST_CITY = (SELECT MIN(COST_CITY) FROM RESULT)
51
GROUP BY GROUPING SETS ((R, T), R)
52
ORDER BY DECODE(T, 'TOTAL', '0', T);
已選擇12行。
已用時間: 00: 00: 05.30
執行計劃
----------------------------------------------------------
Plan hash value: 2578151800
--------------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
TEMP TABLE TRANSFORMATION | |
| 2 |
LOAD AS SELECT | SYS_TEMP_0FD9D6776_D263E88B
|
| 3 |
HASH GROUP BY | |
| 4 | VIEW | |
| 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | |
| 6 | TABLE ACCESS FULL | ROUTES |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL | ROUTES |
| 9 | RECURSIVE WITH PUMP | |
| 10 |
LOAD AS SELECT | SYS_TEMP_0FD9D6777_D263E88B
|
| 11 |
UNION-ALL | |
| 12 | VIEW | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6776_D263E88B
|
| 14 | VIEW | |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6776_D263E88B
|
| 16 |
LOAD AS SELECT | SYS_TEMP_0FD9D6778_D263E88B
|
| 17 |
WINDOW BUFFER | |
| 18 | SORT GROUP BY | |
|* 19 | HASH JOIN OUTER | |
| 20 | VIEW | |
| 21 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| |
| 22 | VIEW | |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6777_D263E88B
|
|* 24 | FILTER | |
|* 25 | HASH JOIN | |
| 26 | VIEW | |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6777_D263E88B
|
| 28 | RECURSIVE WITH PUMP | |
|* 29 | VIEW | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6777_D263E88B
|
| 31 | TABLE ACCESS FULL | CITIES |
| 32 |
SORT ORDER BY | |
| 33 |
SORT GROUP BY ROLLUP | |
|* 34 | VIEW | |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6778_D263E88B
|
| 36 | SORT AGGREGATE | |
| 37 |
VIEW | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6778_D263E88B
|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."C2"="R"."CITY1")
19 - access("R"."C2"="C"."CITY_NAME"(+))
24 - filter("DISTANCE"+"DIS"<=NVL(
(SELECT :B1 FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "R","C1" "T","C2"
"DIS" FROM "SYS"."SYS_TEMP_0FD9D6777_D263E88B" "T1")
"RS" WHERE "RS"."R"=:B2 AND "RS"."T"=:B3),9.9E38))
25 - access("A"."C2"="R"."R")
filter(INSTR("LINES","R"||"T")=0
AND INSTR("LINES","T"||"R")=0 AND "C1"<>"T"
AND INSTR("LINES","R",1,2)=0 AND INSTR("LINES","T",1,2)=0)
29 - filter("RS"."R"=:B1
AND "RS"."T"=:B2)
34 - filter("COST_CITY"= (SELECT
MIN("COST_CITY") FROM (SELECT /*+
CACHE_TEMP_TABLE ("T1") */ "C0" "R","C1"
"T","C2" "COST","C3" "COST_CITY"
FROM "SYS"."SYS_TEMP_0FD9D6778_D263E88B" "T1") "RESULT"))
Note
-----
- dynamic sampling used for this statement
(level=2)
統計資訊
----------------------------------------------------------
498 recursive calls
101011 db block gets
346853 consistent gets
3 physical reads
1756 redo size
825 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts
(disk)
12 rows processed
這次兩個SQL的唯一區別之處在於，錯誤的SQL使用了||連線字串，而改用了CONCAT函式後，則錯誤消失。二者的執行計劃則完全一致，都是HASH JOIN OUTER，以遞迴WITH查詢結果作為驅動表。
顯然可以排除執行計劃導致錯誤的因素了，問題肯定是SQL寫法導致的。
雖然||是一個操作而CONCAT是一個函式，但是我一直認為||和CONCAT並沒有本質的區別，甚至認為二者的內部實現是相同的。但是現在看來，二者還是存在相當明顯的差別的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-692172/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 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 Server索引管理之六大鐵律SQLServer索引
- sql大賽總結SQL
- sql大賽第四期SQL
- SQL優化之六脈神劍SQL優化
- 2014第六屆華為程式設計大賽初賽第四輪程式設計
- 2014第六屆華為程式設計大賽初賽第一輪程式設計
- SQL資料庫程式設計大賽隨感SQL資料庫程式設計
- SQL資料庫程式設計大賽開幕SQL資料庫程式設計
- SQL*Plus菜鳥筆記之第六篇SQL筆記
- 設計模式之六大原則設計模式
- 綠盟科技助力2021年中國技能大賽-西藏自治區第六屆網路安全技能大賽圓滿落幕
- 2014第六屆華為創新杯程式設計大賽初賽解題報告程式設計
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- itpub 復活節最短sql（未完成程式碼）SQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- 繪六藝君子，築三國風骨，網易遊戲《率土之濱》高校原畫大賽正式開啟遊戲
- itpub
- 物件導向之六大設計原則物件
- Android之四大元件、六大布局、五大儲存Android元件
- SQL最佳化案例-分割槽索引之無字首索引（六）SQL索引
- SQL經典練習題48道之六(36-40)SQL
- 2024六安市第二屆網路安全大賽-misc
- 【ITPUB】ITPUB十週年感言 - Secooler
- 一路走來之“網路技術大賽”