子查询解嵌套in改写为exists

原创 2013年12月04日 16:24:07
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

 


 

sql查询语句的优化,exists与in的更换

自己开发的小站,页面访问查询的速度一直不让人满意,刚好今天有时间,就决定对它优化一下。 因为在本地开时发,查询的速度是相当快的,一开始就以为是mysql版本的问题,本地是MariaDB 5.5,...
  • hj7jay
  • hj7jay
  • 2016年04月12日 09:30
  • 2369

带有exists谓词的子查询 嵌套查询

Exists,NOT EXISTS: 1.含义:带有exists谓词的子查询不返回任何实际数据,它只产生逻辑真值true或逻辑假值false。 2.查询所有选修了c1号课程的学生姓名:    s...
  • lvzhifu
  • lvzhifu
  • 2012年02月02日 21:57
  • 679

SQL关于相关子查询与EXISTS的简单总结

期末复习题里面碰到了SQL查询有关 相关子查询 的内容,经过网上各种资料各种看,下面整理于此。 我们先看下面一道题。 建表格如下: 查询:给出被所有学生借阅过的图书类别(类别,catname),...
  • yuandi_xia
  • yuandi_xia
  • 2014年12月27日 20:30
  • 2706

exists和not exists的嵌套使用及求关系的除法

(用 exists 和 not exists可以求集合的交集(intersection)和差集(except),请参考另一份如名文档) (本文中所有SQL语句均在Mysql v5.1中验证) ...
  • gerogeya
  • gerogeya
  • 2012年11月05日 00:54
  • 1577

子查询解嵌套not in 无法展开改写

SQL> explain plan for select *   from OPS$CZTEST1.SAVJ_ATOMJOURBAK  where ((list_flag = '1' and prt_...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2013年12月04日 15:07
  • 709

标量子查询SQL改写一则(包括WITH的改写)

在主查询返回大量结果集的情况下,标量子查询(Scalar Subquery)的性能是非常差的,而且即使使用并行也不行,这种情况一般需要根据SQL本身的逻辑进行改写,一般可以改写为外关联的方式。部分标量...
  • youyonghu001
  • youyonghu001
  • 2015年04月21日 15:46
  • 177

oracle 子查询解嵌套和谓词推入(sqlsubquery unnest and pre_push)

Subquery Unnesting  In subquery unnesting, the optimizer transforms a nested query into an equiva...
  • toratto
  • toratto
  • 2013年08月27日 23:32
  • 838

SQL 子查询 EXISTS 和 NOT EXISTS

MySQL EXISTS 和 NOT EXISTS 子查询语法如下: SELECT … FROM table WHERE EXISTS (subquery) 该语法可以理解为:将主查询的数据,放到子...
  • qq_27571221
  • qq_27571221
  • 2016年11月08日 23:00
  • 2643

sql server 中关于Order By 在子查询嵌套中的出错的解决方法!

一直以来,总是被困于一旦sql 语句,子查询中有order by 就出错的烦恼,没想到用这一招就解决了,无意中发现,确成了解决方法,。 解决方法是:只要在2个select 后加上一个top 10000...
  • dxnn520
  • dxnn520
  • 2012年05月12日 21:37
  • 3049

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

今天下午盖尔又发来一条坑爹的SQL,哎,最近被盖尔给咕噜惨了,老是发一些鸟SQL让我调,最近都帮忙调了N多个SQL了,还好盖尔良心发现了,说这个月末给我重200元的话费。认识盖尔的人都知道,盖尔发的S...
  • robinson1988
  • robinson1988
  • 2011年12月06日 22:58
  • 7434
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:子查询解嵌套in改写为exists
举报原因:
原因补充:

(最多只允许输入30个字)