关闭

利用no_merge优化

578人阅读 评论(0) 收藏 举报
分类:
SQL> select a.unit3_code 机构编码,
  2         a.unit3_name 机构名称,
  3         a.dept1_code 部门编码,
  4         a.dept1_name 部门名称,
  5         a.mgr_code   客户经理编码,
  6         a.mgr_name   客户经理名称,
  7         b.base       基数
  8    from s_pm_mgr_dept_rela a,
  9         (select t2.mgr_code, sum(T1.avg * t2.share_rate / 100) / 10000 base
 10            from (select acct_no_pk,
 11                         (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) -
 12                         SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) +
 13                         SUM(DECODE(T1.DATA_DATE,
 14                                     '20120101',
 15                                     T1.ADJUST_AMT_AF,
 16                                     0))) /
 17                         PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG
 18                    from t_pm_acct_dtl_af t1
 19                   where 1 = 1
 20                     and t1.data_date in ('20120101', '20121217')
 21                     and acct_flag = 'DEPOSIT'
 22                     and t1.cur_code = 1
 23                     and substr(flag, 1, 1) = '1'
 24                     and substr(flag, 4, 1) = '1'
 25                     and t1.dept1_code <> '999999999'
 26                   group by acct_no_pk) t1,
 27                 (select t3.acct_no_pk as acct_no_pk,
 28                         NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code,
 29                         decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate
 30                    from t_pm_acct_dtl   t3,
 31                         bap_cm_account  t1,
 32                         bap_cm_psndoc   t2,
 33                         bap_nctobapdept t4
 34                   where 1 = 1
 35                     and t1.account(+) = t3.acct_no_pk
 36                     and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N'
 37                     and replace(t1.effect_date(+), '-') <= '20121217'
 38                     and replace(decode(t1.seal_date(+),
 39                                        '',
 40                                        pkg_uf_bank.FN_GETNEXTDAY('20121217',
 41                                                                  'M'),
 42                                        t1.seal_date(+)),
 43                                 '-') > '20121217'
 44                     and decode(t1.dr(+), '', 0, t1.dr(+)) = 0
 45                     and t1.pk_cm_psndoc = t2
 46                  .pk_cm_psndoc(+)
 47                     and decode(t2.dr(+), '', 0, t2.dr(+)) = 0
 48                     and t2.pk_dept = t4.pk_nctobapdept(+)
 49                     and t4.deptcode(+) <> '999999999'
 50                     and t3.data_date = '20121217'
 51                     and t3.acct_flag = 'DEPOSIT') t2
 52           Where t1.acct_no_pk = t2.acct_no_pk
 53           group by t2.mgr_code) b
 54   where a.mgr_code = b.mgr_code
 55     and a.dept1_code <> '999999999'
 56     and a.dept1_code = '18605'
 57     and a.unit3_code = '18601'
 58     and 1 = 1
 59     and a.mgr_code IN ('00798', '00817')
 60   order by 1, 3, 5
 61  ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1749794338

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                    |     1 |   288 | 11414  (15)| 00:00:33 |       |       |
|   1 |  SORT GROUP BY                           |                    |     1 |   288 | 11414  (15)| 00:00:33 |       |       |
|*  2 |   HASH JOIN OUTER                        |                    |     1 |   288 | 11413  (15)| 00:00:33 |       |       |
|*  3 |    FILTER                                |                    |       |       |            |          |       |       |
|   4 |     NESTED LOOPS OUTER                   |                    |     1 |   258 | 11408  (15)| 00:00:33 |       |       |
|   5 |      NESTED LOOPS OUTER                  |                    |     1 |   207 | 11407  (15)| 00:00:33 |       |       |
|   6 |       NESTED LOOPS                       |                    |     1 |   139 | 11402  (15)| 00:00:33 |       |       |
|   7 |        MERGE JOIN CARTESIAN              |                    |     1 |    98 | 11398  (15)| 00:00:33 |       |       |
|   8 |         INLIST ITERATOR                  |                    |       |       |            |          |       |       |
|*  9 |          TABLE ACCESS BY INDEX ROWID     | S_PM_MGR_DEPT_RELA |     1 |    63 |     3   (0)| 00:00:01 |       |       |
|* 10 |           INDEX RANGE SCAN               | MGR_DEPT_RELA_IDX5 |     1 |       |     2   (0)| 00:00:01 |       |       |
|  11 |         BUFFER SORT                      |                    |    93 |  3255 | 11396  (15)| 00:00:33 |       |       |
|  12 |          VIEW                            |                    |    93 |  3255 | 11396  (15)| 00:00:33 |       |       |
|  13 |           HASH GROUP BY                  |                    |    93 |  5487 | 11396  (15)| 00:00:33 |       |       |
|  14 |            PARTITION LIST INLIST         |                    |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
|* 15 |             TABLE ACCESS FULL            | T_PM_ACCT_DTL_AF   |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
|  16 |        PARTITION LIST SINGLE             |                    |     1 |    41 |     4   (0)| 00:00:01 |   KEY |   KEY |
|* 17 |         TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL      |     1 |    41 |     4   (0)| 00:00:01 |   352 |   352 |
|* 18 |          INDEX RANGE SCAN                | ACCT_DTL_IDX1      |     2 |       |     2   (0)| 00:00:01 |   352 |   352 |
|* 19 |       TABLE ACCESS BY INDEX ROWID        | BAP_CM_ACCOUNT     |     1 |    68 |     5   (0)| 00:00:01 |       |       |
|* 20 |        INDEX RANGE SCAN                  | IDX_BAP_CM_ACCOUNT |     2 |       |     2   (0)| 00:00:01 |       |       |
|* 21 |      TABLE ACCESS BY INDEX ROWID         | BAP_CM_PSNDOC      |     1 |    51 |     1   (0)| 00:00:01 |       |       |
|* 22 |       INDEX UNIQUE SCAN                  | PK_BAP_CM_PSNDOC   |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 23 |    TABLE ACCESS FULL                     | BAP_NCTOBAPDEPT    |   609 | 18270 |     4   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T2"."PK_DEPT"="T4"."PK_NCTOBAPDEPT"(+))
   3 - filter("A"."MGR_CODE"=NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx')) AND
              (NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00798' OR
              NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00817'))
   9 - filter("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999')
  10 - access("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817'))
  15 - filter("ACCT_FLAG"='DEPOSIT' AND SUBSTR("FLAG",1,1)='1' AND SUBSTR("FLAG",4,1)='1' AND
              "T1"."DEPT1_CODE"<>'999999999' AND TO_NUMBER("T1"."CUR_CODE")=1)
  17 - filter("T3"."ACCT_FLAG"='DEPOSIT')
  18 - access("T3"."DATA_DATE"=20121217 AND "T1"."ACCT_NO_PK"="T3"."ACCT_NO_PK")
  19 - filter(DECODE("T1"."SEAL_FLAG"(+),'','N',"T1"."SEAL_FLAG"(+))='N' AND
              REPLACE("T1"."EFFECT_DATE"(+),'-')<='20121217' AND DECODE(TO_CHAR("T1"."DR"(+)),'',0,"T1"."DR"(+))=0 AND
              REPLACE(DECODE("T1"."SEAL_DATE"(+),'',"PKG_UF_BANK"."FN_GETNEXTDAY"('20121217','M'),"T1"."SEAL_DATE"(+)),'-')>'20121217
              ')
  20 - access("T1"."ACCOUNT"(+)="T3"."ACCT_NO_PK")
  21 - filter(DECODE(TO_CHAR("T2"."DR"(+)),'',0,"T2"."DR"(+))=0)
  22 - access("T1"."PK_CM_PSNDOC"="T2"."PK_CM_PSNDOC"(+))
  23 - filter("T4"."DEPTCODE"(+)<>'999999999')


Statistics
----------------------------------------------------------
        214  recursive calls
          0  db block gets
   11912102  consistent gets
     122578  physical reads
      13516  redo size
       1064  bytes sent via SQL*Net to client
       1625  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed
select count(*) from S_PM_MGR_DEPT_RELA A where ("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817'))
and ("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999');
---返回2条记录


(select acct_no_pk,
 11                         (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) -
 12                         SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) +
 13                         SUM(DECODE(T1.DATA_DATE,
 14                                     '20120101',
 15                                     T1.ADJUST_AMT_AF,
 16                                     0))) /
 17                         PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG
 18                    from t_pm_acct_dtl_af t1
 19                   where 1 = 1
 20                     and t1.data_date in ('20120101', '20121217')
 21                     and acct_flag = 'DEPOSIT'
 22                     and t1.cur_code = 1
 23                     and substr(flag, 1, 1) = '1'
 24                     and substr(flag, 4, 1) = '1'
 25                     and t1.dept1_code <> '999999999'
 26                   group by acct_no_pk) t1



 (select t3.acct_no_pk as acct_no_pk,
 28                         NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code,
 29                         decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate
 30                    from t_pm_acct_dtl   t3,
 31                         bap_cm_account  t1,
 32                         bap_cm_psndoc   t2,
 33                         bap_nctobapdept t4
 34                   where 1 = 1
 35                     and t1.account(+) = t3.acct_no_pk
 36                     and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N'
 37                     and replace(t1.effect_date(+), '-') <= '20121217'
 38                     and replace(decode(t1.seal_date(+),
 39                                        '',
 40                                        pkg_uf_bank.FN_GETNEXTDAY('20121217',
 41                                                                  'M'),
 42                                        t1.seal_date(+)),
 43                                 '-') > '20121217'
 44                     and decode(t1.dr(+), '', 0, t1.dr(+)) = 0
 45                     and t1.pk_cm_psndoc = t2
 46                  .pk_cm_psndoc(+)
 47                     and decode(t2.dr(+), '', 0, t2.dr(+)) = 0
 48                     and t2.pk_dept = t4.pk_nctobapdept(+)
 49                     and t4.deptcode(+) <> '999999999'
 50                     and t3.data_date = '20121217'
 51                     and t3.acct_flag = 'DEPOSIT') t2


|  12 |          VIEW                            |                    |    93 |  3255 | 11396  (15)| 00:00:33 |       |       |
|  13 |           HASH GROUP BY                  |                    |    93 |  5487 | 11396  (15)| 00:00:33 |       |       |
|  14 |            PARTITION LIST INLIST         |                    |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
|* 15 |             TABLE ACCESS FULL            | T_PM_ACCT_DTL_AF   |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
|  16 |        PARTITION LIST SINGLE             |                    |     1 |    41 |     4   (0)| 00:00:01 |   KEY |   KEY |
|* 17 |         TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL      |     1 |    41 |     4   (0)| 00:00:01 |   352 |   352 |
|* 18 |          INDEX RANGE SCAN                | ACCT_DTL_IDX1      |     2 |       |     2   (0)| 00:00:01 |   352 |   352 |
|* 19 |       TABLE ACCESS BY INDEX ROWID        | BAP_CM_ACCOUNT     |     1 |    68 |     5   (0)| 00:00:01 |       |       |
|* 20 |        INDEX RANGE SCAN                  | IDX_BAP_CM_ACCOUNT |     2 |       |     2   (0)| 00:00:01 |       |       |
|* 21 |      TABLE ACCESS BY INDEX ROWID         | BAP_CM_PSNDOC      |     1 |    51 |     1   (0)| 00:00:01 |       |       |
|* 22 |       INDEX UNIQUE SCAN                  | PK_BAP_CM_PSNDOC   |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 23 |    TABLE ACCESS FULL                     | BAP_NCTOBAPDEPT    |   609 | 18270 |     4   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------


可以看到t1视图没有展开,但是t2视图确被展开了,尝试让t2视图不展开

SQL> select a.unit3_code 机构编码,
  2         a.unit3_name 机构名称,
  3         a.dept1_code 部门编码,
  4         a.dept1_name 部门名称,
  5         a.mgr_code   客户经理编码,
  6         a.mgr_name   客户经理名称,
  7         b.base       基数
  8    from s_pm_mgr_dept_rela a,
  9         (select /*+ no_merge(t2)*/ t2.mgr_code, sum(T1.avg * t2.share_rate / 100) / 10000 base
 10            from (select acct_no_pk,
 11                         (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) -
 12                         SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) +
 13                         SUM(DECODE(T1.DATA_DATE,
 14                                     '20120101',
 15                                     T1.ADJUST_AMT_AF,
 16                                     0))) /
 17                         PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG
 18                    from t_pm_acct_dtl_af t1
 19                   where 1 = 1
 20                     and t1.data_date in ('20120101', '20121217')
 21                     and acct_flag = 'DEPOSIT'
 22                     and t1.cur_code = 1
 23                     and substr(flag, 1, 1) = '1'
 24                     and substr(flag, 4, 1) = '1'
 25                     and t1.dept1_code <> '999999999'
 26                   group by acct_no_pk) t1,
 27                 (select 
 28                   t3.acct_no_pk as acct_no_pk,
 29                   NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code,
 30                   decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate
 31                    from t_pm_acct_dtl   t3,
 32                         bap_cm_account  t1,
 33                         bap_cm_psndoc   t2,
 34                         bap_nctobapdept t4
 35                   where 1 = 1
 36                     and t1.account(+) = t3.acct_no_pk
 37                     and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N'
 38                     and replace(t1.effect_date(+), '-') <= '20121217'
 39                     and replace(decode(t1.seal_date(+),
 40                                        '',
 41                                        pkg_uf_bank.FN_GETNEXTDAY('20121217',
 42                                                                  'M'),
 43                                        t1.seal_date(+)),
 44                                 '-') > '20121217'
 45                     and decode(t1.dr(+), '', 0, t1.dr(+)) = 0
 46                     and t1.pk_cm_psndoc = t2
 47                  .pk_cm_psndoc(+)
 48                     and decode(t2.dr(+), '', 0, t2.dr(+)) = 0
 49                     and t2.pk_dept = t4.pk_nctobapdept(+)
 50                     and t4.deptcode(+) <> '999999999'
 51                     and t3.data_date = '20121217'
 52                     and t3.acct_flag = 'DEPOSIT') t2
 53           Where t1.acct_no_pk = t2.acct_no_pk
 54           group by t2.mgr_code) b
 55   where a.mgr_code = b.mgr_code
 56     and a.dept1_code <> '999999999'
 57     and a.dept1_code = '18605'
 58     and a.unit3_code = '18601'
 59     and 1 = 1
 60     and a.mgr_code IN ('00798', '00817')
 61   order by 1, 3, 5
 62  ;


Execution Plan
----------------------------------------------------------
Plan hash value: 74438866

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |   150 | 19483  (14)| 00:00:57 |       |       |
|   1 |  SORT GROUP BY                  |                    |     1 |   150 | 19483  (14)| 00:00:57 |       |       |
|*  2 |   HASH JOIN                     |                    |     1 |   150 | 19482  (14)| 00:00:57 |       |       |
|*  3 |    HASH JOIN                    |                    |     1 |   115 |  8086  (13)| 00:00:24 |       |       |
|   4 |     INLIST ITERATOR             |                    |       |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID| S_PM_MGR_DEPT_RELA |     1 |    63 |     3   (0)| 00:00:01 |       |       |
|*  6 |       INDEX RANGE SCAN          | MGR_DEPT_RELA_IDX5 |     1 |       |     2   (0)| 00:00:01 |       |       |
|   7 |     VIEW                        |                    | 12519 |   635K|  8082  (13)| 00:00:24 |       |       |
|*  8 |      HASH JOIN RIGHT OUTER      |                    | 12519 |  2322K|  8082  (13)| 00:00:24 |       |       |
|*  9 |       TABLE ACCESS FULL         | BAP_NCTOBAPDEPT    |   609 | 18270 |     4   (0)| 00:00:01 |       |       |
|* 10 |       FILTER                    |                    |       |       |            |          |       |       |
|* 11 |        HASH JOIN RIGHT OUTER    |                    | 12519 |  1956K|  8077  (13)| 00:00:24 |       |       |
|* 12 |         TABLE ACCESS FULL       | BAP_CM_PSNDOC      |    84 |  4284 |    33  (10)| 00:00:01 |       |       |
|* 13 |         HASH JOIN RIGHT OUTER   |                    |   629K|    65M|  8021  (12)| 00:00:24 |       |       |
|* 14 |          TABLE ACCESS FULL      | BAP_CM_ACCOUNT     |     1 |    68 |  5627  (12)| 00:00:17 |       |       |
|  15 |          PARTITION LIST SINGLE  |                    |   629K|    24M|  2370  (13)| 00:00:07 |   KEY |   KEY |
|* 16 |           TABLE ACCESS FULL     | T_PM_ACCT_DTL      |   629K|    24M|  2370  (13)| 00:00:07 |   352 |   352 |
|  17 |    VIEW                         |                    |    93 |  3255 | 11396  (15)| 00:00:33 |       |       |
|  18 |     HASH GROUP BY               |                    |    93 |  5487 | 11396  (15)| 00:00:33 |       |       |
|  19 |      PARTITION LIST INLIST      |                    |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
|* 20 |       TABLE ACCESS FULL         | T_PM_ACCT_DTL_AF   |    93 |  5487 | 11395  (15)| 00:00:33 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."ACCT_NO_PK"="T2"."ACCT_NO_PK")
   3 - access("A"."MGR_CODE"="T2"."MGR_CODE")
   5 - filter("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999')
   6 - access("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817'))
   8 - access("T2"."PK_DEPT"="T4"."PK_NCTOBAPDEPT"(+))
   9 - filter("T4"."DEPTCODE"(+)<>'999999999')
  10 - filter(NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00798' OR
              NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00817')
  11 - access("T1"."PK_CM_PSNDOC"="T2"."PK_CM_PSNDOC"(+))
  12 - filter(DECODE(TO_CHAR("T2"."DR"(+)),'',0,"T2"."DR"(+))=0)
  13 - access("T1"."ACCOUNT"(+)="T3"."ACCT_NO_PK")
  14 - filter(DECODE("T1"."SEAL_FLAG"(+),'','N',"T1"."SEAL_FLAG"(+))='N' AND
              REPLACE("T1"."EFFECT_DATE"(+),'-')<='20121217' AND DECODE(TO_CHAR("T1"."DR"(+)),'',0,"T1"."DR"(+))=0 AND
              REPLACE(DECODE("T1"."SEAL_DATE"(+),'',"PKG_UF_BANK"."FN_GETNEXTDAY"('20121217','M'),"T1"."SEAL_DATE"(+)),'-')>
              '20121217')
  16 - filter("T3"."ACCT_FLAG"='DEPOSIT')
  20 - filter("ACCT_FLAG"='DEPOSIT' AND SUBSTR("FLAG",1,1)='1' AND SUBSTR("FLAG",4,1)='1' AND
              "T1"."DEPT1_CODE"<>'999999999' AND TO_NUMBER("T1"."CUR_CODE")=1)


Statistics
----------------------------------------------------------
        154  recursive calls
          0  db block gets
     193327  consistent gets
      13545  physical reads
        692  redo size
       1064  bytes sent via SQL*Net to client
       1649  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed






 


 

0
0

猜你在找
【套餐】Hadoop生态系统零基础入门
【套餐】嵌入式Linux C编程基础
【套餐】2017软考系统集成项目——任铄
【套餐】Android 5.x顶级视频课程——李宁
【套餐】深度学习入门视频课程——唐宇迪
【直播】广义线性模型及其应用——李科
【直播】从0到1 区块链的概念到实践
【直播】计算机视觉原理及实战——屈教授
【直播】机器学习之凸优化——马博士
【直播】机器学习&数据挖掘7周实训--韦玮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1159411次
    • 积分:38353
    • 等级:
    • 排名:第103名
    • 原创:2808篇
    • 转载:14篇
    • 译文:0篇
    • 评论:45条
    文章分类
    最新评论