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

最新推荐文章于 2022-10-24 21:55:25 发布

congchuang7723

最新推荐文章于 2022-10-24 21:55:25 发布

阅读量162

点赞数

文章标签：数据库

前几天因为客户数据统计错误，引起强烈反应，加班加点终于把问题解决。但是最终结果却出人意料，经排查，确定并非我们代码的问题，而是 Oracle的一个bug。bug号及描述：
6840911 Wrongs results from WINDOW NOSORT in execution plan
参考链接：

http://www.myoraclesupports.com/content/10205-patch-set-list-bug-fixes-problem-type

简单的说就是在某些情况下，执行计划会产生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
         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          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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-701661/

congchuang7723

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle分析函数多层使用时的一个bug及其解决方案

前几天因为客户数据统计错误，引起强烈反应，加班加点终于把问题解决。但是最终结果却出人意料，经排查，确定并非我们代码的问题，而是Oracle的一个bug。bug号及描述：6840911 Wrongs results fr...
复制链接

扫一扫