ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ITPUB SQL大赛之BUG(五)

ITPUB SQL大赛之BUG(五)

原创 Linux操作系统 作者:yangtingkun 时间:2011-04-07 23:59:08 0 删除 编辑

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

 这个错误的本质并不是Oraclebug,只是字符串超长而已,不过引发了一系列的现象比较有意思,简单记录一下:

SQL> SET AUTOT TRACE
SQL> SET LINES 140
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 SUBSTR(LINES, 1, 1) R, SUBSTR(LINES, LENGTH(LINES)) T, MIN(DIS) DIS
 13     FROM ROUTE_ALL_S
 14     GROUP BY SUBSTR(LINES, 1, 1), SUBSTR(LINES, LENGTH(LINES))
 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);

已选择12行。

已用时间:  00: 00: 05.36

执行计划
----------------------------------------------------------
Plan hash value: 4201235322

--------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                        |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                             |
|   1 |  TEMP TABLE TRANSFORMATION                     |                             |
|   2 |   LOAD AS SELECT                               | SYS_TEMP_0FD9D6758_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_0FD9D6759_D263E88B |
|  11 |    UNION-ALL                                   |                             |
|  12 |     VIEW                                       |                             |
|  13 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D6758_D263E88B |
|  14 |     VIEW                                       |                             |
|  15 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D6758_D263E88B |
|  16 |   LOAD AS SELECT                               | SYS_TEMP_0FD9D675A_D263E88B |
|  17 |    WINDOW BUFFER                               |                             |
|  18 |     SORT GROUP BY                              |                             |
|* 19 |      HASH JOIN RIGHT OUTER                     |                             |
|  20 |       TABLE ACCESS FULL                        | CITIES                      |
|  21 |       VIEW                                     |                             |
|  22 |        UNION ALL (RECURSIVE WITH) BREADTH FIRST|                             |
|  23 |         VIEW                                   |                             |
|  24 |          TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6759_D263E88B |
|* 25 |         FILTER                                 |                             |
|* 26 |          HASH JOIN                             |                             |
|  27 |           VIEW                                 |                             |
|  28 |            TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6759_D263E88B |
|  29 |           RECURSIVE WITH PUMP                  |                             |
|* 30 |          VIEW                                  |                             |
|  31 |           TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6759_D263E88B |
|  32 |   SORT ORDER BY                                |                             |
|  33 |    SORT GROUP BY ROLLUP                        |                             |
|* 34 |     VIEW                                       |                             |
|  35 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D675A_D263E88B |
|  36 |      SORT AGGREGATE                            |                             |
|  37 |       VIEW                                     |                             |
|  38 |        TABLE ACCESS FULL                       | SYS_TEMP_0FD9D675A_D263E88B |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("A"."C2"="R"."CITY1")
  19 - access("R"."C2"="C"."CITY_NAME"(+))
  25 - filter("DISTANCE"+"DIS"<=NVL( (SELECT :B1 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "R","C1" "T","C2" "DIS" FROM "SYS"."SYS_TEMP_0FD9D6759_D263E88B" "T1") "RS" WHERE "RS"."R"=:B2 AND "RS"."T"=:B3),9.9E38))
  26 - 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)
  30 - 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_0FD9D675A_D263E88B" "T1") "RESULT"))

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
        498  recursive calls
     101011  db block gets
     347396  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> 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);
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
*
1 行出现错误:
ORA-01489:
字符串连接的结果过长


已用时间:  00: 00: 03.49
SQL> SET AUTOT TRACE EXP
SQL> /
执行计划
----------------------------------------------------------
Plan hash value: 710466196

--------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                        |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                             |
|   1 |  TEMP TABLE TRANSFORMATION                     |                             |
|   2 |   LOAD AS SELECT                               | SYS_TEMP_0FD9D675E_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_0FD9D675F_D263E88B |
|  11 |    UNION-ALL                                   |                             |
|  12 |     VIEW                                       |                             |
|  13 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D675E_D263E88B |
|  14 |     VIEW                                       |                             |
|  15 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D675E_D263E88B |
|  16 |   LOAD AS SELECT                               | SYS_TEMP_0FD9D6760_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_0FD9D675F_D263E88B |
|* 24 |         FILTER                                 |                             |
|* 25 |          HASH JOIN                             |                             |
|  26 |           VIEW                                 |                             |
|  27 |            TABLE ACCESS FULL                   | SYS_TEMP_0FD9D675F_D263E88B |
|  28 |           RECURSIVE WITH PUMP                  |                             |
|* 29 |          VIEW                                  |                             |
|  30 |           TABLE ACCESS FULL                    | SYS_TEMP_0FD9D675F_D263E88B |
|  31 |       TABLE ACCESS FULL                        | CITIES                      |
|  32 |   SORT ORDER BY                                |                             |
|  33 |    SORT GROUP BY ROLLUP                        |                             |
|* 34 |     VIEW                                       |                             |
|  35 |      TABLE ACCESS FULL                         | SYS_TEMP_0FD9D6760_D263E88B |
|  36 |      SORT AGGREGATE                            |                             |
|  37 |       VIEW                                     |                             |
|  38 |        TABLE ACCESS FULL                       | SYS_TEMP_0FD9D6760_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_0FD9D675F_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_0FD9D6760_D263E88B" "T1") "RESULT"))

Note
-----
   - dynamic sampling used for this statement (level=2)

这两个SQL的区别在于,在ROUTE_S字句中,不出错误的SQL是利用LINES列通过SUBSTR来获取起始城市和终点城市,而报错ORA-1489SQL是直接使用ROUTE_ALL_S字句定义的C1C2列。

而从执行计划上看,二者也有小的差别,不报错的SQL使用了HASH JOIN RIGHT OUTER连接方式,将CITIES表作为驱动表。而报错的SQL使用了HASH JOIN OUTER,将递归WITH查询作为驱动表。

显然问题不是SQL写法导致的,就是执行计划导致的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-691919/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10525428