ITPub博客

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

ITPUB SQL大赛之BUG(二)

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

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

第二个bug仍然和第一个问题有关,尝试利用OPTIMIZER_FEATURES_ENABLE解决问题时碰到了错误。

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

 

 

正常情况下无论是在SQL提示中使用OPTIMIZER_FEATURES_ENABLE,还是设置会话或实例级的OPTIMIZER_FEATURES_ENABLE参数,同样都可以解决上一篇文章中提到的问题。

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 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 :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5        931

已用时间:  00: 01: 22.04

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 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 /*+ optimizer_features_enable('10.2.0.3') */ :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5         92

已用时间:  00: 00: 30.59

SQL> alter session set optimizer_features_enable = '10.2.0.4';

会话已更改。

已用时间:  00: 00: 00.01
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 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 :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );

         M          N     ALLCNT
---------- ---------- ----------
         2          5         92

已用时间:  00: 00: 23.26

不过如果尝试将优化器降级到11.1.0.611.1.0.7,则会导致ORA-976错误的出现:

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 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 /*+ optimizer_features_enable('11.1.0.6') */ :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
                                                                     *
28 行出现错误:
ORA-00976:
此处不允许指定的伪列或运算符。


已用时间:  00: 00: 00.01

SQL> alter session set optimizer_features_enable = '11.1.0.6';

会话已更改。

已用时间:  00: 00: 00.02
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 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 :m M, :n N, count(*) AllCnt
 25  from (
 26  select result
 27  from (
 28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
 29  from lines_result, position
 30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
 31  group by result
 32  having max(case when l is null and r is null then c end) = :m
 33  and max(case when j is null and l is null then c end) = :m
 34  and max(case when j is null and r is null then c end) = :m
 35  );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
                                                                     *
28 行出现错误:
ORA-00976:
此处不允许指定的伪列或运算符。


已用时间:  00: 00: 00.02

看来,无论是11.2还是11.1的优化器,在处理复杂的树形查询时,都存在bug。在metalink上没有找到相关的问题描述,可能这个问题还没有被发现。

 

 

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

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

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10634452