Oracle的又一巨型bug

关于exist中条件为true时的执行计划问题，同事发现的，还真没留意到：
SQL> with tmp as(
  2  select 1 a from dual union all
  3  select 2 a from dual union all
  4  select 3 a from dual union all
  5  select 4 a from dual),
  6  notext as(
  7  select 2 a from dual union all
  8  select 3 a from dual)
  9  select * from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a))
10  /

         A
----------
         1
         2
         3
         4
         1
         2
         3
         4

8 rows selected
执行计划（红色部分明显错了）：
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |    24 |    13   (8)| 00:00:01 |
|   1 | MERGE JOIN CARTESIAN|      |     4 |    24 |    13   (8)| 00:00:01 |
|*  2 |   VIEW               |      |     2 |     6 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE       |      |     2 |       |     4  (50)| 00:00:01 |
|   4 |     UNION-ALL        |      |       |       |            |          |
|   5 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |   BUFFER SORT        |      |     4 |    12 |    13   (8)| 00:00:01 |
|   8 |    VIEW              |      |     4 |    12 |     8   (0)| 00:00:01 |
|   9 |     UNION-ALL        |      |       |       |            |          |
|  10 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  13 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("B"."A"="B"."A")

25 rows selected

正确的结果（加了红色部分）：
with tmp as(
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual union all
select 4 a from dual),
notext as(
select 2 a from dual union all
select 3 a from dual)
select * from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a) and rownum <= 1e100)

执行计划（通过rownum约束了谓词推进，这次对了）：
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3 |    12   (0)| 00:00:01 |
|*  1 |   FILTER          |      |       |       |            |          |
|   2 |   VIEW           |      |     4 |    12 |     8   (0)| 00:00:01 |
|   3 |    UNION-ALL     |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|*  8 |   COUNT STOPKEY  |      |       |       |            |          |
|   9 |    VIEW          |      |     2 |     6 |     4   (0)| 00:00:01 |
|  10 |     UNION-ALL    |      |       |       |            |          |
|  11 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM  ( (SELECT /*+ */ 2 "A"
              FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT /*+ */ 3 "A" FROM
              "SYS"."DUAL" "DUAL")) "B" WHERE ROWNUM<=1e100))
   8 - filter(ROWNUM<=1e100)

bell6248给的另一个解决办法：
with tmp as(
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual union all
select 4 a from dual),
notext as(
select 2 a from dual union all
select 3 a from dual)
select rownum from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a) )

需要说明的是，以证明在11g里面该bug已经修复。10201bug太多了，什么时候发个心把版本升了。

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

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