由于SQL大赛题目相对比较困难,不但需要使用大量的特性,且SQL实现十分复杂,一般运行时间也会比较长,这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug。
这一篇介绍11.2上优化器处理树形查询的问题。
下面这个SQL在10.2中运行没有问题:
SQL> var m number
SQL> var n number
SQL> exec :m := 2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec :n := 5
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Elapsed: 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 );
M N ALLCNT
---------- ---------- ----------
2 5 92
Elapsed: 00:00:16.91
而在11.2中则存在问题:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
已用时间: 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 );
M N ALLCNT
---------- ---------- ----------
2 5 931
已用时间: 00: 01: 29.87
检查执行计划,发现10.2中执行计划为:
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TEMP TABLE TRANSFORMATION |
| 2 | LOAD AS SELECT |
| 3 | COUNT |
|* 4 | CONNECT BY WITHOUT FILTERING |
| 5 | FAST DUAL |
| 6 | LOAD AS SELECT |
|* 7 | VIEW |
|* 8 | FILTER |
|* 9 | CONNECT BY WITHOUT FILTERING |
| 10 | COUNT |
| 11 | VIEW |
| 12 | TABLE ACCESS FULL |
| 13 | SORT AGGREGATE |
| 14 | VIEW |
|* 15 | FILTER |
| 16 | HASH GROUP BY |
| 17 | VIEW |
| 18 | TEMP TABLE TRANSFORMATION |
| 19 | LOAD AS SELECT |
| 20 | MERGE JOIN CARTESIAN |
| 21 | MERGE JOIN CARTESIAN |
| 22 | VIEW |
|* 23 | FILTER |
|* 24 | CONNECT BY WITHOUT FILTERING |
| 25 | TABLE ACCESS FULL |
| 26 | BUFFER SORT |
| 27 | VIEW |
| 28 | COUNT |
|* 29 | CONNECT BY WITHOUT FILTERING|
| 30 | FAST DUAL |
| 31 | BUFFER SORT |
| 32 | VIEW |
| 33 | COUNT |
|* 34 | CONNECT BY WITHOUT FILTERING |
| 35 | FAST DUAL |
| 36 | LOAD AS SELECT |
| 37 | HASH GROUP BY |
| 38 | TABLE ACCESS FULL |
| 39 | LOAD AS SELECT |
| 40 | HASH GROUP BY |
| 41 | TABLE ACCESS FULL |
| 42 | LOAD AS SELECT |
| 43 | HASH GROUP BY |
| 44 | TABLE ACCESS FULL |
| 45 | VIEW |
| 46 | TABLE ACCESS FULL |
---------------------------------------------------
而11.2的执行计划为:
-------------------------------------------------------
| Id | Operation |
-------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
| 2 | VIEW |
|* 3 | FILTER |
| 4 | HASH GROUP BY |
| 5 | VIEW |
| 6 | TEMP TABLE TRANSFORMATION |
| 7 | LOAD AS SELECT |
| 8 | MERGE JOIN CARTESIAN |
| 9 | MERGE JOIN CARTESIAN |
| 10 | VIEW |
|* 11 | FILTER |
|* 12 | CONNECT BY WITHOUT FILTERING |
| 13 | VIEW |
|* 14 | FILTER |
|* 15 | CONNECT BY WITHOUT FILTERING |
| 16 | VIEW |
| 17 | COUNT |
|* 18 | CONNECT BY WITHOUT FILTERING|
| 19 | FAST DUAL |
| 20 | BUFFER SORT |
| 21 | VIEW |
| 22 | COUNT |
|* 23 | CONNECT BY WITHOUT FILTERING |
| 24 | FAST DUAL |
| 25 | BUFFER SORT |
| 26 | VIEW |
| 27 | COUNT |
|* 28 | CONNECT BY WITHOUT FILTERING |
| 29 | FAST DUAL |
| 30 | LOAD AS SELECT |
| 31 | HASH GROUP BY |
| 32 | TABLE ACCESS FULL |
| 33 | LOAD AS SELECT |
| 34 | HASH GROUP BY |
| 35 | TABLE ACCESS FULL |
| 36 | LOAD AS SELECT |
| 37 | HASH GROUP BY |
| 38 | TABLE ACCESS FULL |
| 39 | VIEW |
| 40 | TABLE ACCESS FULL |
-------------------------------------------------------
显然是11g的CBO在尝试优化树形查询的时候出现了错误,解决方法也比较简单,可以利用ROWNUM固定结果集的特性,避免11g的CBO优化执行计划:
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
已用时间: 00: 00: 00.00
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 :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: 25.12
除了利用ROWNUM之外,还可以使用OPTIMIZER_FEATURES_ENABLE提示,或者在会话级或实例级设置OPTIMIZER_FEATURES_ENABLE参数的方式,将优化器属性降低到11.1.0.7或以下的版本。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-691346/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-691346/