一次300多行SQL从7分15s到24s的SQL历程

ゝ一世哀愁。 2022-09-30 03:40 159阅读 0赞

环境：阿里云 ECS 11.2.0.1 ADG Oracle

# 环境分析 #

前几天优化了一条SQL搞定以后，以为晚上日终批量报表没有问题了，然后昨天想着检查一下，看awr报告，截取晚上1点到2点的时间段，赫然映入眼帘。SQL换了，四条完全类似的SQL语句，有300多行，着实吓了一条，和开发了解到，这四条SQL其实差不多算是一条SQL，只是最后取模的的值分为1,2,3,4，所以运行了4次，可是每一次都将近400s，

![Image 1][] ![Center][]

![Image 1][]

然后就手动的执行了其中的一条语句,取得是模为2的为例进行执行如下：

select *
           from (select a.*, rownum rn
                   from (select t7.processid,
                                t7.taskname,
                                t7.operatorcode,
                                t7.orgcode,
                                t7.orgcate,
                                t7.protype,
                                t7.procode,
                                nvl(t1.prodaytotal, 0) prodaytotal,
                                nvl(t2.prodaytreat, 0) prodaytreat,
                                nvl(t3.prodayuntreat, 0) prodayuntreat,
                                nvl(t4.prodaytotalnew, 0) prodaytotalnew,
                                nvl(t5.prodaytreatnew, 0) prodaytreatnew,
                                nvl(t6.prodayuntreatnew, 0) prodayuntreatnew,
                                (select count(b.activationtime) daytasktotal
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and b.taskname = t7.taskname
                                    and trunc(b.activationtime, 'dd') = c.BUSINESSDATE) daytasktotal,
                                (select count(b.activationtime) daytasktreat
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and b.taskname = t7.taskname
                                    and trunc(b.completetime, 'dd') = c.BUSINESSDATE) daytasktreat,
                                (select count(b.activationtime) daytaskuntreat
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid 
           and a.id = b.PROCESSINFOID
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and b.taskname = t7.taskname
                                    and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                    and trunc(b.completetime, 'dd') is null) daytaskuntreat,
                                (select count(b.activationtime) daytasktotalnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.taskname = t7.taskname
                                    and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                    and b.tasktypeflag = '00') daytasktotalnew,
                                (select count(b.activationtime) daytasktreatnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.taskname = t7.taskname
                                    and trunc(b.completetime, 'dd') = c.BUSINESSDATE
                                    and b.tasktypeflag = '00') daytasktreatnew,
                                (select count(b.activationtime) daytaskuntreatnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.taskname = t7.taskname
                                    and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                    and trunc(b.completetime, 'dd') is null
                                    and b.tasktypeflag = '00') daytaskuntreatnew,
                                (select count(b.activationtime) beforetasktotal
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmsysdate         c
                                  where a.processid = t7.processid
                                    and b.operatorcode = t7.operatorcode
                                    and a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.taskname = t7.taskname
                                    and trunc(b.activationtime, 'dd') <= c.BUSINESSDATE) beforetasktotal
                           from (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.PROTYPE,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodaytotal
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmorg             c,
                                        f_smproduct          d,
                                        f_vsmsysdate         e
                                  where a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.orgcode = c.orgcode
                                    and a.productcode = d.procode
                                    and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                                    and a.processid in ('financeApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t1,
                                (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.protype,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodaytreat
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        f_vsmorg             c,
                                        f_smproduct          d,
                                        f_vsmsysdate         e
                                  where a.processid = b.processid
                                    and a.id = b.PROCESSINFOID
                                    and b.orgcode = c.orgcode
                                    and a.productcode = d.procode
                                    and trunc(b.completetime, 'dd') = e.BUSINESSDATE
                                    and a.processid in ('financeApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t2,
                                (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.protype,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodayuntreat
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK  b,
                                        f_vsmorg     c,
                                        f_smproduct  d,
                                        f_vsmsysdate e
                                  where a.processid = b.processid
                                    and b.orgcode = c.orgcode
                                    and a.productcode = d.procode
                                    and a.id = b.PROCESSINFOID
                                    and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                                    and a.processid in ('financeApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                    and b.completetime is null
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t3,
                                (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.protype,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodaytotalnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        credit.smorg         c,
                                        f_smproduct          d,
                                        f_vsmsysdate         e
                                  where a.processid = b.processid
                                    and b.orgcode = c.orgcode
                                    and a.id = b.PROCESSINFOID
                                    and a.productcode = d.procode
                                    and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                                    and a.processid in ('finan ceApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                    and b.tasktypeflag = '00'
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t4,
                                (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.protype,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodaytreatnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        credit.smorg         c,
                                        f_smproduct          d,
                                        f_vsmsysdate         e
                                  where a.processid = b.processid
                                    and b.orgcode = c.orgcode
                                    and a.productcode = d.procode
                                    and a.id = b.PROCESSINFOID
                                    and trunc(b.completetime, 'dd') = e.BUSINESSDATE
                                    and a.processid in ('financeApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                    and b.tasktypeflag = '00'
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t5,
                                (select a.processid,
                                        b.taskname,
                                        b.operatorcode,
                                        b.orgcode,
                                        c.orgcate,
                                        a.protype,
                                        a.productcode procode,
                                        COUNT(B.ACTIVATIONTIME) prodayuntreatnew
                                   from bpm.jbpm_processinfo a,
                                        VBPMEXTTASK          b,
                                        credit.smorg         c,
                                        f_smproduct          d,
                                        f_vsmsysdate         e
                                  where a.processid = b.processid
                                    and b.orgcode = c.orgcode
                                    and a.productcode = d.procode
                                    and a.id = b.PROCESSINFOID
                                    and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                                    and trunc(b.completetime, 'dd') is null
                                    and a.processid in ('financeApp',
                                                        'aheadRedemption',
                                                        'financeInno',
                                                        'financeCusMod',
                                                        'financeAccMod')
                                    and b.tasktypeflag = '00'
                                  GROUP BY a.processid,
                                           b.taskname,
                                           b.operatorcode,
                                           b.orgcode,
                                           c.orgcate,
                                           a.protype,
                                           a.productcode) t6,
                                (select br.name     processid,
                                        br.taskname,
                                        s.usercode  operatorcode,
                                        v2.PROCODE,
                                        v2.PROTYPE,
                                        fv.orgcode,
                                        fv.orgcate
                                   from bpmsteprole       br,
                                        f_vsmuser         s,
                                        f_vsmuserflowrole sr,
                                        f_vsmflowrole     r,
                                        f_vsmproduct      v2,
                                        f_smuserpro       v3,
                                        f_vsmorg          fv
                                  where s.id = sr.flowuserid
                                    and sr.flowroleid = r.id
                                    and r.flowrolecode = br.steprole
                                    and s.validflag in ('0', '1')
                                    and s.id = v3.userid
                                    and v2.id = v3.proid
                                    and s.ORGID = fv.ID
                                    AND sr.USEFLAG = '1') t7
                          where t7.processid = t1.processid(+)
                            and t7.processid = t2.processid(+)
                            and t7.processid = t3.processid(+)
                            and t7.processid = t4.processid(+)
                            and t7.processid = t5.processid(+)
                            and t7.processid = t6.processid(+)
                            and t7.taskname = t1.taskname(+)
                            and t7.taskname = t2.taskname(+)
                            and t7.taskname = t3.taskname(+)
                            and t7.taskname = t4.taskname(+)
                            and t7.taskname = t5.taskname(+)
                            and t7.taskname = t6.taskname(+)
                            and t7.operatorcode = t1.operatorcode(+)
                            and t7.operatorcode = t2.operatorcode(+)
                            and t7.operatorcode = t3.operatorcode(+)
                            and t7.operatorcode = t4.operatorcode(+)
                            and t7.operatorcode = t5.operatorcode(+)
                            and t7.operatorcode = t6.operatorcode(+)
                            and t7.orgcode = t1.orgcode(+)
                            and t7.orgcode = t2.orgcode(+)
                            and t7.orgcode = t3.orgcode(+)
                            and t7.orgcode = t4.orgcode(+)
                            and t7.orgcode = t5.orgcode(+)
                            and t7.orgcode = t6.orgcode(+)
                            and t7.orgcate = t1.orgcate(+)
                            and t7.orgcate = t2.orgcate(+)
                            and t7.orgcate = t3.orgcate(+)
                            and t7.orgcate = t4.orgcate(+)
                            and t7.orgcate = t5.orgcate(+)
                            and t7.protype = t6.protype(+)
                            and t7.protype = t1.protype(+)
                            and t7.protype = t2.protype(+)
                            and t7.protype = t3.protype(+)
                            and t7.protype = t4.protype(+)
                            and t7.protype = t5.protype(+)
                            and t7.protype = t6.protype(+)
                            and t7.procode = t1.procode(+)
                            and t7.procode = t2.procode(+)
                            and t7.procode = t3.procode(+)
                            and t7.procode = t4.procode(+)
                            and t7.procode = t5.procode(+)
                            and t7.procode = t6.procode(+)) a)
          where mod(rn, 4) = 2

执行计划如下：

36679 rows selected.
          
          Elapsed: 00:07:15.54
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2469110542
          
          -------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation     | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |      |   183K|    44M|       |   268K (1)| 00:53:39 |
          |   1 |  SORT AGGREGATE     |      |     1 |    84 |       |   |      |
          |   2 |   NESTED LOOPS     |      |       |       |       |   |      |
          |   3 |    NESTED LOOPS     |      |     1 |    84 |       |   401 (0)| 00:00:05 |
          |   4 |     NESTED LOOPS     |      |     1 |    68 |       |   399 (0)| 00:00:05 |
          |   5 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |*  6 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    60 |       |   396 (0)| 00:00:05 |
          |*  7 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |*  8 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |*  9 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  10 |  SORT AGGREGATE     |      |     1 |    95 |       |   |      |
          |  11 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  12 |    NESTED LOOPS     |      |     1 |    95 |       |   401 (0)| 00:00:05 |
          |  13 |     NESTED LOOPS     |      |     1 |    79 |       |   399 (0)| 00:00:05 |
          |  14 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 15 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    71 |       |   396 (0)| 00:00:05 |
          |* 16 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 17 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 18 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  19 |  SORT AGGREGATE     |      |     1 |    95 |       |   |      |
          |  20 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  21 |    NESTED LOOPS     |      |     1 |    95 |       |   401 (0)| 00:00:05 |
          |  22 |     NESTED LOOPS     |      |     1 |    79 |       |   399 (0)| 00:00:05 |
          |  23 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 24 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    71 |       |   396 (0)| 00:00:05 |
          |* 25 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 26 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 27 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  28 |  SORT AGGREGATE     |      |     1 |    87 |       |   |      |
          |  29 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  30 |    NESTED LOOPS     |      |     1 |    87 |       |   401 (0)| 00:00:05 |
          |  31 |     NESTED LOOPS     |      |     1 |    71 |       |   399 (0)| 00:00:05 |
          |  32 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 33 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    63 |       |   396 (0)| 00:00:05 |
          |* 34 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 35 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 36 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  37 |  SORT AGGREGATE     |      |     1 |    98 |       |   |      |
          |  38 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  39 |    NESTED LOOPS     |      |     1 |    98 |       |   401 (0)| 00:00:05 |
          |  40 |     NESTED LOOPS     |      |     1 |    82 |       |   399 (0)| 00:00:05 |
          |  41 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 42 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    74 |       |   396 (0)| 00:00:05 |
          |* 43 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 44 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 45 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  46 |  SORT AGGREGATE     |      |     1 |    98 |       |   |      |
          |  47 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  48 |    NESTED LOOPS     |      |     1 |    98 |       |   401 (0)| 00:00:05 |
          |  49 |     NESTED LOOPS     |      |     1 |    82 |       |   399 (0)| 00:00:05 |
          |  50 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 51 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    74 |       |   396 (0)| 00:00:05 |
          |* 52 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 53 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 54 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |  55 |  SORT AGGREGATE     |      |     1 |    84 |       |   |      |
          |  56 |   NESTED LOOPS     |      |       |       |       |   |      |
          |  57 |    NESTED LOOPS     |      |     1 |    84 |       |   401 (0)| 00:00:05 |
          |  58 |     NESTED LOOPS     |      |     1 |    68 |       |   399 (0)| 00:00:05 |
          |  59 |      TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |* 60 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK      |     1 |    60 |       |   396 (0)| 00:00:05 |
          |* 61 |       INDEX RANGE SCAN     | EXTTASK_OPTCODE      |   574 |       |       |     4 (0)| 00:00:01 |
          |* 62 |     INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 63 |    TABLE ACCESS BY INDEX ROWID     | JBPM_PROCESSINFO      |     1 |    16 |       |     2 (0)| 00:00:01 |
          |* 64 |  VIEW     |      |   183K|    44M|       |   268K (1)| 00:53:39 |
          |  65 |   COUNT     |      |       |       |       |   |      |
          |* 66 |    HASH JOIN RIGHT OUTER     |      |   183K|    85M|       |   268K (1)| 00:53:39 |
          |  67 |     VIEW     |      |  8227 |   570K|       | 43765 (1)| 00:08:46 |
          |  68 |      HASH GROUP BY     |      |  8227 |   923K|  1024K| 43765 (1)| 00:08:46 |
          |* 69 |       HASH JOIN     |      |  8227 |   923K|       | 43549 (1)| 00:08:43 |
          |  70 |        TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |* 71 |        HASH JOIN     |      |  8227 |   835K|       | 43519 (1)| 00:08:43 |
          |  72 | NESTED LOOPS     |      |  7915 |   278K|       | 10231 (1)| 00:02:03 |
          |  73 | MERGE JOIN CARTESIAN     |      |  9812 |   297K|       | 10230 (1)| 00:02:03 |
          |  74 |  TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |  75 |  BUFFER SORT     |      |  9812 |   220K|       | 10227 (1)| 00:02:03 |
          |* 76 |   TABLE ACCESS FULL     | JBPM_PROCESSINFO      |  9812 |   220K|       | 10227 (1)| 00:02:03 |
          |* 77 | INDEX UNIQUE SCAN     | UN_F_SMPRODUCT_PROCODE      |     1 |     5 |       |     0 (0)| 00:00:01 |
          |* 78 | TABLE ACCESS FULL     | JBPM_EXT_TASK      | 37467 |  2488K|       | 33288 (1)| 00:06:40 |
          |* 79 |     HASH JOIN RIGHT OUTER     |      |   183K|    73M|       |   224K (1)| 00:44:54 |
          |  80 |      VIEW     |      |  2214 |   153K|       | 38744 (1)| 00:07:45 |
          |  81 |       HASH GROUP BY     |      |  2214 |   265K|       | 38744 (1)| 00:07:45 |
          |  82 |        NESTED LOOPS     |      |  2214 |   265K|       | 38743 (1)| 00:07:45 |
          |  83 | NESTED LOOPS     |      |  2744 |   316K|       | 38743 (1)| 00:07:45 |
          |* 84 | HASH JOIN     |      |  2742 |   254K|       | 33257 (1)| 00:06:40 |
          |  85 |  MERGE JOIN CARTESIAN     |      |  4593 | 87267 |       |    33 (0)| 00:00:01 |
          |  86 |   TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |  87 |   BUFFER SORT     |      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |  88 |    TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |* 89 |  TABLE ACCESS FULL     | JBPM_EXT_TASK      |  2742 |   203K|       | 33224 (1)| 00:06:39 |
          |* 90 | TABLE ACCESS BY INDEX ROWID | JBPM_PROCESSINFO      |     1 |    23 |       |     2 (0)| 00:00:01 |
          |* 91 |  INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |* 92 | INDEX UNIQUE SCAN     | UN_F_SMPRODUCT_PROCODE      |     1 |     5 |       |     0 (0)| 00:00:01 |
          |* 93 |      HASH JOIN RIGHT OUTER     |      |   183K|    61M|       |   185K (1)| 00:37:09 |
          |  94 |       VIEW     |      |  2033 |   136K|       | 38370 (1)| 00:07:41 |
          |  95 |        HASH GROUP BY     |      |  2033 |   250K|       | 38370 (1)| 00:07:41 |
          |  96 | NESTED LOOPS     |      |  2033 |   250K|       | 38369 (1)| 00:07:41 |
          |  97 | NESTED LOOPS     |      |  2520 |   297K|       | 38369 (1)| 00:07:41 |
          |* 98 |  HASH JOIN     |      |  2518 |   240K|       | 33332 (1)| 00:06:40 |
          |  99 |   MERGE JOIN CARTESIAN      |      |  4593 | 87267 |       |    33 (0)| 00:00:01 |
          | 100 |    TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          | 101 |    BUFFER SORT     |      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          | 102 |     TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |*103 |   TABLE ACCESS FULL     | JBPM_EXT_TASK      |  2518 |   194K|       | 33298 (1)| 00:06:40 |
          |*104 |  TABLE ACCESS BY INDEX ROWID| JBPM_PROCESSINFO      |     1 |    23 |       |     2 (0)| 00:00:01 |
          |*105 |   INDEX UNIQUE SCAN     | SYS_C0011919      |     1 |       |       |     1 (0)| 00:00:01 |
          |*106 | INDEX UNIQUE SCAN     | UN_F_SMPRODUCT_PROCODE      |     1 |     5 |       |     0 (0)| 00:00:01 |
          |*107 |       HASH JOIN RIGHT OUTER     |      |   183K|    49M|  5048K|   147K (1)| 00:29:28 |
          | 108 |        VIEW     |      | 62231 |  4314K|       | 46637 (1)| 00:09:20 |
          | 109 | HASH GROUP BY     |      | 62231 |  7657K|  8312K| 46637 (1)| 00:09:20 |
          |*110 | HASH JOIN     |      | 62231 |  7657K|       | 44881 (1)| 00:08:59 |
          | 111 |  TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |*112 |  HASH JOIN     |      | 62231 |  6988K|       | 44850 (1)| 00:08:59 |
          | 113 |   TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |*114 |   HASH JOIN     |      | 62231 |  6502K|  2384K| 44847 (1)| 00:08:59 |
          |*115 |    HASH JOIN     |      | 60962 |  1666K|       | 10229 (1)| 00:02:03 |
          | 116 |     INDEX FULL SCAN     | UN_F_SMPRODUCT_PROCODE      |    69 |   345 |       |     1 (0)| 00:00:01 |
          |*117 |     TABLE ACCESS FULL     | JBPM_PROCESSINFO      | 75568 |  1697K|       | 10228 (1)| 00:02:03 |
          |*118 |    TABLE ACCESS FULL     | JBPM_EXT_TASK      |   281K|    21M|       | 33287 (1)| 00:06:40 |
          |*119 |        HASH JOIN RIGHT OUTER     |      |   183K|    36M|  5040K| 98514 (1)| 00:19:43 |
          | 120 | VIEW     |      | 62116 |  4306K|       | 46633 (1)| 00:09:20 |
          | 121 | HASH GROUP BY     |      | 62116 |  7461K|  8160K| 46633 (1)| 00:09:20 |
          |*122 |  HASH JOIN     |      | 62116 |  7461K|       | 44915 (1)| 00:08:59 |
          | 123 |   TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |*124 |   HASH JOIN     |      | 62116 |  6793K|       | 44884 (1)| 00:08:59 |
          | 125 |    TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |*126 |    HASH JOIN     |      | 62116 |  6308K|  2384K| 44880 (1)| 00:08:59 |
          |*127 |     HASH JOIN     |      | 60962 |  1666K|       | 10229 (1)| 00:02:03 |
          | 128 |      INDEX FULL SCAN     | UN_F_SMPRODUCT_PROCODE      |    69 |   345 |       |     1 (0)| 00:00:01 |
          |*129 |      TABLE ACCESS FULL      | JBPM_PROCESSINFO      | 75568 |  1697K|       | 10228 (1)| 00:02:03 |
          |*130 |     TABLE ACCESS FULL     | JBPM_EXT_TASK      |   306K|    22M|       | 33257 (1)| 00:06:40 |
          |*131 | HASH JOIN RIGHT OUTER     |      |   183K|    24M|  5040K| 50328 (1)| 00:10:04 |
          | 132 | VIEW     |      | 62116 |  4306K|       | 46332 (1)| 00:09:16 |
          | 133 |  HASH GROUP BY     |      | 62116 |  6793K|  7320K| 46332 (1)| 00:09:16 |
          |*134 |   HASH JOIN     |      | 62116 |  6793K|       | 44755 (1)| 00:08:58 |
          | 135 |    TABLE ACCESS FULL     | SMORG      |  4593 | 50523 |       |    30 (0)| 00:00:01 |
          |*136 |    HASH JOIN     |      | 62116 |  6126K|       | 44724 (1)| 00:08:57 |
          | 137 |     TABLE ACCESS FULL     | SMSYSDATE      |     1 |     8 |       |     3 (0)| 00:00:01 |
          |*138 |     HASH JOIN     |      | 62116 |  5641K|  2384K| 44720 (1)| 00:08:57 |
          |*139 |      HASH JOIN     |      | 60962 |  1666K|       | 10229 (1)| 00:02:03 |
          | 140 |       INDEX FULL SCAN     | UN_F_SMPRODUCT_PROCODE      |    69 |   345 |       |     1 (0)| 00:00:01 |
          |*141 |       TABLE ACCESS FULL     | JBPM_PROCESSINFO      | 75568 |  1697K|       | 10228 (1)| 00:02:03 |
          |*142 |      TABLE ACCESS FULL      | JBPM_EXT_TASK      |   306K|    18M|       | 33257 (1)| 00:06:40 |
          | 143 | VIEW     |      |   183K|    11M|       |  3056 (1)| 00:00:37 |
          |*144 |  HASH JOIN     |      |   183K|    72M|       |  3056 (1)| 00:00:37 |
          | 145 |   TABLE ACCESS FULL     | F_SMPRODUCT      |    69 |  3105 |       |     5 (0)| 00:00:01 |
          |*146 |   HASH JOIN     |      |   183K|    64M|       |  3050 (1)| 00:00:37 |
          |*147 |    HASH JOIN     |      |  6061 |  1752K|       |   583 (1)| 00:00:07 |
          | 148 |     TABLE ACCESS FULL     | SMORG      |  4593 |   215K|       |    30 (0)| 00:00:01 |
          |*149 |     HASH JOIN     |      |  6061 |  1467K|       |   553 (1)| 00:00:07 |
          |*150 |      TABLE ACCESS FULL      | SMUSER      | 12190 |  1035K|       |   445 (1)| 00:00:06 |
          |*151 |      HASH JOIN     |      |  8179 |  1285K|       |   107 (1)| 00:00:02 |
          |*152 |       HASH JOIN     |      |    18 |  1530 |       |     5  (20)| 00:00:01 |
          | 153 | TABLE ACCESS FULL    | BPMSTEPROLE      |    18 |   774 |       |     3 (0)| 00:00:01 |
          | 154 | INDEX FULL SCAN      | IDX_SMFLOWROLE_FLOWROLECODE_ID |    43 |  1806 |       |     1 (0)| 00:00:01 |
          |*155 |       TABLE ACCESS FULL     | SMUSERFLOWROLE      | 19539 |  1450K|       |   102 (0)| 00:00:02 |
          | 156 |    TABLE ACCESS FULL     | F_SMUSERPRO      |   504K|    35M|       |  2465 (1)| 00:00:30 |
          -------------------------------------------------------------------------------------------------------------------------------
          
          Statistics
          ----------------------------------------------------------
          239  recursive calls
           0  db block gets
            170466338  consistent gets
              2211311  physical reads
           0  redo size
              1731712  bytes sent via SQL*Net to client
                38104  bytes received via SQL*Net from client
                 2447  SQL*Net roundtrips to/from client
           3  sorts (memory)
           0  sorts (disk)
                36679  rows processed

分析来看，总共要得到37000条左右的数据，逻辑读1.7亿，物理读220w，3次内存排序，耗时7分15s；

从执行计划上看，SQL语句多次回表，并且其中还有一些超过400w的表，并且都是全表扫描。然后我们来分析一下SQL语句和执行计划，从执行计划看是不是觉得此起彼伏挺有规律的，然后看看眼瞎的那条300多行的SQL语句，7个标量子查询和t1到t6的6张结果集表，都有很相同的部分，7个标量子查询查的数据都是count(b.activationtime)，只不过是因为条件不同，所得到的值不同而已，t1 - t6 的6张结果集表的语句同样查询的结果集字段都是一样的，只不过也是因为条件的不同而不同罢了。

那么到这里，可以很明确的表示，这个SQL写的无比的烂，等于平白无故的多扫了多次的大表，并且，进行了冗余的hash连接，导致资源消耗巨大。

其中总结出来的，不同的条件就是：

trunc(b.activationtime, 'dd') = c.BUSINESSDATE

trunc(b.completetime, 'dd') = c.BUSINESSDATE

trunc(b.completetime, 'dd') is null

b.tasktypeflag = '00'

trunc(b.activationtime, 'dd') <= c.BUSINESSDATE

标量子查询中和t7关联的条件都是相同的：

a.processid = t7.processid

b.operatorcode = t7.operatorcode

b.taskname = t7.taskname

而t1到t6的6张表来说，都类似如下：

select a.processid,
                                         b.taskname,
                                         b.operatorcode,
                                         b.orgcode,
                                         c.orgcate,
                                         a.PROTYPE,
                                         a.productcode procode,
                                         COUNT(B.ACTIVATIONTIME) prodaytotal
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmorg             c,
                                         f_smproduct          d,
                                         f_vsmsysdate         e
                                   where a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.orgcode = c.orgcode
                                     and a.productcode = d.procode
                                     and trunc(b.activationtime, 'dd') = e.BUSINESSDATE ----条件不同导致结果集不同
                                     and a.processid in ('financeApp',
                                                         'aheadRedemption',
                                                         'financeInno',
                                                         'financeCusMod',
                                                         'financeAccMod')
                                   GROUP BY a.processid,
                                            b.taskname,
                                            b.operatorcode,
                                            b.orgcode,
                                            c.orgcate,
                                            a.protype,
                                            a.productcode

同样也是因为如上所写的不同条件的组合导致的结果集不同，进而被简单罗列成了多张表。

然后导致，最后出现了几十条外链接的条件。类似如下形式：

![Image 1][] ![Center 1][]

![Image 1][]

所以改写势在必行，也是唯一的方式。

# 第一次尝试的描述 #

所以就之前的分析思想，进行了第一次改写，但是虽然有了减少回表，减少冗余表连接的思想以及集合的思想在内，但是并没有实质性的改变，只是简单的将数据t1到t6表的集合放到了一起，然而标量子查询因为无法从一个集合衍生出如原标量查询出的结果集，导致无法修改标量子查询，所以修改后的结果收效甚微，修改如下：

with t7 as
          (select br.name     processid,
                                         br.taskname,
                                         s.usercode  operatorcode,
                                         v2.PROCODE,
                                         v2.PROTYPE,
                                         fv.orgcode,
                                         fv.orgcate
                                    from bpmsteprole       br,
                                         f_vsmuser         s,
                                         f_vsmuserflowrole sr,
                                         f_vsmflowrole     r,
                                         f_vsmproduct      v2,
                                         f_smuserpro       v3,
                                         f_vsmorg          fv
                                   where s.id = sr.flowuserid
                                     and sr.flowroleid = r.id
                                     and r.flowrolecode = br.steprole
                                     and s.validflag in ('0', '1')
                                     and s.id = v3.userid
                                     and v2.id = v3.proid
                                     and s.ORGID = fv.ID
                                     AND sr.USEFLAG = '1'),
          ta as
          (select a.processid,
                                         b.taskname,
                                         b.operatorcode,
                                         b.orgcode,
                                         c.orgcate,
                                         a.PROTYPE,
                                         a.productcode,
            e.BUSINESSDATE,
                                         B.ACTIVATIONTIME,
            b.completetime,
            b.tasktypeflag
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmorg             c,
                                         f_smproduct          d,
                                         f_vsmsysdate         e
                                   where a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.orgcode = c.orgcode
                                     and a.productcode = d.procode
                                     and a.processid in ('financeApp',
                                                         'aheadRedemption',
                                                         'financeInno',
                                                         'financeCusMod',
                                                         'financeAccMod')),
          a1 as
          (select t7.processid,
                                         t7.taskname,
                                         t7.operatorcode,
                                         t7.orgcode,
                                         t7.orgcate,
                                         t7.protype,
                                         t7.procode, 
            t1.prodaytotal,
            t2.prodaytreat,
            t3.prodayuntreat,
            t4.prodaytotalnew,
            t5.prodaytreatnew,
            t6.prodayuntreatnew
            from  (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodaytotal
                                    from ta
                                   where trunc(ta.ACTIVATIONTIME, 'fmdd') = ta.BUSINESSDATE
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t1,
                                 (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodaytreat
                                    from ta
                                   where trunc(ta.completetime, 'fmdd') = ta.BUSINESSDATE
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t2,
                                 (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodayuntreat
                                    from ta
                                   where trunc(ta.activationtime, 'fmdd') = ta.BUSINESSDATE
                                     and ta.completetime is null
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t3,
                                 (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodaytotalnew
                                    from ta
                                   where trunc(ta.activationtime, 'fmdd') = ta.BUSINESSDATE
                                     and ta.tasktypeflag = '00'
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t4,
                                 (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodaytreatnew
                                    from ta
                                   where trunc(ta.completetime, 'fmdd') = ta.BUSINESSDATE
                                     and ta.tasktypeflag = '00'
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t5,
                                 (select ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode procode,
                                         COUNT(ta.ACTIVATIONTIME) prodayuntreatnew
                                    from ta
                                   where trunc(ta.activationtime, 'fmdd') = ta.BUSINESSDATE
                                     and trunc(ta.completetime, 'fmdd') is null
                                     and ta.tasktypeflag = '00'
                                   GROUP BY ta.processid,
                                         ta.taskname,
                                         ta.operatorcode,
                                         ta.orgcode,
                                         ta.orgcate,
                                         ta.protype,
                                         ta.productcode) t6,
                                 t7
                           where t7.processid = t1.processid(+)
                             and t7.processid = t2.processid(+)
                             and t7.processid = t3.processid(+)
                             and t7.processid = t4.processid(+)
                             and t7.processid = t5.processid(+)
                             and t7.processid = t6.processid(+)
                             and t7.taskname = t1.taskname(+)
                             and t7.taskname = t2.taskname(+)
                             and t7.taskname = t3.taskname(+)
                             and t7.taskname = t4.taskname(+)
                             and t7.taskname = t5.taskname(+)
                             and t7.taskname = t6.taskname(+)
                             and t7.operatorcode = t1.operatorcode(+)
                             and t7.operatorcode = t2.operatorcode(+)
                             and t7.operatorcode = t3.operatorcode(+)
                             and t7.operatorcode = t4.operatorcode(+)
                             and t7.operatorcode = t5.operatorcode(+)
                             and t7.operatorcode = t6.operatorcode(+)
                             and t7.orgcode = t1.orgcode(+)
                             and t7.orgcode = t2.orgcode(+)
                             and t7.orgcode = t3.orgcode(+)
                             and t7.orgcode = t4.orgcode(+)
                             and t7.orgcode = t5.orgcode(+)
                             and t7.orgcode = t6.orgcode(+)
                             and t7.orgcate = t1.orgcate(+)
                             and t7.orgcate = t2.orgcate(+)
                             and t7.orgcate = t3.orgcate(+)
                             and t7.orgcate = t4.orgcate(+)
                             and t7.orgcate = t5.orgcate(+)
                             and t7.protype = t6.protype(+)
                             and t7.protype = t1.protype(+)
                             and t7.protype = t2.protype(+)
                             and t7.protype = t3.protype(+)
                             and t7.protype = t4.protype(+)
                             and t7.protype = t5.protype(+)
                             and t7.protype = t6.protype(+)
                             and t7.procode = t1.procode(+)
                             and t7.procode = t2.procode(+)
                             and t7.procode = t3.procode(+)
                             and t7.procode = t4.procode(+)
                             and t7.procode = t5.procode(+)
                             and t7.procode = t6.procode(+))
          select *
            from (select a.*, rownum rn
                    from (select processid,
                                 taskname,
                                 operatorcode,
                                 orgcode,
                                 orgcate,
                                 protype,
                                 procode,
                                 nvl(a1.prodaytotal, 0) prodaytotal,
                                 nvl(a1.prodaytreat, 0) prodaytreat,
                                 nvl(a1.prodayuntreat, 0) prodayuntreat,
                                 nvl(a1.prodaytotalnew, 0) prodaytotalnew,
                                 nvl(a1.prodaytreatnew, 0) prodaytreatnew,
                                 nvl(a1.prodayuntreatnew, 0) prodayuntreatnew,
            (select count(b.activationtime) daytasktotal
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and b.taskname = a1.taskname
                                     and trunc(b.activationtime, 'dd') = c.BUSINESSDATE) daytasktotal,
                                 (select count(b.activationtime) daytasktreat
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and b.taskname = a1.taskname
                                     and trunc(b.completetime, 'dd') = c.BUSINESSDATE) daytasktreat,
                                 (select count(b.activationtime) daytaskuntreat
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid 
            and a.id = b.PROCESSINFOID
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and b.taskname = a1.taskname
                                     and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                     and trunc(b.completetime, 'dd') is null) daytaskuntreat,
                                 (select count(b.activationtime) daytasktotalnew
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.taskname = a1.taskname
                                     and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                     and b.tasktypeflag = '00') daytasktotalnew,
                                 (select count(b.activationtime) daytasktreatnew
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.taskname = a1.taskname
                                     and trunc(b.completetime, 'dd') = c.BUSINESSDATE
                                     and b.tasktypeflag = '00') daytasktreatnew,
                                 (select count(b.activationtime) daytaskuntreatnew
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.taskname = a1.taskname
                                     and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                                     and trunc(b.completetime, 'dd') is null
                                     and b.tasktypeflag = '00') daytaskuntreatnew,
                                 (select count(b.activationtime) beforetasktotal
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = a1.processid
                                     and b.operatorcode = a1.operatorcode
                                     and a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.taskname = a1.taskname
                                     and trunc(b.activationtime, 'dd') <= c.BUSINESSDATE) beforetasktotal
                            from a1) a)
           where mod(rn, 4) = 2

执行计划如下：

36701 rows selected.
          
          Elapsed: 00:05:39.86
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3857590314
          
          ----------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation  | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |
          ----------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |   |   183K| 44M|   | 91990   (1)| 00:18:24 |
          |   1 |  SORT AGGREGATE  |   | 1 | 84 |   | |   |
          |   2 |   NESTED LOOPS  |   |   |   |   | |   |
          |   3 |    NESTED LOOPS  |   | 1 | 84 |   |   401   (0)| 00:00:05 |
          |   4 |     NESTED LOOPS  |   | 1 | 68 |   |   399   (0)| 00:00:05 |
          |   5 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |*  6 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 60 |   |   396   (0)| 00:00:05 |
          |*  7 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |*  8 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |*  9 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  10 |  SORT AGGREGATE  |   | 1 | 95 |   | |   |
          |  11 |   NESTED LOOPS  |   |   |   |   | |   |
          |  12 |    NESTED LOOPS  |   | 1 | 95 |   |   401   (0)| 00:00:05 |
          |  13 |     NESTED LOOPS  |   | 1 | 79 |   |   399   (0)| 00:00:05 |
          |  14 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 15 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 71 |   |   396   (0)| 00:00:05 |
          |* 16 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 17 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 18 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  19 |  SORT AGGREGATE  |   | 1 | 95 |   | |   |
          |  20 |   NESTED LOOPS  |   |   |   |   | |   |
          |  21 |    NESTED LOOPS  |   | 1 | 95 |   |   401   (0)| 00:00:05 |
          |  22 |     NESTED LOOPS  |   | 1 | 79 |   |   399   (0)| 00:00:05 |
          |  23 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 24 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 71 |   |   396   (0)| 00:00:05 |
          |* 25 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 26 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 27 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  28 |  SORT AGGREGATE  |   | 1 | 87 |   | |   |
          |  29 |   NESTED LOOPS  |   |   |   |   | |   |
          |  30 |    NESTED LOOPS  |   | 1 | 87 |   |   401   (0)| 00:00:05 |
          |  31 |     NESTED LOOPS  |   | 1 | 71 |   |   399   (0)| 00:00:05 |
          |  32 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 33 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 63 |   |   396   (0)| 00:00:05 |
          |* 34 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 35 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 36 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  37 |  SORT AGGREGATE  |   | 1 | 98 |   | |   |
          |  38 |   NESTED LOOPS  |   |   |   |   | |   |
          |  39 |    NESTED LOOPS  |   | 1 | 98 |   |   401   (0)| 00:00:05 |
          |  40 |     NESTED LOOPS  |   | 1 | 82 |   |   399   (0)| 00:00:05 |
          |  41 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 42 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 74 |   |   396   (0)| 00:00:05 |
          |* 43 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 44 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 45 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  46 |  SORT AGGREGATE  |   | 1 | 98 |   | |   |
          |  47 |   NESTED LOOPS  |   |   |   |   | |   |
          |  48 |    NESTED LOOPS  |   | 1 | 98 |   |   401   (0)| 00:00:05 |
          |  49 |     NESTED LOOPS  |   | 1 | 82 |   |   399   (0)| 00:00:05 |
          |  50 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 51 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 74 |   |   396   (0)| 00:00:05 |
          |* 52 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 53 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 54 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  55 |  SORT AGGREGATE  |   | 1 | 84 |   | |   |
          |  56 |   NESTED LOOPS  |   |   |   |   | |   |
          |  57 |    NESTED LOOPS  |   | 1 | 84 |   |   401   (0)| 00:00:05 |
          |  58 |     NESTED LOOPS  |   | 1 | 68 |   |   399   (0)| 00:00:05 |
          |  59 |      TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |* 60 |      TABLE ACCESS BY INDEX ROWID  | JBPM_EXT_TASK   | 1 | 60 |   |   396   (0)| 00:00:05 |
          |* 61 |       INDEX RANGE SCAN  | EXTTASK_OPTCODE   |   574 |   |   | 4   (0)| 00:00:01 |
          |* 62 |     INDEX UNIQUE SCAN  | SYS_C0011919   | 1 |   |   | 1   (0)| 00:00:01 |
          |* 63 |    TABLE ACCESS BY INDEX ROWID  | JBPM_PROCESSINFO   | 1 | 16 |   | 2   (0)| 00:00:01 |
          |  64 |  TEMP TABLE TRANSFORMATION  |   |   |   |   | |   |
          |  65 |   LOAD AS SELECT  | SYS_TEMP_0FD9D6BA5_2483BF1F    |   |   |   | |   |
          |* 66 |    HASH JOIN  |   | 62116 |  7643K|   | 44980   (1)| 00:09:00 |
          |  67 |     TABLE ACCESS FULL  | SMORG   |  4593 | 50523 |   | 30   (0)| 00:00:01 |
          |* 68 |     HASH JOIN  |   | 62116 |  6975K|  2864K| 44949   (1)| 00:09:00 |
          |* 69 |      HASH JOIN  |   | 60962 |  2143K|   | 10232   (1)| 00:02:03 |
          |  70 |       MERGE JOIN CARTESIAN  |   | 69 |   897 |   | 4   (0)| 00:00:01 |
          |  71 |        TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 |   | 3   (0)| 00:00:01 |
          |  72 |        BUFFER SORT  |   | 69 |   345 |   | 1   (0)| 00:00:01 |
          |  73 | INDEX FULL SCAN  | UN_F_SMPRODUCT_PROCODE   | 69 |   345 |   | 1   (0)| 00:00:01 |
          |* 74 |       TABLE ACCESS FULL  | JBPM_PROCESSINFO   | 75568 |  1697K|   | 10228   (1)| 00:02:03 |
          |* 75 |      TABLE ACCESS FULL  | JBPM_EXT_TASK   |   306K| 23M|   | 33257   (1)| 00:06:40 |
          |* 76 |   VIEW  |   |   183K| 44M|   | 47010   (1)| 00:09:25 |
          |  77 |    COUNT  |   |   |   |   | |   |
          |* 78 |     HASH JOIN RIGHT OUTER  |   |   183K| 85M|  5040K| 47010   (1)| 00:09:25 |
          |  79 |      VIEW  |   | 62116 |  4306K|   |  8223   (1)| 00:01:39 |
          |  80 |       HASH GROUP BY  |   | 62116 | 35M| 40M|  8223   (1)| 00:01:39 |
          |* 81 |        VIEW  |   | 62116 | 35M|   |   290   (1)| 00:00:04 |
          |  82 | TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          |* 83 |      HASH JOIN RIGHT OUTER  |   |   183K| 73M|  5040K| 34792   (1)| 00:06:58 |
          |  84 |       VIEW  |   | 62116 |  4306K|   |  8059   (1)| 00:01:37 |
          |  85 |        HASH GROUP BY  |   | 62116 | 35M| 37M|  8059   (1)| 00:01:37 |
          |* 86 | VIEW  |   | 62116 | 35M|   |   290   (1)| 00:00:04 |
          |  87 | TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          |* 88 |       HASH JOIN RIGHT OUTER  |   |   183K| 61M|  5040K| 23353   (1)| 00:04:41 |
          |  89 |        VIEW  |   | 62116 |  4306K|   |  1621   (1)| 00:00:20 |
          |  90 | HASH GROUP BY  |   | 62116 |  5641K|  7320K|  1621   (1)| 00:00:20 |
          |* 91 | VIEW  |   | 62116 |  5641K|   |   290   (1)| 00:00:04 |
          |  92 |  TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          |* 93 |        HASH JOIN RIGHT OUTER  |   |   183K| 48M|  5040K| 18966   (1)| 00:03:48 |
          |  94 | VIEW  |   | 62116 |  4306K|   |  1621   (1)| 00:00:20 |
          |  95 | HASH GROUP BY  |   | 62116 |  5641K|  7320K|  1621   (1)| 00:00:20 |
          |* 96 |  VIEW  |   | 62116 |  5641K|   |   290   (1)| 00:00:04 |
          |  97 |   TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          |* 98 | HASH JOIN RIGHT OUTER  |   |   183K| 36M|  5040K| 15194   (1)| 00:03:03 |
          |  99 | VIEW  |   | 62116 |  4306K|   |  1445   (1)| 00:00:18 |
          | 100 |  HASH GROUP BY  |   | 62116 |  4852K|  6384K|  1445   (1)| 00:00:18 |
          |*101 |   VIEW  |   | 62116 |  4852K|   |   290   (1)| 00:00:04 |
          | 102 |    TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          |*103 | HASH JOIN RIGHT OUTER  |   |   183K| 23M|  4920K| 12213   (1)| 00:02:27 |
          | 104 |  VIEW  |   | 62116 |  4185K|   |  8223   (1)| 00:01:39 |
          | 105 |   HASH GROUP BY  |   | 62116 | 35M| 40M|  8223   (1)| 00:01:39 |
          |*106 |    VIEW  |   | 62116 | 35M|   |   290   (1)| 00:00:04 |
          | 107 |     TABLE ACCESS FULL  | SYS_TEMP_0FD9D6BA5_2483BF1F    | 62116 |  5520K|   |   290   (1)| 00:00:04 |
          | 108 |  VIEW  |   |   183K| 11M|   |  3056   (1)| 00:00:37 |
          |*109 |   HASH JOIN  |   |   183K| 72M|   |  3056   (1)| 00:00:37 |
          | 110 |    TABLE ACCESS FULL  | F_SMPRODUCT   | 69 |  3105 |   | 5   (0)| 00:00:01 |
          |*111 |    HASH JOIN  |   |   183K| 64M|   |  3050   (1)| 00:00:37 |
          |*112 |     HASH JOIN  |   |  6061 |  1752K|   |   583   (1)| 00:00:07 |
          | 113 |      TABLE ACCESS FULL   | SMORG   |  4593 |   215K|   | 30   (0)| 00:00:01 |
          |*114 |      HASH JOIN  |   |  6061 |  1467K|   |   553   (1)| 00:00:07 |
          |*115 |       TABLE ACCESS FULL  | SMUSER   | 12190 |  1035K|   |   445   (1)| 00:00:06 |
          |*116 |       HASH JOIN  |   |  8179 |  1285K|   |   107   (1)| 00:00:02 |
          |*117 | HASH JOIN  |   | 18 |  1530 |   | 5  (20)| 00:00:01 |
          | 118 | TABLE ACCESS FULL| BPMSTEPROLE   | 18 |   774 |   | 3   (0)| 00:00:01 |
          | 119 | INDEX FULL SCAN  | IDX_SMFLOWROLE_FLOWROLECODE_ID | 43 |  1806 |   | 1   (0)| 00:00:01 |
          |*120 | TABLE ACCESS FULL | SMUSERFLOWROLE   | 19539 |  1450K|   |   102   (0)| 00:00:02 |
          | 121 |     TABLE ACCESS FULL  | F_SMUSERPRO   |   504K| 35M|   |  2465   (1)| 00:00:30 |
          ----------------------------------------------------------------------------------------------------------------------------
          
          Statistics
          ----------------------------------------------------------
          506  recursive calls
                 4986  db block gets
            170132907  consistent gets
               257001  physical reads
          600  redo size
              1737787  bytes sent via SQL*Net to client
                35662  bytes received via SQL*Net from client
                 2448  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
                36701  rows processed

我们看到时间从7分15s降到了5分39s，逻辑读没有什么变化依然1.7亿，物理读25w下降近10倍，内存排序从3次到1次，要说有效果也是有的，但是依然很慢啊。然后就再思考原因，其实这里的第一次修改之后好处是，将t1-t6这6张表，进行了一次合并排序，然后再根据条件的不同再组合出这6张表，这就减少了一些回表的资源消耗，但是标量那里的资源消耗依然没有减少，因为标量查询压根就没有改过。

之所以没有进行修改，之前也提到了，按照我这种集合思路来看，修改之后，好像是可以进行集合一次再根据条件的不同组合进行得到不一样的值，但是事实证明得到的结果对应列所在行的数据全部都一样，也就是说这种方式是不行的。

# 第二次修改的尝试 #

然后我就开始考虑第二种思路：

大致思路如下：

然后我们将标量的查询拿出来和t1-t6这6张表的语句拿出来看的话，发现，t1-t6的子查询当中包含有标量子查询的一些部分表和条件。而标量子查询中部分条件是和t7表做的关联，而t1,t3,t4,t6四张表其实和合为一张表，然后把不同的条件所得到的count(b.activationtime)值，都放到同一个子查询当中，作为列，类似t2，t5也是一样的效果，然后就可以把6次回表变成2次。

再来看标量子查询，其实还是一样针对trunc(b.activationtime, 'dd') <= c.BUSINESSDATE条件的不动，还是标量子查询，因为它和前边的6个标量子查询不太一样。而对于这6个标量子查询来说，还是按顺序1,3,4,6类似，2,5类似，也可以这样进行关联，同样也是为了将多次回表减少。

这里采用的技术就是，针对count来说，可以在子查询中使用decode，case when语句，来根据条件设置默认值，我这里设置条件为真为1，条件为假为0，然后sum集合，其得到的效果和count是一致的。

修改语句如下：

with temp01 as (
          select  a.processid, b.operatorcode, b.taskname, b.activationtime,b.completetime,
                                         b.orgcode,                               
                                         a.protype,
                                         a.productcode,
                                         b.tasktypeflag
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.id = b.PROCESSINFOID                           
                                     and a.processid = b.processid                           
                                     and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
          ),
          temp02 as (
          select  a.processid, b.operatorcode, b.taskname, b.activationtime,b.completetime,
                                         b.orgcode,                               
                                         a.protype,
                                         a.productcode,
                                         b.tasktypeflag
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.id = b.PROCESSINFOID                           
                                     and a.processid = b.processid                           
                                     and trunc(b.completetime, 'dd') = c.BUSINESSDATE
          )
          select * from (select a.*,rownum rn from (select t7.processid,
                                 t7.taskname,
                                 t7.operatorcode,
                                 t7.orgcode,
                                 t7.orgcate,
                                 t7.protype,
                                 t7.procode,
                                 nvl(t1.prodaytotal, 0) prodaytotal,
                                 nvl(t2.prodaytreat, 0) prodaytreat,
                                 nvl(t1.prodayuntreat, 0) prodayuntreat,
                                 nvl(t1.prodaytotalnew, 0) prodaytotalnew,
                                 nvl(t2.prodaytreatnew, 0) prodaytreatnew,
                                 nvl(t1.prodayuntreatnew, 0) prodayuntreatnew,
                                  nvl(t8.daytasktotal,0) daytasktotal,
                                  nvl(t9.daytasktreat,0) daytasktreat,
                                  nvl(t8.daytaskuntreat,0) daytaskuntreat,
                                 nvl(t8.daytasktotalnew,0) daytasktotalnew,
                                 nvl(t9.daytasktreatnew,0) daytasktreatnew,
                                 nvl(t8.daytaskuntreatnew,0) daytaskuntreatnew,
                                 (select count(b.activationtime) beforetasktotal
                                    from bpm.jbpm_processinfo a,
                                         VBPMEXTTASK          b,
                                         f_vsmsysdate         c
                                   where a.processid = t7.processid
                                     and b.operatorcode = t7.operatorcode
                                     and a.processid = b.processid
                                     and a.id = b.PROCESSINFOID
                                     and b.taskname = t7.taskname
                                     and trunc(b.activationtime, 'dd') <= c.BUSINESSDATE) beforetasktotal
                            from (select     
          temp.processid,
                                         temp.taskname,
                                         temp.operatorcode,
                                         temp.orgcode,
                                         temp.orgcate,
                                         temp.PROTYPE,
                                         temp.productcode procode,
                                         sum(prodaytotal) prodaytotal,
                                         sum(prodayuntreat) prodayuntreat, 
                                         sum(prodaytotalnew) prodaytotalnew,
                                         sum(prodayuntreatnew) prodayuntreatnew
                                         from
                      (select temp01.processid,
                                         temp01.taskname,
                                         temp01.operatorcode,
                                         temp01.orgcode,
                                         c.orgcate,
                                         temp01.PROTYPE,
                                         temp01.productcode,
                                         1 prodaytotal,
                                         decode(temp01.completetime,null,1,0) prodayuntreat,
                                         decode(temp01.tasktypeflag,'00',1,0) prodaytotalnew,
                                         case when temp01.completetime is null and temp01.tasktypeflag = '00' then 1 else 0 end prodayuntreatnew
                                    from temp01,
                                         f_vsmorg             c,
                                         f_smproduct          d                               
                                   where 
                                      temp01.orgcode = c.orgcode
                                     and temp01.productcode = d.procode                           
                                     and temp01.processid in ('financeApp',
                                                         'aheadRedemption',
                                                         'financeInno',
                                                         'financeCusMod',
                                                         'financeAccMod')
                                                         ) temp
                                   GROUP BY temp.processid,
                                            temp.taskname,
                                            temp.operatorcode,
                                            temp.orgcode,
                                            temp.orgcate,
                                            temp.protype,
                                            temp.productcode) t1,
                                 (select temp.processid,
                                         temp.taskname,
                                         temp.operatorcode,
                                         temp.orgcode,
                                         temp.orgcate,
                                         temp.protype,
                                         temp.productcode procode,
                                         sum(prodaytreat) prodaytreat,
                                         sum(prodaytreatnew) prodaytreatnew
                                         from (
          select temp02.processid,
                                         temp02.taskname,
                                         temp02.operatorcode,
                                         temp02.orgcode,
                                         c.orgcate,
                                         temp02.protype,
                                         temp02.productcode,
                                         1 prodaytreat,
                                         decode(temp02.tasktypeflag,'00',1,0) prodaytreatnew
                                    from temp02,
                                         f_vsmorg             c,
                                         f_smproduct          d                               
                                   where temp02.orgcode = c.orgcode
                                     and temp02.productcode = d.procode                           
                                     and temp02.processid in ('financeApp',
                                                         'aheadRedemption',
                                                         'financeInno',
                                                         'financeCusMod',
                                                         'financeAccMod')
                                                         ) temp
                                   GROUP BY temp.processid,
                                            temp.taskname,
                                            temp.operatorcode,
                                            temp.orgcode,
                                            temp.orgcate,
                                            temp.protype,
                                            temp.productcode) t2,                       
                                 (select br.name     processid,
                                         br.taskname,
                                         s.usercode  operatorcode,
                                         v2.PROCODE,
                                         v2.PROTYPE,
                                         fv.orgcode,
                                         fv.orgcate
                                    from bpmsteprole       br,
                                         f_vsmuser         s,
                                         f_vsmuserflowrole sr,
                                         f_vsmflowrole     r,
                                         f_vsmproduct      v2,
                                         f_smuserpro       v3,
                                         f_vsmorg          fv
                                   where s.id = sr.flowuserid
                                     and sr.flowroleid = r.id
                                     and r.flowrolecode = br.steprole
                                     and s.validflag in ('0', '1')
                                     and s.id = v3.userid
                                     and v2.id = v3.proid
                                     and s.ORGID = fv.ID
                                     AND sr.USEFLAG = '1') t7,
                                     (select processid,
                 operatorcode,
                 taskname,
                 sum(col1) daytasktotal,
                 sum(col2) daytaskuntreat,
                 sum(col3) daytasktotalnew,
                 sum(col4) daytaskuntreatnew
            from (select processid,
                         operatorcode,
                         taskname,
                         1 col1,
                         decode(temp01.completetime, null, 1, 0) col2,
                         decode(temp01.tasktypeflag, '00', 1, 0) col3,
                         case
                           when temp01.completetime is null and
                                temp01.tasktypeflag = '00' then
                            1
                           else
                            0
                         end col4
                    from temp01)
           group by processid, operatorcode, taskname
                                     ) t8,
                                     (select processid,
                 operatorcode,
                 taskname,
                 sum(col1) daytasktreat,
                 sum(col2) daytasktreatnew
            from (select processid,
                         operatorcode,
                         taskname,
                         1 col1,
                         decode(tasktypeflag, '00', 1, 0) col2
                    from temp02)
           group by processid, operatorcode, taskname
                                     ) t9                           
                           where t7.processid = t1.processid(+)
                             and t7.processid = t2.processid(+)                   
                             and t7.processid = t8.processid(+)
                             and t7.processid = t9.processid(+)                   
                             and t7.taskname = t1.taskname(+)
                             and t7.taskname = t2.taskname(+)                   
                             and t7.taskname = t8.taskname(+)
                             and t7.taskname = t9.taskname(+)                   
                             and t7.operatorcode = t1.operatorcode(+)
                             and t7.operatorcode = t2.operatorcode(+)                   
                             and t7.operatorcode = t8.operatorcode(+)
                             and t7.operatorcode = t9.operatorcode(+)                   
                             and t7.orgcode = t1.orgcode(+)
                             and t7.orgcode = t2.orgcode(+)                   
                             and t7.orgcate = t1.orgcate(+)
                             and t7.orgcate = t2.orgcate(+)                   
                             and t7.protype = t1.protype(+)
                             and t7.protype = t2.protype(+)                   
                             and t7.procode = t1.procode(+)
                             and t7.procode = t2.procode(+)) a)
           where mod(rn, 4) = 2

执行计划如下：

36701 rows selected.
          
          Elapsed: 00:01:44.81
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1691032574
          
          --------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time |
          --------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT | |   183K|    44M| |  1738K  (1)| 05:47:42 |
          |   1 |  SORT AGGREGATE | |     1 |    84 | |      | |
          |   2 |   NESTED LOOPS | | | | |      | |
          |   3 |    NESTED LOOPS | |     1 |    84 | |   401   (0)| 00:00:05 |
          |   4 |     NESTED LOOPS | |     1 |    68 | |   399   (0)| 00:00:05 |
          |   5 |      TABLE ACCESS FULL | SMSYSDATE |     1 |     8 | |     3   (0)| 00:00:01 |
          |*  6 |      TABLE ACCESS BY INDEX ROWID| JBPM_EXT_TASK |     1 |    60 | |   396   (0)| 00:00:05 |
          |*  7 |       INDEX RANGE SCAN | EXTTASK_OPTCODE |   574 | | |     4   (0)| 00:00:01 |
          |*  8 |     INDEX UNIQUE SCAN | SYS_C0011919 |     1 | | |     1   (0)| 00:00:01 |
          |*  9 |    TABLE ACCESS BY INDEX ROWID | JBPM_PROCESSINFO |     1 |    16 | |     2   (0)| 00:00:01 |
          |  10 |  TEMP TABLE TRANSFORMATION | | | | |      | |
          |  11 |   LOAD AS SELECT | SYS_TEMP_0FD9D6BD2_2483BF1F | | | |      | |
          |* 12 |    HASH JOIN | |  1692K|   177M| | 62578   (1)| 00:12:31 |
          |  13 |     TABLE ACCESS FULL | SMSYSDATE |     1 |     8 | |     3   (0)| 00:00:01 |
          |* 14 |     HASH JOIN | |  1692K|   164M|    30M| 62569   (1)| 00:12:31 |
          |  15 |      TABLE ACCESS FULL | JBPM_PROCESSINFO |   924K|    20M| | 10228   (1)| 00:02:03 |
          |* 16 |      TABLE ACCESS FULL | JBPM_EXT_TASK |  4074K|   306M| | 33254   (1)| 00:06:40 |
          |  17 |   LOAD AS SELECT | SYS_TEMP_0FD9D6BD3_2483BF1F | | | |      | |
          |* 18 |    HASH JOIN | |  1692K|   177M| | 62578   (1)| 00:12:31 |
          |  19 |     TABLE ACCESS FULL | SMSYSDATE |     1 |     8 | |     3   (0)| 00:00:01 |
          |* 20 |     HASH JOIN | |  1692K|   164M|    30M| 62569   (1)| 00:12:31 |
          |  21 |      TABLE ACCESS FULL | JBPM_PROCESSINFO |   924K|    20M| | 10228   (1)| 00:02:03 |
          |* 22 |      TABLE ACCESS FULL | JBPM_EXT_TASK |  4074K|   306M| | 33254   (1)| 00:06:40 |
          |* 23 |   VIEW | |   183K|    44M| |  1613K  (1)| 05:22:40 |
          |  24 |    COUNT | | | | |      | |
          |* 25 |     HASH JOIN OUTER | |   183K|    74M|    57M|  1613K  (1)| 05:22:40 |
          |* 26 |      HASH JOIN OUTER | |   183K|    55M|    40M|  1015K  (1)| 03:23:09 |
          |* 27 |       HASH JOIN OUTER | |   183K|    38M|    26M|   794K  (1)| 02:38:58 |
          |* 28 |        HASH JOIN OUTER | |   183K|    24M|    13M|   216K  (1)| 00:43:13 |
          |  29 | VIEW | |   183K|    11M| |  3056   (1)| 00:00:37 |
          |* 30 | HASH JOIN | |   183K|    72M| |  3056   (1)| 00:00:37 |
          |  31 |  TABLE ACCESS FULL | F_SMPRODUCT |    69 |  3105 | |     5   (0)| 00:00:01 |
          |* 32 |  HASH JOIN | |   183K|    64M| |  3050   (1)| 00:00:37 |
          |* 33 |   HASH JOIN | |  6061 |  1752K| |   583   (1)| 00:00:07 |
          |  34 |    TABLE ACCESS FULL | SMORG |  4593 |   215K| |    30   (0)| 00:00:01 |
          |* 35 |    HASH JOIN | |  6061 |  1467K| |   553   (1)| 00:00:07 |
          |* 36 |     TABLE ACCESS FULL | SMUSER | 12190 |  1035K| |   445   (1)| 00:00:06 |
          |* 37 |     HASH JOIN | |  8179 |  1285K| |   107   (1)| 00:00:02 |
          |* 38 |      HASH JOIN | |    18 |  1530 | |     5  (20)| 00:00:01 |
          |  39 |       TABLE ACCESS FULL| BPMSTEPROLE |    18 |   774 | |     3   (0)| 00:00:01 |
          |  40 |       INDEX FULL SCAN | IDX_SMFLOWROLE_FLOWROLECODE_ID |    43 |  1806 | |     1   (0)| 00:00:01 |
          |* 41 |      TABLE ACCESS FULL | SMUSERFLOWROLE | 19539 |  1450K| |   102   (0)| 00:00:02 |
          |  42 |   TABLE ACCESS FULL | F_SMUSERPRO |   504K|    35M| |  2465   (1)| 00:00:30 |
          |  43 | VIEW | |  1692K|   112M| |   205K  (1)| 00:41:09 |
          |  44 | HASH GROUP BY | |  1692K|   897M|   944M|   205K  (1)| 00:41:09 |
          |  45 |  VIEW | |  1692K|   897M| |  6863   (1)| 00:01:23 |
          |  46 |   TABLE ACCESS FULL | SYS_TEMP_0FD9D6BD3_2483BF1F |  1692K|   130M| |  6863   (1)| 00:01:23 |
          |  47 |        VIEW | |  4461K|   357M| |   557K  (1)| 01:51:27 |
          |  48 | HASH GROUP BY | |  4461K|  2484M|  2681M|   557K  (1)| 01:51:27 |
          |* 49 | HASH JOIN | |  4461K|  2484M| |  6914   (1)| 00:01:23 |
          |  50 |  TABLE ACCESS FULL | SMORG |  4593 | 50523 | |    30   (0)| 00:00:01 |
          |* 51 |  HASH JOIN | |  1373K|   750M| |  6870   (1)| 00:01:23 |
          |  52 |   INDEX FULL SCAN | UN_F_SMPRODUCT_PROCODE |    69 |   345 | |     1   (0)| 00:00:01 |
          |* 53 |   VIEW | |  1692K|   916M| |  6863   (1)| 00:01:23 |
          |  54 |    TABLE ACCESS FULL | SYS_TEMP_0FD9D6BD3_2483BF1F |  1692K|   130M| |  6863   (1)| 00:01:23 |
          |  55 |       VIEW | |  1692K|   154M| |   210K  (1)| 00:42:03 |
          |  56 |        HASH GROUP BY | |  1692K|   918M|  1017M|   210K  (1)| 00:42:03 |
          |  57 | VIEW | |  1692K|   918M| |  6863   (1)| 00:01:23 |
          |  58 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BD2_2483BF1F |  1692K|   130M| |  6863   (1)| 00:01:23 |
          |  59 |      VIEW | |  4461K|   468M| |   568K  (1)| 01:53:48 |
          |  60 |       HASH GROUP BY | |  4461K|  2540M|  2681M|   568K  (1)| 01:53:48 |
          |* 61 |        HASH JOIN | |  4461K|  2540M| |  6914   (1)| 00:01:23 |
          |  62 | TABLE ACCESS FULL | SMORG |  4593 | 50523 | |    30   (0)| 00:00:01 |
          |* 63 | HASH JOIN | |  1373K|   767M| |  6870   (1)| 00:01:23 |
          |  64 | INDEX FULL SCAN | UN_F_SMPRODUCT_PROCODE |    69 |   345 | |     1   (0)| 00:00:01 |
          |* 65 | VIEW | |  1692K|   937M| |  6863   (1)| 00:01:23 |
          |  66 |  TABLE ACCESS FULL | SYS_TEMP_0FD9D6BD2_2483BF1F |  1692K|   130M| |  6863   (1)| 00:01:23 |
          --------------------------------------------------------------------------------------------------------------------------
          
          Statistics
          ----------------------------------------------------------
          629  recursive calls
          231  db block gets
             39551721  consistent gets
               365639  physical reads
                 1288  redo size
              2914057  bytes sent via SQL*Net to client
                30160  bytes received via SQL*Net from client
                 2448  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
                36701  rows processed

查看执行计划，时间从5分39秒到了1分44s，标量子查询的此起彼伏的规律消失，逻辑读从1.7亿降到4000w，但是物理读变成了36w，内存排序从1次变为0次。

结果来看改进不小，讲道理，查4w条数据，不到2分钟，还可以了。但是还是发现，这里还有一个标量子查询，如果也同样像前边的标量子查询提取出来呢，会是什么效果呢，原以为确实应该变化不会很大的，但是还是想尝试一下。

# 第三次改写尝试 #

将trunc(b.activationtime, 'dd') <= c.BUSINESSDATE条件的标量子查询单独提取出来，然后用t7再关联了一张表，外链接也同样关联出相关的外链接条件，语句如下：

从340行愣是减少到了200行。

执行计划：

36691 rows selected.
          
          Elapsed: 00:00:24.80
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 680590905
          
          ---------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
          ---------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |  |   183K|    44M|  |  1778K  (1)| 05:55:48 |
          |   1 |  TEMP TABLE TRANSFORMATION |  |  |  |  |       |  |
          |   2 |   LOAD AS SELECT | SYS_TEMP_0FD9D6D19_2483BF1F  |  |  |  |       |  |
          |*  3 |    HASH JOIN |  |  1695K|   177M|  | 62630   (1)| 00:12:32 |
          |   4 |     TABLE ACCESS FULL | SMSYSDATE  | 1 | 8 |  | 3   (0)| 00:00:01 |
          |*  5 |     HASH JOIN |  |  1695K|   164M|    30M| 62621   (1)| 00:12:32 |
          |   6 |      TABLE ACCESS FULL | JBPM_PROCESSINFO  |   924K|    20M|  | 10228   (1)| 00:02:03 |
          |*  7 |      TABLE ACCESS FULL | JBPM_EXT_TASK  |  4086K|   307M|  | 33254   (1)| 00:06:40 |
          |   8 |   LOAD AS SELECT | SYS_TEMP_0FD9D6D1A_2483BF1F  |  |  |  |       |  |
          |*  9 |    HASH JOIN |  |  1695K|   177M|  | 62630   (1)| 00:12:32 |
          |  10 |     TABLE ACCESS FULL | SMSYSDATE  | 1 | 8 |  | 3   (0)| 00:00:01 |
          |* 11 |     HASH JOIN |  |  1695K|   164M|    30M| 62621   (1)| 00:12:32 |
          |  12 |      TABLE ACCESS FULL | JBPM_PROCESSINFO  |   924K|    20M|  | 10228   (1)| 00:02:03 |
          |* 13 |      TABLE ACCESS FULL | JBPM_EXT_TASK  |  4086K|   307M|  | 33254   (1)| 00:06:40 |
          |* 14 |   VIEW |  |   183K|    44M|  |  1653K  (1)| 05:30:44 |
          |  15 |    COUNT |  |  |  |  |       |  |
          |* 16 |     HASH JOIN OUTER |  |   183K|    84M|    67M|  1653K  (1)| 05:30:44 |
          |* 17 |      HASH JOIN OUTER |  |   183K|    65M|    50M|  1060K  (1)| 03:32:12 |
          |* 18 |       HASH JOIN OUTER |  |   183K|    48M|    36M|   839K  (1)| 02:47:50 |
          |* 19 |        HASH JOIN OUTER |  |   183K|    34M|    23M|   265K  (1)| 00:53:01 |
          |* 20 | HASH JOIN RIGHT OUTER |  |   183K|    21M|  5720K| 51166   (1)| 00:10:14 |
          |  21 | VIEW |  | 84797 |  4720K|  | 47137   (1)| 00:09:26 |
          |  22 |  HASH GROUP BY |  | 84797 |  6956K|  7720K| 47137   (1)| 00:09:26 |
          |* 23 |   HASH JOIN |  | 84797 |  6956K|    15M| 45481   (1)| 00:09:06 |
          |  24 |    NESTED LOOPS |  |   204K|    13M|  | 33257   (1)| 00:06:40 |
          |  25 |     TABLE ACCESS FULL | SMSYSDATE  | 1 | 8 |  | 3   (0)| 00:00:01 |
          |* 26 |     TABLE ACCESS FULL | JBPM_EXT_TASK  |   204K|    11M|  | 33254   (1)| 00:06:40 |
          |  27 |    TABLE ACCESS FULL | JBPM_PROCESSINFO  |   924K|    14M|  | 10220   (1)| 00:02:03 |
          |  28 | VIEW |  |   183K|    11M|  |  3056   (1)| 00:00:37 |
          |* 29 |  HASH JOIN |  |   183K|    72M|  |  3056   (1)| 00:00:37 |
          |  30 |   TABLE ACCESS FULL | F_SMPRODUCT  |    69 |  3105 |  | 5   (0)| 00:00:01 |
          |* 31 |   HASH JOIN |  |   183K|    64M|  |  3050   (1)| 00:00:37 |
          |* 32 |    HASH JOIN |  |  6061 |  1752K|  |   583   (1)| 00:00:07 |
          |  33 |     TABLE ACCESS FULL | SMORG  |  4593 |   215K|  |    30   (0)| 00:00:01 |
          |* 34 |     HASH JOIN |  |  6061 |  1467K|  |   553   (1)| 00:00:07 |
          |* 35 |      TABLE ACCESS FULL  | SMUSER  | 12190 |  1035K|  |   445   (1)| 00:00:06 |
          |* 36 |      HASH JOIN |  |  8179 |  1285K|  |   107   (1)| 00:00:02 |
          |* 37 |       HASH JOIN |  |    18 |  1530 |  | 5  (20)| 00:00:01 |
          |  38 | TABLE ACCESS FULL| BPMSTEPROLE  |    18 |   774 |  | 3   (0)| 00:00:01 |
          |  39 | INDEX FULL SCAN  | IDX_SMFLOWROLE_FLOWROLECODE_ID |    43 |  1806 |  | 1   (0)| 00:00:01 |
          |* 40 |       TABLE ACCESS FULL | SMUSERFLOWROLE  | 19539 |  1450K|  |   102   (0)| 00:00:02 |
          |  41 |    TABLE ACCESS FULL | F_SMUSERPRO  |   504K|    35M|  |  2465   (1)| 00:00:30 |
          |  42 | VIEW |  |  1695K|   113M|  |   206K  (1)| 00:41:14 |
          |  43 | HASH GROUP BY |  |  1695K|   899M|   946M|   206K  (1)| 00:41:14 |
          |  44 |  VIEW |  |  1695K|   899M|  |  6876   (1)| 00:01:23 |
          |  45 |   TABLE ACCESS FULL | SYS_TEMP_0FD9D6D1A_2483BF1F  |  1695K|   113M|  |  6876   (1)| 00:01:23 |
          |  46 |        VIEW |  |  4420K|   354M|  |   552K  (1)| 01:50:27 |
          |  47 | HASH GROUP BY |  |  4420K|  2461M|  2656M|   552K  (1)| 01:50:27 |
          |* 48 | HASH JOIN |  |  4420K|  2461M|  |  6927   (1)| 00:01:24 |
          |  49 |  TABLE ACCESS FULL | SMORG  |  4593 | 50523 |  |    30   (0)| 00:00:01 |
          |* 50 |  HASH JOIN |  |  1376K|   752M|  |  6883   (1)| 00:01:23 |
          |  51 |   INDEX FULL SCAN | UN_F_SMPRODUCT_PROCODE  |    69 |   345 |  | 1   (0)| 00:00:01 |
          |* 52 |   VIEW |  |  1695K|   918M|  |  6876   (1)| 00:01:23 |
          |  53 |    TABLE ACCESS FULL | SYS_TEMP_0FD9D6D1A_2483BF1F  |  1695K|   113M|  |  6876   (1)| 00:01:23 |
          |  54 |       VIEW |  |  1695K|   155M|  |   210K  (1)| 00:42:08 |
          |  55 |        HASH GROUP BY |  |  1695K|   920M|  1019M|   210K  (1)| 00:42:08 |
          |  56 | VIEW |  |  1695K|   920M|  |  6877   (1)| 00:01:23 |
          |  57 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D19_2483BF1F  |  1695K|   131M|  |  6877   (1)| 00:01:23 |
          |  58 |      VIEW |  |  4420K|   463M|  |   563K  (1)| 01:52:47 |
          |  59 |       HASH GROUP BY |  |  4420K|  2516M|  2656M|   563K  (1)| 01:52:47 |
          |* 60 |        HASH JOIN |  |  4420K|  2516M|  |  6928   (1)| 00:01:24 |
          |  61 | TABLE ACCESS FULL | SMORG  |  4593 | 50523 |  |    30   (0)| 00:00:01 |
          |* 62 | HASH JOIN |  |  1376K|   769M|  |  6884   (1)| 00:01:23 |
          |  63 | INDEX FULL SCAN | UN_F_SMPRODUCT_PROCODE  |    69 |   345 |  | 1   (0)| 00:00:01 |
          |* 64 | VIEW |  |  1695K|   939M|  |  6877   (1)| 00:01:23 |
          |  65 |  TABLE ACCESS FULL | SYS_TEMP_0FD9D6D19_2483BF1F  |  1695K|   131M|  |  6877   (1)| 00:01:23 |
          ---------------------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             3 - access("BUSINESSDATE"=TRUNC(INTERNAL_FUNCTION("ACTIVATIONTIME"),'fmdd'))
             5 - access("A"."ID"="PROCESSINFOID" AND "A"."PROCESSID"="PROCESSID")
             7 - filter("PROCESSID" IS NOT NULL)
             9 - access("BUSINESSDATE"=TRUNC(INTERNAL_FUNCTION("COMPLETETIME"),'fmdd'))
            11 - access("A"."ID"="PROCESSINFOID" AND "A"."PROCESSID"="PROCESSID")
            13 - filter("PROCESSID" IS NOT NULL)
            14 - filter(MOD("RN",4)=2)
            16 - access("T7"."PROCESSID"="T1"."PROCESSID"(+) AND "T7"."TASKNAME"="T1"."TASKNAME"(+) AND
               "T7"."OPERATORCODE"="T1"."OPERATORCODE"(+) AND "T7"."ORGCODE"="T1"."ORGCODE"(+) AND
               "T7"."ORGCATE"="T1"."ORGCATE"(+) AND "T7"."PROTYPE"="T1"."PROTYPE"(+) AND "T7"."PROCODE"="T1"."PROCODE"(+))
            17 - access("T7"."PROCESSID"="A0"."PROCESSID"(+) AND "T7"."TASKNAME"="A0"."TASKNAME"(+) AND
               "T7"."OPERATORCODE"="A0"."OPERATORCODE"(+))
            18 - access("T7"."PROCESSID"="T2"."PROCESSID"(+) AND "T7"."TASKNAME"="T2"."TASKNAME"(+) AND
               "T7"."OPERATORCODE"="T2"."OPERATORCODE"(+) AND "T7"."ORGCODE"="T2"."ORGCODE"(+) AND
               "T7"."ORGCATE"="T2"."ORGCATE"(+) AND "T7"."PROTYPE"="T2"."PROTYPE"(+) AND "T7"."PROCODE"="T2"."PROCODE"(+))
            19 - access("T7"."PROCESSID"="A1"."PROCESSID"(+) AND "T7"."TASKNAME"="A1"."TASKNAME"(+) AND
               "T7"."OPERATORCODE"="A1"."OPERATORCODE"(+))
            20 - access("T7"."PROCESSID"="A2"."PROCESSID"(+) AND "T7"."TASKNAME"="A2"."TASKNAME"(+) AND
               "T7"."OPERATORCODE"="A2"."OPERATORCODE"(+))
            23 - access("A"."ID"="PROCESSINFOID" AND "A"."PROCESSID"="PROCESSID")
            26 - filter("PROCESSID" IS NOT NULL AND "BUSINESSDATE">=TRUNC(INTERNAL_FUNCTION("ACTIVATIONTIME"),'fmdd'))
            29 - access("ID"="V3"."PROID")
            31 - access("ID"="V3"."USERID")
            32 - access("ORGID"="ID")
            34 - access("ID"="FLOWUSERID")
            35 - filter("VALIDFLAG"='0' OR "VALIDFLAG"='1')
            36 - access("FLOWROLEID"="ID")
            37 - access("FLOWROLECODE"="BR"."STEPROLE")
            40 - filter("USEFLAG"='1')
            48 - access("S1"."ORGCODE"="ORGCODE")
            50 - access("S1"."PROCODE"="D"."PROCODE")
            52 - filter("S1"."PROCESSID"='aheadRedemption' OR "S1"."PROCESSID"='financeAccMod' OR
               "S1"."PROCESSID"='financeApp' OR "S1"."PROCESSID"='financeCusMod' OR "S1"."PROCESSID"='financeInno')
            60 - access("S0"."ORGCODE"="ORGCODE")
            62 - access("S0"."PROCODE"="D"."PROCODE")
            64 - filter("S0"."PROCESSID"='aheadRedemption' OR "S0"."PROCESSID"='financeAccMod' OR
               "S0"."PROCESSID"='financeApp' OR "S0"."PROCESSID"='financeCusMod' OR "S0"."PROCESSID"='financeInno')
          
          
          Statistics
          ----------------------------------------------------------
          609  recursive calls
          91  db block gets
               491810  consistent gets
               300296  physical reads
                 1244  redo size
              2891260  bytes sent via SQL*Net to client
                29933  bytes received via SQL*Net from client
                 2448  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
                36691  rows processed

从执行计划看，1分44s到24s，逻辑读从4000w到50w，物理读30w基本没有什么大的变化，没有内存排序。

到这里基本优化完毕，24s查询出4w条数据，我也是拼了老命了。针对大表全扫描，从谓词信息看过滤条件是filter("PROCESSID" IS NOT NULL)，所以也好像显得无能为力。

算了就此作罢。

# 测试验证正确性 #

最简单的办法就是到测试环境中将修改SQL查询的数据同原SQL查询的数据做minus，然后得到如下：

![Image 1][]

![Center 2][]

其中t表是针对修改后SQL做的临时表，验证结论正确无误。

ok！搞定收工！！！

[Image 1]: 
[Center]: /images/20220708/c4cbe43cf2564fb486509c28177c422c.png
[Center 1]: /images/20220708/5c0d5a05b6314543a327aa29383f6317.png
[Center 2]: /images/20220708/5d02fd7308a04577aadd9edc56a09ef3.png