10g中注意子查询里主表谓词过滤的位置
来源:互联网 发布:食用油知乎 编辑:程序博客网 时间:2024/06/06 02:35
在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'))
0 0
- 10g中注意子查询里主表谓词过滤的位置
- 带有EXISTS谓词的子查询
- 带有EXISTS谓词的子查询
- 带有EXISTS谓词的子查询
- 带有in谓词的子查询
- 带有exists谓词的子查询 嵌套查询
- sql2000嵌套查询01--带有IN谓词的子查询
- sql2000嵌套查询04--带有EXISTS谓词的子查询
- 嵌套查询 带有in谓词,比较运算符,any或all谓词的子查询
- 带有ANY(SOME)或ALL谓词的子查询
- iOS 谓词 过滤掉两个数组中相同的元素
- 子查询的使用位置
- SQL有外连接的时候注意过滤条件位置
- SQL有外连接的时候注意过滤条件位置
- NSPredicate谓词的用法 数组过滤
- iOS---NSPredicate谓词的用法 数组过滤
- NSPredicate谓词过滤的简单使用
- 使用CoreData查询数据:谓词过滤,排序操作
- ubuntu 安装。
- 传iOS 7.1将在3月15日发布:增强移动设备管理
- Oracle trim 简介
- GPS 的经纬度换算成距离的代码(转载)
- C# break ,continue, return
- 10g中注意子查询里主表谓词过滤的位置
- IT 攻城狮必备的十大算法思想
- .net中的4种事务总结
- JAVA文档注释小结
- 发通知 PendingIntent 中Intent 内容没有更新
- Web Service和WCF的到底有什么区别
- 南阳36 最长公共子序列
- KMP中next之我的个人理解
- 一个字符串的能力