ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分析函数多层使用时的一个bug及其解决方案

Oracle分析函数多层使用时的一个bug及其解决方案

原创 Linux操作系统 作者:regonly1 时间:2011-07-08 09:21:17 0 删除 编辑
前几天因为客户数据统计错误,引起强烈反应,加班加点终于把问题解决。但是最终结果却出人意料,经排查,确定并非我们代码的问题,而是Oracle的一个bug。bug号及描述:
6840911 Wrongs results from WINDOW NOSORT in execution plan
参考链接:

简单的说就是在某些情况下,执行计划会产生window nosort的操作,而使用该操作的分析函数可能会引起错误的结果。
该问题在10.2.0.5版本及以上已经修复。
这两天有空,正好把这个问题总结一下,也提醒看到这篇总结的人遇到此类错误时有所参考,或在编码中有意识的避开该bug。

【说明】:以下表中的C字段不是必要条件,没有该字段还是会出现此问题。

测试数据:
create table abcccc(a number, b char(1), c number);
insert into abcccc (A, B, C) values (1, 'x', 1);
insert into abcccc (A, B, C) values (1, 'x', 1);
insert into abcccc (A, B, C) values (1, 'x', 1);
insert into abcccc (A, B, C) values (1, 'y', 1);
insert into abcccc (A, B, C) values (1, 'y', 1);
insert into abcccc (A, B, C) values (1, 'z', 1);
insert into abcccc (A, B, C) values (1, 'z', 1);
insert into abcccc (A, B, C) values (1, 'z', 1);
insert into abcccc (A, B, C) values (1, 'z', 1);
commit;

查询结果:
select * from abcccc;

A    B    C
1    x    1
1    x    1
1    x    1
1    y    1
1    y    1
1    z    1
1    z    1
1    z    1
1    z    1

以下两个查询的不同在于外层的两个decode(以下红色部分),其他完全相同:
查询1:
select dense_rank() over (order by a,decode(b||'', 'X', 'Y', b), c) groupid, a, b, c, cntall
  from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
          from abcccc) t
它的外层decode是这样的:
decode(b||'', 'X', 'Y', b)

查询2:
select dense_rank() over (order by a,decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
          from abcccc) t
它的外层decode是这样的:
decode(b, 'X', 'Y', b)

从含义上来看,两者没有本质的区别,但是查询结果却是有很大的不同(注意groupid字段):
第一个查询的结果:
SQL> select dense_rank() over (order by a, decode(b||'', 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
        1          1 x          1          3
        1          1 x          1          3
        1          1 x          1          3
        2          1 y          1          2
                 1 y          1          2
        3          1 z          1          4
        3          1 z          1          4
        3          1 z          1          4
        3          1 z          1          4
 
9 rows selected

第二个查询的结果:
SQL> select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
        1          1 x          1          3
        1          1 x          1          3
        1          1 x          1          3
        1          1 y          1          2
        1          1 y          1          2
        1          1 z          1          4
        1          1 z          1          4
                 1 z          1          4
        1          1 z          1          4
 
9 rows selected

可以看到,两个的groupid差异非常大,根据语句含义,实际上要的是查询一的结果,但是查询二却出现了如此怪异的结果。
而实际上,正常的编码应该是查询二的这种方式。并非查询一的经过加工的。
尝试在内层查询使用其他分析函数 ,看是否会出现这个问题。
先用sum试一下:
SQL> select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, sum(a)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          3
         1          1 x          1          3
         1          1 x          1          3
         1          1 y          1          2
         1          1 y          1          2
         1          1 z          1          4
         1          1 z          1          4
         1          1 z          1          4
         1          1 z          1          4
 
9 rows selected
这个查询也有问题。

再用lead:
SQL> select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, lead(a)over(partition by a, decode(b, 'X', 'Y', b), c order by rownum) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          1
         1          1 x          1          1
         1          1 x          1
         1          1 y          1          1
         1          1 y          1
         1          1 z          1          1
         1          1 z          1          1
         1          1 z          1          1
         1          1 z          1
 
9 rows selected
也有问题;
从这里可以知道,任何分析函数都有可能会遇到这个问题。

不用partition by子句,改用order by子句:
SQL> select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(order by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          3
         1          1 x          1          3
         1          1 x          1          3
         1          1 y          1          5
         1          1 y          1          5
         1          1 z          1          9
         1          1 z          1          9
         1          1 z          1          9
         1          1 z          1          9
 
9 rows selected
还是有问题。
到这里,又可以知道,并非只有partition by才会出现问题,order by也有可能。

那会不会是decode的缘故引起的呢?再次验证:
SQL> select dense_rank() over (order by a, b, c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(order by a, b, c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          3
         1          1 x          1          3
         1          1 x          1          3
         2          1 y          1          5
         2          1 y          1          5
         3          1 z          1          9
         3          1 z          1          9
         3          1 z          1          9
         3          1 z          1          9
 
9 rows selected
这次分组正确了。
从这里可以知道,是因为partition by或order by列表中指定了decode函数所致。

好了,到现在为止,可以得出是decode引起的分组错误。那扩展一下,如果是其他函数会不会出现这个错误呢,这次我改用nvl:
SQL> select dense_rank() over (order by a, nvl(b,'1'), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(order by a, nvl(b,'1'), c) cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          3
         1          1 x          1          3
         1          1 x          1          3
         1          1 y          1          5
         1          1 y          1          5
         1          1 z          1          9
         1          1 z          1          9
         1          1 z          1          9
         1          1 z          1          9
 
9 rows selected
结果证明了我的猜测是对的,分析函数中,用于排序或分组的字段如果存在函数转换,就可能出现问题。



经过几次数据测试,最终可以总结出这么一个规律:
在A字段相同的前提下,如果B字段不同,而C字段相同,且内层查询中有与外层相同的parition by或order by列表,且列表中存在decode、nvl等转换的函数
就有可能出现这个问题。如果内层查询没有使用相同字段列表的分析函数,就不会出现此现象:
SQL> select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, 1 cntall
  3            from abcccc) t
  4  /
 
   GROUPID          A B          C     CNTALL
---------- ---------- - ---------- ----------
         1          1 x          1          1
         1          1 x          1          1
         1          1 x          1          1
         2          1 y          1          1
         2          1 y          1          1
         3          1 z          1          1
         3          1 z          1          1
         3          1 z          1          1
         3          1 z          1          1
 
9 rows selected

分析开始说到得两个语句的执行计划:
语句一:
SQL> explain plan for select dense_rank() over (order by a, decode(b||'', 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1435324867
-------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |     9      |   378 |    17  (12)| 00:00:01 |
|   1 |  WINDOW SORT               |                   |     9       |   378 |    17  (12)| 00:00:01 |
|   2 |   VIEW                                  |                   |     9       |   378 |    16   (7)| 00:00:01 |
|   3 |    WINDOW SORT             |                   |     9       |   261 |    16   (7)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | ABCCCC |     9       |   261 |    15   (0)| 00:00:01 |
-------------------------------------------------------------------------------

语句二:
SQL> explain plan for select dense_rank() over (order by a, decode(b, 'X', 'Y', b), c) groupid, a, b, c, cntall
  2    from (select a, b, c, count(*)over(partition by a, decode(b, 'X', 'Y', b), c) cntall
  3            from abcccc) t
  4  /
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 542054269
-------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     9      |   378 |    16   (7)| 00:00:01 |
|   1 |  WINDOW NOSORT        |                   |     9       |   378 |    16   (7)| 00:00:01 |
|   2 |   VIEW                                 |                   |     9       |   378 |    16   (7)| 00:00:01 |
|   3 |    WINDOW SORT             |                  |     9       |   261 |    16   (7)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| ABCCCC |     9       |   261 |    15   (0)| 00:00:01 |
-------------------------------------------------------------------------------

可以看到,只是一个简单的拼接,造成了两个执行计划的不同,
一个采用了window sort(正常的这个),一个采用了window nosort(错误的这个)
而查询window nosort相关的信息,发现了其对应的bug:
6840911 Wrongs results from WINDOW NOSORT in execution plan
目前只有两种办法确保不出现此类问题:
1、升级到10.2.0.5及以上版本;
2、改写语句,改写方式在我上面的实验中已经说明了。我采用的方式是这样的:
select dense_rank() over(order by a, bb, c) groupid, a, b, c, cntall
  from (select a, b, c,
               decode(b, 'X', 'Y', b) bb,
               count(*) over(partition by a, decode(b, 'X', 'Y', b), c) cntall
          from abcccc) t
也就是在内层查询中提前把字段转换好,然后在外层直接使用即可。也可以用语句一的这种方式。

【重要补充】
经过进一步实验发现,上述结论中的c字段其实不是必要条件,没有该字段的存在仍然会出现这个问题,所以结论改为:
当a字段相同,b字段不同时,且内层查询中有与外层相同的parition by或order by列表,且列表中存在decode、nvl等转换的函数,就有可能出现这个问题。这样的话,这个bug的暴露条件又降低了,加大了其出现概率。
查询如下:
SQL> select dense_rank() over(order by a, decode(b, 'X', 'Y', b)) groupid, a, b, cntall
  2    from (select a, b,
  3                 count(*) over(partition by a, decode(b, 'X', 'Y', b)) cntall
  4            from abcccc) t
  5  /

   GROUPID          A B     CNTALL
----------        ---------- - ----------
         1                    1 x          3
         1                    1 x          3
         1                    1 x          3
         1                    1 y          2
         1                    1 y          2
         1                    1 z          4
         1                    1 z          4
         1                    1 z          4
         1                    1 z          4

9 rows selected

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

请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1070064