开发谓词放错引起的尴尬
来源：互联网 发布：云计算用什么编程语言 编辑：程序博客网 时间：2024/05/01 09:17
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')----------------------------------------------------------------------------------------------------------开发居然把AND O.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'))
