子查询包含where ..or在Corelated Subquery 中语句中问题

       我们知道语句中where... or.....可能对执行计划造成的错误。现在我们看一下

corelated subquery 可能造成的SQL的执行计划的问题。

1、测试：
备注，本测试加载数据的多少不会对执行计划造成影响

create table t1
(
  id     NUMBER(13) not null,
  rpadding       VARCHAR2(13)
);

insert into t1 values(0,100);
insert into t1 values(0,100);
insert into t1 values(0,100);

commit;

create table t2
(
 id   number ,
 small_vc     NUMBER(15)
);

insert into t2 values(1,100);
insert into t2 values(2,100);
insert into t2 values(1,100);
insert into t2 values(1,100);

commit;

2、查看执行计划
set autotrace traceonly explain
select a.rowid ri,
            a.*
from t2 a
where a.id=1
 AND  a.small_vc IS NOT NULL
 AND NOT EXISTS
             (SELECT 1 FROM t1 
                 WHERE  (rpadding = a.small_vc  ) or ( id = a.small_vc )
                 )

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |         2 |      76 |              5   (0) | 00:00:01 |
|*  1 |  FILTER                                   |             |             |           |                          |                 |
|*  2 |   TABLE ACCESS FULL       | T2       |          3 |    114 |               2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL       | T1       |          1 |      21 |               2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
  我们看到执行计划为filter。通常子查询采用"no_unnest " hint的时候，执行计划

为filter，但缺省为unnest。为了避免可能影响，我们在子查询中加入unnest，但

执行计划未变。

如果子查询条件将where ....or改成where.... and，执行计划正常
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |           1 |      59 |             5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI            |             |           1 |      59 |             5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL |      T2   |           3 |    114 |             2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  |      T1   |           3 |      63 |             2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

目前对该问题的解决办法暂时只能重写语句
(where   a or b)  <=> (where a and b)

select a.rowid ri,
            a.*
from t2 a
where a.id=1
AND  a.small_vc IS NOT NULL
AND NOT EXISTS
                 (SELECT  1 FROM t1 
                 WHERE  rpadding = a.small_vc 
                 )
and  NOT EXISTS
                 (SELECT  1 FROM t1 
                 WHERE  id = a.small_vc 
                 )
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |           1 |      59 |           7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI              |             |           1 |      59 |           7  (15)| 00:00:01 |
|*  2 |   HASH JOIN ANTI             |             |           1 |      51 |           5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL   |     T2   |           3 |    114 |            2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   |      T1   |           3 |      39 |            2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL    |       T1   |          3 |       24 |           2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/354732/viewspace-732358/