ITPUB SQL大赛之BUG（六）

时间：2011-04-09 来源：yangtingkun

由于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/515815

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并没有本质的区别，甚至认为二者的内部实现是相同的。但是现在看来，二者还是存在相当明显的差别的。