现在的位置: 首页 > 综合 > 正文

子查询里面有树形查询,子查询选择使用in/exists需要特别留意

2013年05月11日 ⁄ 综合 ⁄ 共 11003字 ⁄ 字号 评论关闭

今天下午盖尔又发来一条坑爹的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需要特别留意

抱歉!评论已关闭.