子查询里面有树形查询，子查询选择使用in/exists需要特别留意
来源：互联网 发布：java工程师jd 编辑：程序博客网 时间：2024/04/30 15:19
今天下午盖尔又发来一条坑爹的SQL，哎，最近被盖尔给咕噜惨了，老是发一些鸟SQL让我调，最近都帮忙调了N多个SQL了，还好盖尔良心发现了，说这个月末给我重200元的话费
。认识盖尔的人都知道，盖尔发的SQL那绝对是坑爹的，说它是OLTP的SQL不像，因为OLTP的SQL没那么复杂，说它是OLAP的也不像，因为我没看见典型的事实表，维度表的JOIN，那么唯一的解释就是他们DB设计的人是SB。
盖尔的SQL和执行计划如下：
select distinct decode(length(a.category_id), 5, decode(a.origin_type, 801, 888888, 999999), a.category_id) category_id, a.notice_code, a.treat_status, lr.real_name as receiver_name, f.send_code, f.policy_code, g.real_name agent_name, f.organ_id, f.dept_id, a.policy_id, a.change_id, a.case_id, a.group_policy_id, a.fee_id, a.auth_id, a.pay_id, cancel_appoint.appoint_time cancel_appoint_time, a.insert_time, a.send_time, a.end_time, f.agency_code, a.REPLY_TIME, a.REPLY_EMP_ID, a.FIRST_DUTY, a.NEED_SEND_PRINT, 11 source from t_policy_problem a, t_policy f, t_agent g, t_letter_receiver lr, t_problem_category pc, t_policy_cancel_appoint cancel_appoint where f.agent_id = g.agent_id(+) and a.policy_id = f.policy_id(+) and lr.main_receiver = 'Y' and a.category_id = pc.category_id and a.item_id = lr.item_id and a.policy_id = cancel_appoint.policy_id(+) And a.Item_Id = (Select Max(item_id) From t_Policy_Problem Where notice_code = a.notice_code) and a.policy_id is not null and a.notice_code is not null and a.change_id is null and a.case_id is null and a.group_policy_id is null and a.origin_type not in (801, 802) and a.pay_id is null and a.category_id not in (130103, 130104, 130102, 140102, 140101) and f.policy_type = '1' and (a.fee_id is null or (a.fee_id is not null and a.origin_type = 701)) and exists((select 1 from t_dept where f.dept_id = dept_id start with dept_id = '1020200028' connect by parent_id = prior dept_id)) and exists (select 1 from T_COMPANY_ORGAN where f.organ_id = organ_id start with organ_id = '10202' connect by parent_id = prior organ_id) and pc.NEED_PRITN = 'Y'; SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 236 | 741 (1)|| 1 | SORT UNIQUE | | 1 | 236 | 681 (0)||* 2 | FILTER | | | | || 3 | NESTED LOOPS | | 1 | 236 | 666 (1)|| 4 | NESTED LOOPS OUTER | | 1 | 219 | 665 (1)|| 5 | NESTED LOOPS | | 1 | 203 | 664 (1)|| 6 | NESTED LOOPS OUTER | | 1 | 196 | 663 (1)|| 7 | NESTED LOOPS | | 1 | 182 | 662 (1)||* 8 | TABLE ACCESS FULL | T_POLICY_PROBLEM | 1 | 107 | 660 (0)||* 9 | TABLE ACCESS BY INDEX ROWID| T_POLICY | 1 | 75 | 2 (50)||* 10 | INDEX UNIQUE SCAN | PK_T_POLICY | 1 | | 1 (0)|| 11 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CANCEL_APPOINT | 1 | 14 | 2 (50)||* 12 | INDEX UNIQUE SCAN | UK1_POLICY_CANCEL_APPOINT | 1 | | ||* 13 | TABLE ACCESS BY INDEX ROWID | T_PROBLEM_CATEGORY | 1 | 7 | 2 (50)||* 14 | INDEX UNIQUE SCAN | PK_T_PROBLEM_CATEGORY | 1 | | || 15 | TABLE ACCESS BY INDEX ROWID | T_AGENT | 1 | 16 | 2 (50)||* 16 | INDEX UNIQUE SCAN | PK_T_AGENT | 1 | | ||* 17 | INDEX RANGE SCAN | T_LETTER_RECEIVER_IDX_001 | 1 | 17 | 2 (0)|| 18 | SORT AGGREGATE | | 1 | 21 | || 19 | TABLE ACCESS BY INDEX ROWID | T_POLICY_PROBLEM | 1 | 21 | 2 (50)||* 20 | INDEX RANGE SCAN | IDX_POLICY_PROBLEM__N_CODE | 1 | | 3 (0)||* 21 | FILTER | | | | ||* 22 | CONNECT BY WITH FILTERING | | | | || 23 | NESTED LOOPS | | | | ||* 24 | INDEX UNIQUE SCAN | PK_T_DEPT | 1 | 17 | 1 (0)|| 25 | TABLE ACCESS BY USER ROWID | T_DEPT | | | || 26 | HASH JOIN | | | | || 27 | CONNECT BY PUMP | | | | || 28 | TABLE ACCESS FULL | T_DEPT | 30601 | 896K| 56 (0)||* 29 | FILTER | | | | ||* 30 | CONNECT BY WITH FILTERING | | | | || 31 | NESTED LOOPS | | | | ||* 32 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | || 33 | TABLE ACCESS BY USER ROWID | T_COMPANY_ORGAN | | | || 34 | NESTED LOOPS | | | | || 35 | BUFFER SORT | | 7 | 70 | || 36 | CONNECT BY PUMP | | | | ||* 37 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | 1 (0)|------------------------------------------------------------------------------------------------------- 2 - filter("SYS_ALIAS_1"."ITEM_ID"= (SELECT /*+ */ MAX("T_POLICY_PROBLEM"."ITEM_ID") FROM "T_POLICY_PROBLEM" "T_POLICY_PROBLEM" WHERE "T_POLICY_PROBLEM"."NOTICE_CODE"=:B1) AND EXISTS (SELECT /*+ */ 0 FROM "T_DEPT" "T_DEPT" AND ("T_DEPT"."DEPT_ID"=:B2)) AND EXISTS (SELECT /*+ */ 0 FROM "T_COMPANY_ORGAN" "T_COMPANY_ORGAN" WHERE "T_COMPANY_ORGAN"."PARENT_ID"=NULL AND ("T_COMPANY_ORGAN"."ORGAN_ID"=:B3))) 8 - filter("SYS_ALIAS_1"."POLICY_ID" IS NOT NULL AND "SYS_ALIAS_1"."NOTICE_CODE" IS NOT NULL AND "SYS_ALIAS_1"."CHANGE_ID" IS NULL AND "SYS_ALIAS_1"."CASE_ID" IS NULL AND "SYS_ALIAS_1"."GROUP_POLICY_ID" IS NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>801 AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>802 AND "SYS_ALIAS_1"."PAY_ID" IS NULL AND "SYS_ALIAS_1"."CATEGORY_ID"<>130103 AND "SYS_ALIAS_1"."CATEGORY_ID"<>130104 AND "SYS_ALIAS_1"."CATEGORY_ID"<>130102 AND "SYS_ALIAS_1"."CATEGORY_ID"<>140102 AND "SYS_ALIAS_1"."CATEGORY_ID"<>140101 AND ("SYS_ALIAS_1"."FEE_ID" IS NULL OR "SYS_ALIAS_1"."FEE_ID" IS NOT NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")=701)) 9 - filter(TO_NUMBER("SYS_ALIAS_3"."POLICY_TYPE")=1) 10 - access("SYS_ALIAS_1"."POLICY_ID"="SYS_ALIAS_3"."POLICY_ID") 12 - access("SYS_ALIAS_1"."POLICY_ID"="CANCEL_APPOINT"."POLICY_ID"(+)) 13 - filter("PC"."NEED_PRITN"='Y') 14 - access("SYS_ALIAS_1"."CATEGORY_ID"="PC"."CATEGORY_ID") filter("PC"."CATEGORY_ID"<>130103 AND "PC"."CATEGORY_ID"<>130104 AND "PC"."CATEGORY_ID"<>130102 AND "PC"."CATEGORY_ID"<>140102 AND "PC"."CATEGORY_ID"<>140101) 16 - access("SYS_ALIAS_3"."AGENT_ID"="G"."AGENT_ID"(+)) 17 - access("LR"."MAIN_RECEIVER"='Y' AND "SYS_ALIAS_1"."ITEM_ID"="LR"."ITEM_ID") 20 - access("T_POLICY_PROBLEM"."NOTICE_CODE"=:B1) 21 - filter("T_DEPT"."DEPT_ID"=:B1) 22 - filter("T_DEPT"."DEPT_ID"='1020200028') 24 - access("T_DEPT"."DEPT_ID"='1020200028') 29 - filter("T_COMPANY_ORGAN"."ORGAN_ID"=:B1) 30 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='10202') 32 - access("T_COMPANY_ORGAN"."ORGAN_ID"='10202') 37 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)77 rows selected.这个SQL里面又有 (TO_NUMBER("SYS_ALIAS_3"."POLICY_TYPE")=1) ，写SQL的那人啊 为啥又不加上'' 他们的开发人员水平真的是太那个啥了
盖尔说这个SQL跑不出结果，让他查询等待事件，是 db file sequential read
这个SQL之所以跑不出结果是因为子查询里面有 start with.... connect by ，
子查询外面使用的是exists，CBO里面有个限制：当子查询里面有 start with....connect by ， CBO不会对子查询进行转换，所以这个时候就只能走filter了
但是子查询又要返回很多结果 所以帮盖尔改写了SQL，用in 代替 exists
select distinct decode(length(a.category_id), 5, decode(a.origin_type, 801, 888888, 999999), a.category_id) category_id, a.notice_code, a.treat_status, lr.real_name as receiver_name, f.send_code, f.policy_code, g.real_name agent_name, f.organ_id, f.dept_id, a.policy_id, a.change_id, a.case_id, a.group_policy_id, a.fee_id, a.auth_id, a.pay_id, cancel_appoint.appoint_time cancel_appoint_time, a.insert_time, a.send_time, a.end_time, f.agency_code, a.REPLY_TIME, a.REPLY_EMP_ID, a.FIRST_DUTY, a.NEED_SEND_PRINT, 11 source from t_policy_problem a, t_policy f, t_agent g, t_letter_receiver lr, t_problem_category pc, t_policy_cancel_appoint cancel_appoint where f.agent_id = g.agent_id(+) and a.policy_id = f.policy_id(+) and lr.main_receiver = 'Y' and a.category_id = pc.category_id and a.item_id = lr.item_id and a.policy_id = cancel_appoint.policy_id(+) And a.Item_Id = (Select Max(item_id) From t_Policy_Problem Where notice_code = a.notice_code) and a.policy_id is not null and a.notice_code is not null and a.change_id is null and a.case_id is null and a.group_policy_id is null and a.origin_type not in (801, 802) and a.pay_id is null and a.category_id not in (130103, 130104, 130102, 140102, 140101) and f.policy_type = '1' and (a.fee_id is null or (a.fee_id is not null and a.origin_type = 701)) and f.dept_id in (select dept_id from t_dept start with dept_id = '1020200028' connect by parent_id = prior dept_id)) and f.organ_id in (select organ_id from T_COMPANY_ORGAN start with organ_id = '10202' connect by parent_id = prior organ_id) and pc.NEED_PRITN = 'Y'; ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 259 | 742 (1)|| 1 | SORT UNIQUE | | 1 | 259 | 740 (0)||* 2 | FILTER | | | | ||* 3 | HASH JOIN | | 1 | 259 | 725 (1)|| 4 | NESTED LOOPS | | 1 | 253 | 723 (1)|| 5 | NESTED LOOPS | | 1 | 236 | 722 (1)|| 6 | NESTED LOOPS OUTER | | 1 | 229 | 721 (1)|| 7 | NESTED LOOPS OUTER | | 1 | 215 | 720 (1)||* 8 | HASH JOIN | | 1 | 199 | 719 (1)|| 9 | NESTED LOOPS | | 1 | 182 | 662 (1)||* 10 | TABLE ACCESS FULL | T_POLICY_PROBLEM | 1 | 107 | 660 (0)||* 11 | TABLE ACCESS BY INDEX ROWID | T_POLICY | 1 | 75 | 2 (50)||* 12 | INDEX UNIQUE SCAN | PK_T_POLICY | 1 | | 1 (0)|| 13 | VIEW | VW_NSO_1 | 30601 | 508K| ||* 14 | CONNECT BY WITH FILTERING | | | | || 15 | NESTED LOOPS | | | | ||* 16 | INDEX UNIQUE SCAN | PK_T_DEPT | 1 | 17 | 1 (0)|| 17 | TABLE ACCESS BY USER ROWID| T_DEPT | | | || 18 | HASH JOIN | | | | || 19 | CONNECT BY PUMP | | | | || 20 | TABLE ACCESS FULL | T_DEPT | 30601 | 896K| 56 (0)|| 21 | TABLE ACCESS BY INDEX ROWID | T_AGENT | 1 | 16 | 2 (50)||* 22 | INDEX UNIQUE SCAN | PK_T_AGENT | 1 | | || 23 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CANCEL_APPOINT | 1 | 14 | 2 (50)||* 24 | INDEX UNIQUE SCAN | UK1_POLICY_CANCEL_APPOINT | 1 | | ||* 25 | TABLE ACCESS BY INDEX ROWID | T_PROBLEM_CATEGORY | 1 | 7 | 2 (50)||* 26 | INDEX UNIQUE SCAN | PK_T_PROBLEM_CATEGORY | 1 | | ||* 27 | INDEX RANGE SCAN | T_LETTER_RECEIVER_IDX_001 | 1 | 17 | 2 (0)|| 28 | VIEW | VW_NSO_2 | 7 | 42 | ||* 29 | CONNECT BY WITH FILTERING | | | | || 30 | NESTED LOOPS | | | | ||* 31 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | || 32 | TABLE ACCESS BY USER ROWID | T_COMPANY_ORGAN | | | || 33 | NESTED LOOPS | | | | || 34 | BUFFER SORT | | 7 | 70 | || 35 | CONNECT BY PUMP | | | | ||* 36 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | 1 (0)|| 37 | SORT AGGREGATE | | 1 | 21 | || 38 | TABLE ACCESS BY INDEX ROWID | T_POLICY_PROBLEM | 1 | 21 | 2 (50)||* 39 | INDEX RANGE SCAN | IDX_POLICY_PROBLEM__N_CODE | 1 | | 3 (0)|----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("SYS_ALIAS_1"."ITEM_ID"= (SELECT /*+ */ MAX("T_POLICY_PROBLEM"."ITEM_ID") FROM "T_POLICY_PROBLEM" "T_POLICY_PROBLEM" WHERE "T_POLICY_PROBLEM"."NOTICE_CODE"=:B1)) 3 - access("F"."ORGAN_ID"="VW_NSO_2"."$nso_col_1") 8 - access("F"."DEPT_ID"="VW_NSO_1"."$nso_col_1") 10 - filter("SYS_ALIAS_1"."POLICY_ID" IS NOT NULL AND "SYS_ALIAS_1"."NOTICE_CODE" IS NOT NULL AND "SYS_ALIAS_1"."CHANGE_ID" IS NULL AND "SYS_ALIAS_1"."CASE_ID" IS NULL AND "SYS_ALIAS_1"."GROUP_POLICY_ID" IS NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>801 AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>802 AND "SYS_ALIAS_1"."PAY_ID" IS NULL AND "SYS_ALIAS_1"."CATEGORY_ID"<>130103 AND "SYS_ALIAS_1"."CATEGORY_ID"<>130104 AND "SYS_ALIAS_1"."CATEGORY_ID"<>130102 AND "SYS_ALIAS_1"."CATEGORY_ID"<>140102 AND "SYS_ALIAS_1"."CATEGORY_ID"<>140101 AND ("SYS_ALIAS_1"."FEE_ID" IS NULL OR "SYS_ALIAS_1"."FEE_ID" IS NOT NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")=701)) 11 - filter("F"."POLICY_TYPE"='1') 12 - access("SYS_ALIAS_1"."POLICY_ID"="F"."POLICY_ID") 14 - filter("T_DEPT"."DEPT_ID"='1020200028') 16 - access("T_DEPT"."DEPT_ID"='1020200028') 22 - access("F"."AGENT_ID"="G"."AGENT_ID"(+)) 24 - access("SYS_ALIAS_1"."POLICY_ID"="CANCEL_APPOINT"."POLICY_ID"(+)) 25 - filter("PC"."NEED_PRITN"='Y') 26 - access("SYS_ALIAS_1"."CATEGORY_ID"="PC"."CATEGORY_ID") filter("PC"."CATEGORY_ID"<>130103 AND "PC"."CATEGORY_ID"<>130104 AND "PC"."CATEGORY_ID"<>130102 AND "PC"."CATEGORY_ID"<>140102 AND "PC"."CATEGORY_ID"<>140101) 27 - access("LR"."MAIN_RECEIVER"='Y' AND "SYS_ALIAS_1"."ITEM_ID"="LR"."ITEM_ID") 29 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='10202') 31 - access("T_COMPANY_ORGAN"."ORGAN_ID"='10202') 36 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL) 39 - access("T_POLICY_PROBLEM"."NOTICE_CODE"=:B1)75 rows selected.
SQL 改写之后，35秒就能出结果了，以前是不出结果啊！恩，这个SQL优化就到此为止吧。
我还想提的就是，他的这个SQL里面的过滤条件，
and a.policy_id is not null
and a.notice_code is not null
and a.change_id is null
and a.case_id is null
and a.group_policy_id is null
and a.origin_type not in (801, 802)
and a.pay_id is null
and a.category_id not in (130103, 130104, 130102, 140102, 140101)
is null, is not null , not in ...... 这都是什么需求啊，这个系统设计人员确实“牛逼”
这个案例恰好和 http://blog.csdn.net/robinson1988/article/details/7002545 这个案例完全相反，所以，当你遇到子查询里面有树形查询，子查询选择使用in/exists需要特别留意- 子查询里面有树形查询，子查询选择使用in/exists需要特别留意
- 嵌套子查询里面有树形查询
- 子查询(exists子查询)
- in子查询、exists子查询、连接，效率的探讨
- in子查询、exists子查询、连接，效率的探讨
- 理解exists子查询
- MSSQL EXISTS子查询
- MySQL 子查询-exists
- EXISTS子查询
- mysql 子查询in与exists互换
- 使用Hibernate Criteria实现in子查询和exists子查询
- IN&EXISTS 与 NOT IN&NOT EXISTS 子查询 区别
- mysql---where子查询、form子查询、exists子查询
- mysql之exists子查询和in查询的对比
- 子查询IN错误使用
- hive 子查询特别分析
- hive 子查询特别分析
- Hive 子查询特别分析
- 各种数据类型的大小
- 一个比较完整的连接Access数据库的DAL层代码
- struts1动态验证框架
- 原码、反码、补码
- node.js 中文编码问题
- 子查询里面有树形查询，子查询选择使用in/exists需要特别留意
- oracle 表空间的建立和角色的建立
- 如何使用AccessDataSource连接Access2007
- 我行我素shopping购物管理系统第一阶段(1)
- ！金尚网上商城 03~07、WEB项目开发的一般流程—分析与设计之业务逻辑分析设计
- 《xym生命思想公式》
- 关于A communication failure occurred while attempting to obtain an initial context 。。。的错误分析
- ATL开发一个ActiveX
- Flex Builder 4.5 正式版Myeclipse8.5插件形式安装
