关闭

子查询解嵌套in改写为exists

574人阅读 评论(0) 收藏 举报
SELECT *
  FROM (SELECT pubformdat0_.id                  id332_,
               pubformdat0_.domain_id           domain2_332_,
               pubformdat0_.process_id          process3_332_,
               pubformdat0_.entity_id           entity4_332_,
               pubformdat0_.file_type           file5_332_,
               pubformdat0_.title               title332_,
               pubformdat0_.word_no             word7_332_,
               pubformdat0_.priority            priority332_,
               pubformdat0_.secret_level        secret9_332_,
               pubformdat0_.drafter             drafter332_,
               pubformdat0_.drafter_name        drafter11_332_,
               pubformdat0_.draft_dept          draft12_332_,
               pubformdat0_.draft_deptname      draft13_332_,
               pubformdat0_.draft_date          draft14_332_,
               pubformdat0_.end_date            end15_332_,
               pubformdat0_.arch_state          arch16_332_,
               pubformdat0_.arch_fileid         arch17_332_,
               pubformdat0_.gq_templateid       gq18_332_,
               pubformdat0_.gw_templateid       gw19_332_,
               pubformdat0_.edit_flag           edit20_332_,
               pubformdat0_.delete_flag         delete21_332_,
               pubformdat0_.operator            operator332_,
               pubformdat0_.operate_date        operate23_332_,
               pubformdat0_.file_security_level file24_332_,
               pubformdat0_.yj_flag             yj25_332_,
               pubformdat0_.yj_user             yj26_332_
          FROM wf_pub_form_data pubformdat0_
         WHERE 1 = 1
           AND pubformdat0_.id IN
               (SELECT DISTINCT pubformcas1_.form_id
                  FROM wf_pub_form_case pubformcas1_
                 WHERE pubformcas1_.case_id IN
                       (SELECT to_char(caserun0_.id)
                          FROM case_run caserun0_, workitem_run workitemru1_
                         WHERE caserun0_.id = workitemru1_.caseid
                           AND caserun0_.state = 2
                           AND workitemru1_.performer IN ('300016/00415')
                           AND workitemru1_.valid_flag = '0'
                        UNION ALL
                        SELECT to_char(caserun2_.id)
                          FROM case_run         caserun2_,
                               workitem_history workitemhi3_
                         WHERE caserun2_.id = workitemhi3_.caseid
                           AND caserun2_.state = 2
                           AND workitemhi3_.performer IN ('300016/00415')
                           AND workitemhi3_.valid_flag = '0'))
         ORDER BY pubformdat0_.operate_date DESC)
 WHERE rownum <= 10;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1843931409

------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |    |    10 | 11200 |  |   230M  (1)|769:57:46 |
|*  1 |  COUNT STOPKEY        |    |  |  |  |       |   |
|   2 |   VIEW         |    |   718 |   785K|  |   230M  (1)|769:57:46 |
|*  3 |    SORT ORDER BY STOPKEY      |    |   718 |   281K|   696K|   230M  (1)|769:57:46 |
|   4 |     NESTED LOOPS       |    |   718 |   281K|  |   230M  (1)|769:57:45 |
|   5 |      VIEW        | VW_NSO_1   |   714 | 35700 |  |   230M  (1)|769:57:28 |
|   6 |       HASH UNIQUE       |    |   714 | 40698 |  |       |   |
|*  7 |        FILTER        |    |  |  |  |       |   |
|   8 |  TABLE ACCESS FULL      | WF_PUB_FORM_CASE  |   375K|    20M|  |  1817   (1)| 00:00:22 |
|   9 |  UNION-ALL       |    |  |  |  |       |   |
|  10 |   NESTED LOOPS       |    |     1 |    32 |  |   261   (0)| 00:00:04 |
|  11 |    TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN  |   132 |  3168 |  |   129   (0)| 00:00:02 |
|* 12 |     INDEX RANGE SCAN      | IDX_WORKITEM_R_13 |   132 |  |  |     3   (0)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID| CASE_RUN   |     1 |     8 |  |     1   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN      | PK_CASE_RUN  |     1 |  |  |     0   (0)| 00:00:01 |
|  15 |   NESTED LOOPS       |    |     1 |    31 |  |   368   (1)| 00:00:05 |
|  16 |    TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY  |   582 | 13386 |  |   226   (0)| 00:00:03 |
|* 17 |     INDEX RANGE SCAN      | IDX_WORKITEM_H_13 |   325 |  |  |     5   (0)| 00:00:01 |
|* 18 |    TABLE ACCESS BY INDEX ROWID| CASE_RUN   |     1 |     8 |  |     1   (0)| 00:00:01 |
|* 19 |     INDEX UNIQUE SCAN      | PK_CASE_RUN  |     1 |  |  |     0   (0)| 00:00:01 |
|  20 |      TABLE ACCESS BY INDEX ROWID     | WF_PUB_FORM_DATA  |     1 |   352 |  |     2   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN       | SYS_C0051297  |     1 |  |  |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   7 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("CASERUN0_"."ID") FROM "WORKITEM_RUN"
       "WORKITEMRU1_","CASE_RUN" "CASERUN0_" WHERE "CASERUN0_"."ID"="WORKITEMRU1_"."CASEID" AND
       "CASERUN0_"."STATE"=2 AND TO_CHAR("CASERUN0_"."ID")=:B1 AND "WORKITEMRU1_"."VALID_FLAG"='0' AND
       "WORKITEMRU1_"."PERFORMER"='300016/00415') UNION ALL  (SELECT /*+ */ TO_CHAR("CASERUN2_"."ID") FROM
       "WORKITEM_HISTORY" "WORKITEMHI3_","CASE_RUN" "CASERUN2_" WHERE "CASERUN2_"."ID"="WORKITEMHI3_"."CASEID"
       AND "CASERUN2_"."STATE"=2 AND TO_CHAR("CASERUN2_"."ID")=:B2 AND "WORKITEMHI3_"."VALID_FLAG"='0' AND
       "WORKITEMHI3_"."PERFORMER"='300016/00415')))
  12 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0')
  13 - filter("CASERUN0_"."STATE"=2)
  14 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
       filter(TO_CHAR("CASERUN0_"."ID")=:B1)
  17 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
  18 - filter("CASERUN2_"."STATE"=2)
  19 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
       filter(TO_CHAR("CASERUN2_"."ID")=:B1)
  21 - access("PUBFORMDAT0_"."ID"="$nso_col_1")

50 rows selected.


7 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("CASERUN0_"."ID") FROM "WORKITEM_RUN"
       "WORKITEMRU1_","CASE_RUN" "CASERUN0_" WHERE "CASERUN0_"."ID"="WORKITEMRU1_"."CASEID" AND
       "CASERUN0_"."STATE"=2 AND TO_CHAR("CASERUN0_"."ID")=:B1 AND "WORKITEMRU1_"."VALID_FLAG"='0' AND
       "WORKITEMRU1_"."PERFORMER"='300016/00415') UNION ALL  (SELECT /*+ */ TO_CHAR("CASERUN2_"."ID") FROM
       "WORKITEM_HISTORY" "WORKITEMHI3_","CASE_RUN" "CASERUN2_" WHERE "CASERUN2_"."ID"="WORKITEMHI3_"."CASEID"
       AND "CASERUN2_"."STATE"=2 AND TO_CHAR("CASERUN2_"."ID")=:B2 AND "WORKITEMHI3_"."VALID_FLAG"='0' AND
       "WORKITEMHI3_"."PERFORMER"='300016/00415')))

 pubformcas1_.case_id IN
                       (SELECT to_char(caserun0_.id)
                          FROM case_run caserun0_, workitem_run workitemru1_
                         WHERE caserun0_.id = workitemru1_.caseid
                           AND caserun0_.state = 2
                           AND workitemru1_.performer IN ('300016/00415')
                           AND workitemru1_.valid_flag = '0'
                        UNION ALL
                        SELECT to_char(caserun2_.id)
                          FROM case_run         caserun2_,
                               workitem_history workitemhi3_
                         WHERE caserun2_.id = workitemhi3_.caseid
                           AND caserun2_.state = 2
                           AND workitemhi3_.performer IN ('300016/00415')
                           AND workitemhi3_.valid_flag = '0'))

这里子查询解嵌套没有展开,改写如下:
  SELECT *
    FROM (SELECT  pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, 
                 pubformdat0_.process_id process3_332_, 
                 pubformdat0_.entity_id entity4_332_, 
                 pubformdat0_.file_type file5_332_, 
                 pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, 
                 pubformdat0_.priority priority332_, 
                 pubformdat0_.secret_level secret9_332_, 
                 pubformdat0_.drafter drafter332_, 
                 pubformdat0_.drafter_name drafter11_332_, 
                 pubformdat0_.draft_dept draft12_332_, 
                 pubformdat0_.draft_deptname draft13_332_, 
                 pubformdat0_.draft_date draft14_332_, 
                 pubformdat0_.end_date end15_332_, 
                 pubformdat0_.arch_state arch16_332_, 
                 pubformdat0_.arch_fileid arch17_332_, 
                 pubformdat0_.gq_templateid gq18_332_, 
                 pubformdat0_.gw_templateid gw19_332_, 
                 pubformdat0_.edit_flag edit20_332_, 
                 pubformdat0_.delete_flag delete21_332_, 
                 pubformdat0_.operator operator332_, 
                 pubformdat0_.operate_date operate23_332_, 
                 pubformdat0_.file_security_level file24_332_, 
                 pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_
              FROM wf_pub_form_data pubformdat0_
              WHERE 1 = 1
                AND pubformdat0_.id IN (SELECT  DISTINCT pubformcas1_.form_id
                         FROM wf_pub_form_case pubformcas1_
                        /* WHERE pubformcas1_.case_id IN */
                        where exists 
                         -----------------------------------------------------------------------
                        (select null from  (SELECT  to_char(caserun0_.id) as id
                                                            FROM case_run caserun0_,
                                                                 workitem_run workitemru1_
                                                            WHERE caserun0_.id 
                                                                  = 
                                                                  workitemru1_.caseid
                                                              AND caserun0_.state
                                                                  = 2
                                                              AND workitemru1_.performer
                                                                  IN (
                                                                   '300016/00415'
                                                                   )
                                                              AND workitemru1_.valid_flag
                                                                  = '0'
                                                        UNION ALL
                                                        SELECT to_char(
                                                               caserun2_.id)
                                                            FROM case_run caserun2_,
                                                                 workitem_history workitemhi3_
                                                            WHERE caserun2_.id 
                                                                  = 
                                                                  workitemhi3_.caseid
                                                              AND caserun2_.state
                                                                  = 2
                                                              AND workitemhi3_.performer
                                                                  IN (
                                                                   '300016/00415'
                                                                   )
                                                              AND workitemhi3_.valid_flag
                                                                  = '0'
                                                                  ) t
                                                                  
                                                     where    pubformcas1_.case_id =  t.id         )
                                                                  
                                                     
                                                                  )
              ORDER BY pubformdat0_.operate_date DESC)
    WHERE rownum <= 10

 


 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1381443次
    • 积分:41514
    • 等级:
    • 排名:第93名
    • 原创:2892篇
    • 转载:14篇
    • 译文:0篇
    • 评论:54条
    文章分类
    最新评论