ITPUB SQL大賽之BUG(五)

yangtingkun發表於2011-04-07

由於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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章