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

子查詢包含where ..or在Corelated Subquery 中語句中問題

相關文章