子查詢包含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/，如需轉載，請註明出處，否則將追究法律責任。