ITPub博客

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

ITPUB SQL大赛之BUG(三)

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

由于SQL大赛题目相对比较困难,不但需要使用大量的特性,且SQL实现十分复杂,一般运行时间也会比较长,这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug

这篇描述碰到的ORA-22813错误。

ITPUB SQL大赛之BUG(一):http://yangtingkun.itpub.net/post/468/515815

ITPUB SQL大赛之BUG(二):http://yangtingkun.itpub.net/post/468/515926

 

严格意义上讲,这个问题可能并不是Oraclebug

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 result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c, max(replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)) over(partition by j order by i), ',' ,'')) col
 25  from lines_result, position
 26  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i)));
(select rownum - 1 b from dual connect by rownum <= 2),
 *
9 行出现错误:
ORA-22813:
操作数值超出系统的限制

导致问题的原因多半是由于wmsys.wm_concat函数造成的。正常情况下,SQL语句造成资源不足的错误多半是临时表空间无法扩展,而这里的ORA-22813错误,显然是PL/SQL程序中资源不足导致的,而wmsys.wm_concat函数显然是Oracle通过TYPE类型实现的PL/SQL函数。

虽然导致问题的真正原因是处理的数据量太大所致,但是Oracle的这个错误信息太不明确了,至少应该提示用户这个限制的具体值是多少。而这种错误信息对于解决问题显然没有任何的帮助。

这个问题最终通过修改SQL的方式来解决,由于当前的查询中wmsys.wm_concat所需要处理的数据量太大,最终将SQL改为先求出所有满足的记录,然后对这些记录进行变形,这样wmsys.wm_concat函数所处理记录数缩小了好几个数量级,因此避免了ORA-22813错误的出现。

 

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10542118