在10g中当主查询的谓词信息,被错误的放入子查询中,会导致子查询无法展开
explain plan for UPDATE DWF.F_PTY_INDIV O
SET END_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD')
WHERE EXISTS (SELECT 1
FROM DWF.F_PTY_INDIV F
WHERE O.PTY_ID = PTY_ID
AND O.CORP_ORG = CORP_ORG
AND O.SOURCE_CODE = 'CMS'
AND SOURCE_CODE = 'CMS'
AND F.DW_DATA_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD'))
AND O.DW_DATA_DT <> TO_DATE('2012-12-31', 'YYYY-MM-DD')
AND O.END_DT = TO_DATE('29991231', 'YYYY-MM-DD');
select * from table(dbms_xplan.display());
Plan hash value: 4112899302
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 44 | 373K (1)| 01:14:48 |
| 1 | UPDATE | F_PTY_INDIV | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | F_PTY_INDIV | 1589 | 69916 | 1149 (2)| 00:00:14 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| F_PTY_INDIV | 1 | 36 | 469 (0)| 00:00:06 |
|* 6 | INDEX SKIP SCAN | SYS_C0022051 | 1 | | 468 (0)| 00:00:06 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "DWF"."F_PTY_INDIV" "F" WHERE :B1='CMS' AND
"F"."DW_DATA_DT"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"PTY_ID"=:B2 AND "CORP_ORG"=:B3 AND "SOURCE_CODE"='CMS'))
3 - filter("O"."END_DT"=TO_DATE('29991231','YYYY-MM-DD') AND
"O"."DW_DATA_DT"<>TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
4 - filter(:B1='CMS')
5 - filter("F"."DW_DATA_DT"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
6 - access("PTY_ID"=:B1 AND "SOURCE_CODE"='CMS' AND "CORP_ORG"=:B2)
filter("PTY_ID"=:B1 AND "CORP_ORG"=:B2 AND "SOURCE_CODE"='CMS')
----------------------------------------------------------------------------------------------------------
explain plan for UPDATE DWF.F_PTY_INDIV O
SET END_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD')
WHERE O.SOURCE_CODE = 'CMS' and
EXISTS (SELECT 1
FROM DWF.F_PTY_INDIV F
WHERE O.PTY_ID = PTY_ID
AND O.CORP_ORG = CORP_ORG
/* AND O.SOURCE_CODE = 'CMS'*/
AND SOURCE_CODE = 'CMS'
AND F.DW_DATA_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD'))
AND O.DW_DATA_DT <> TO_DATE('2012-12-31', 'YYYY-MM-DD')
AND O.END_DT = TO_DATE('29991231', 'YYYY-MM-DD')
select * from table(dbms_xplan.display());
Plan hash value: 3463369635
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 80 | 2286 (2)| 00:00:28 |
| 1 | UPDATE | F_PTY_INDIV | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 80 | 2286 (2)| 00:00:28 |
|* 3 | TABLE ACCESS FULL | F_PTY_INDIV | 286 | 10296 | 1141 (2)| 00:00:14 |
|* 4 | TABLE ACCESS FULL | F_PTY_INDIV | 740 | 32560 | 1145 (2)| 00:00:14 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."PTY_ID"="PTY_ID" AND "O"."CORP_ORG"="CORP_ORG")
3 - filter("F"."DW_DATA_DT"=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "SOURCE_CODE"='CMS')
4 - filter("O"."SOURCE_CODE"='CMS' AND
"O"."END_DT"=TO_DATE('29991231','YYYY-MM-DD') AND
"O"."DW_DATA_DT"<>TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
