SQL优化-标量子查询的改写

最新推荐文章于 2024-04-14 23:19:14 发布

洛阳城头见洛阳

最新推荐文章于 2024-04-14 23:19:14 发布

阅读量3.2k

点赞数

分类专栏： ORACLE SQL 调优

本文链接：https://blog.csdn.net/killvoon/article/details/51498840

版权

ORACLE SQL 调优专栏收录该内容

3 篇文章 0 订阅

订阅专栏

原语句：

with d as
 (select *
    from dc.Dcam_Lbsq d
   where d.inptdt between
         to_date(substr('20110930', 1, 6) || '01', 'yyyymmdd') and
         to_date('20110930', 'yyyymmdd')
     and d.lnbltp in ('B', '5', '6')
     and d.Begndt <= To_Date('20110930', 'yyyymmdd')
     And d.Overdt > To_Date('20110930', 'yyyymmdd'))
select /*+index(c IDX_DCAM_LBSQ_ACCTID) index(d IDX_DCAM_LBSQ_ACCTID)*/
 a.lncfno as 账户号,
 a.loancn as 合同号,
 a.lncfno as 借据编号,
 null as 申请号,
 b.custno as 客户号,
 substr('20110930', 1, 6) as 年月戳,
 a.disbdt as 账户开户日期,
 a.closdt as 账户关闭日期,
 null as 还款频率,
 a.initam as 原始贷款金额,
 c.onlnbl + nvl(d.onlnbl, 0) as 本期贷款余额,
 c.orgibl as 本期应还本金,
 nvl(d.orgibl, 0) as 本期应还利息,
 c.orgibl - c.onlnbl as 本期实还本金,
 nvl(d.orgibl, 0) - nvl(d.onlnbl, 0) as 本期实还利息,
 null as 提前还款本金,
 case
   when a.matudt > '20110930' then
    0
   else
    c.onlnbl
 end as 逾期金额,
 case
   when a.matudt > '20110930' then
    0
   else
    c.onlnbl
 end as 本期逾期本金,
 nvl(d.onlnbl, 0) as 本期逾期利息,
 case
   when a.matudt > '20110930' then
    0
   else
    trunc(((to_date(a.matudt, 'yyyymmdd') - to_date('20110930', 'yyyymmdd')) - 1) / 30,
          0) + 1
 end as 本期逾期期数,
 case
   when a.matudt > '20110930' then
    0
   else
    to_date('20110930', 'yyyymmdd') - to_date(a.matudt, 'yyyymmdd')
 end as 本期最大逾期天数,
 case
   when a.matudt > '20110930' then
    0
   else
    to_date('20110930', 'yyyymmdd') - to_date(a.matudt, 'yyyymmdd')
 end as 逾期天数,
 null as 贷款剩余期数,
 null as 贷款期数,
 (select nvl(sum(i.tranam), 0)
    from dc.Dcam_Lscf h
    join dc.Dcam_Bspg i
      on h.contno = i.contno
   where h.lncfno = a.lncfno) as 抵质押物认定价值
  from srcapp.lnb_lncf a
  join srcapp.knc_acid b
    on a.lncfno = b.datavl
--join mon_day j on a.disbdt < '20110930'
  join dc.Dcam_Lbsq c
    on a.acctid = c.acctid
   and c.lnbltp in ('1', '2')
   and c.Begndt <= To_Date('20110930', 'yyyymmdd')
   And c.Overdt > To_Date('20110930', 'yyyymmdd')
  left join d
  on a.acctid = d.acctid
 where a.retnfs = '1'
   and a.disbdt < '20110930'
   and a.initam <= 5000000

执行计划：(DCAM_LBSQ有四千多万的数据,KNC_ACID表有一千三百多万的数据,LNB_LNCF 有三十多万的数据 )

13	--------------------------------------------------------------------------------------------------------------------------------------
14	| Id  | Operation                             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
15	--------------------------------------------------------------------------------------------------------------------------------------
16	|   0 | SELECT STATEMENT                      |                      |       |       |       |  3189K(100)|          |       |       |
17	|   1 |  SORT AGGREGATE                       |                      |     1 |    51 |       |            |          |       |       |
18	|*  2 |   HASH JOIN                           |                      |     1 |    51 |       |  3197   (2)| 00:00:45 |       |       |
19	|*  3 |    TABLE ACCESS FULL                  | DCAM_LSCF            |     1 |    28 |       |  2945   (2)| 00:00:42 |       |       |
20	|   4 |    TABLE ACCESS FULL                  | DCAM_BSPG            | 51086 |  1147K|       |   252   (1)| 00:00:04 |       |       |
21	|*  5 |  HASH JOIN                            |                      |  2101K|   523M|    79M|  3189K  (1)| 12:24:19 |       |       |
22	|*  6 |   HASH JOIN RIGHT OUTER               |                      |   357K|    75M|  4544K|  2244K  (1)| 08:43:44 |       |       |
23	|   7 |    PARTITION RANGE ITERATOR           |                      | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |     1 |    10 |
24	|   8 |     VIEW                              |                      | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |       |       |
25	|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID| DCAM_LBSQ            | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |     1 |    10 |
26	|* 10 |       INDEX RANGE SCAN                | IDX_DCAM_LBSQ_ACCTID |  1630K|       |       | 61415   (1)| 00:14:20 |     1 |    10 |
27	|* 11 |    HASH JOIN                          |                      |   357K|    58M|    51M| 44239   (1)| 00:10:20 |       |       |
28	|* 12 |     TABLE ACCESS FULL                 | LNB_LNCF             |   356K|    47M|       |  3481   (2)| 00:00:49 |       |       |
29	|  13 |     TABLE ACCESS FULL                 | KNC_ACID             |    13M|   435M|       | 13062   (2)| 00:03:03 |       |       |
30	|  14 |   PARTITION RANGE ITERATOR            |                      |  2097K|    80M|       |   937K  (1)| 03:38:45 |     1 |    10 |
31	|  15 |    TABLE ACCESS BY LOCAL INDEX ROWID  | DCAM_LBSQ            |  2097K|    80M|       |   937K  (1)| 03:38:45 |     1 |    10 |
32	|* 16 |     INDEX RANGE SCAN                  | IDX_DCAM_LBSQ_ACCTID |   668K|       |       | 61383   (1)| 00:14:20 |     1 |    10 |
33	--------------------------------------------------------------------------------------------------------------------------------------
34	 
35	Predicate Information (identified by operation id):
36	---------------------------------------------------
37	 
38	   2 - access("H"."CONTNO"="I"."CONTNO")
39	   3 - filter(("H"."CONTNO" IS NOT NULL AND "H"."LNCFNO"=:B1))
40	   5 - access("A"."ACCTID"="C"."ACCTID")
41	   6 - access("A"."ACCTID"="D"."ACCTID")
42	   9 - filter(("D"."INPTDT"<=TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."INPTDT">=TO_DATE(' 2011-09-01 
43	              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
44	  10 - access("D"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."BEGNDT"<=TO_DATE(' 2011-09-30 
45	              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
46	       filter(("D"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND INTERNAL_FUNCTION("D"."LNBLTP")))
47	  11 - access("A"."LNCFNO"="B"."DATAVL")
48	  12 - filter(("A"."INITAM"<=5000000 AND "A"."DISBDT"<'20110930' AND "A"."RETNFS"='1'))
49	  16 - access("C"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C"."BEGNDT"<=TO_DATE(' 2011-09-30 
50	              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
51	       filter(("C"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND INTERNAL_FUNCTION("C"."LNBLTP")))
52

分析说明: 之前还没有使用with as的形式,直接select,根本查不出来数据,使用with as后大概5分钟可以弹出100条数据,但结果集将近30万的数据,预计要5个小时左右才能完全查出结果,速度慢的令人发指.

仔细观察执行计划后,对于大表都走了hash join,但最上面的标量子查询不是如此,于是怀疑性能问题出现在标量子查询部分.

对于标量子查询,如果where谓词条件后面的结果集很少,那么性能没有什么问题,但当where后面结果集很大,比如几十万的数据,那么表量子查询的性能将会很差,这时候考虑将标量子查询改为left join的方式,
让原有的标量子查询中的表参与表连接.

修改成如下写法后,40秒左右完成查询.

with d as
 (select *
    from dc.Dcam_Lbsq d
   where d.inptdt between
         to_date(substr('20110930', 1, 6) || '01', 'yyyymmdd') and
         to_date('20110930', 'yyyymmdd')
     and d.lnbltp in ('B', '5', '6')
     and d.Begndt <= To_Date('20110930', 'yyyymmdd')
     And d.Overdt > To_Date('20110930', 'yyyymmdd'))
select /*+index(c IDX_DCAM_LBSQ_ACCTID) index(d IDX_DCAM_LBSQ_ACCTID)*/
 a.lncfno as 账户号,
 a.loancn as 合同号,
 a.lncfno as 借据编号,
 null as 申请号,
 b.custno as 客户号,
 substr('20110930', 1, 6) as 年月戳,
 a.disbdt as 账户开户日期,
 a.closdt as 账户关闭日期,
 null as 还款频率,
 a.initam as 原始贷款金额,
 c.onlnbl + nvl(d.onlnbl, 0) as 本期贷款余额,
 c.orgibl as 本期应还本金,
 nvl(d.orgibl, 0) as 本期应还利息,
 c.orgibl - c.onlnbl as 本期实还本金,
 nvl(d.orgibl, 0) - nvl(d.onlnbl, 0) as 本期实还利息,
 null as 提前还款本金,
 case
   when a.matudt > '20110930' then
    0
   else
    c.onlnbl
 end as 逾期金额,
 case
   when a.matudt > '20110930' then
    0
   else
    c.onlnbl
 end as 本期逾期本金,
 nvl(d.onlnbl, 0) as 本期逾期利息,
 case
   when a.matudt > '20110930' then
    0
   else
    trunc(((to_date(a.matudt, 'yyyymmdd') - to_date('20110930', 'yyyymmdd')) - 1) / 30,
          0) + 1
 end as 本期逾期期数,
 case
   when a.matudt > '20110930' then
    0
   else
    to_date('20110930', 'yyyymmdd') - to_date(a.matudt, 'yyyymmdd')
 end as 本期最大逾期天数,
 case
   when a.matudt > '20110930' then
    0
   else
    to_date('20110930', 'yyyymmdd') - to_date(a.matudt, 'yyyymmdd')
 end as 逾期天数,
 null as 贷款剩余期数,
 null as 贷款期数,
  抵质押物认定价值
  from srcapp.lnb_lncf a
  join srcapp.knc_acid b
    on a.lncfno = b.datavl
--join mon_day j on a.disbdt < '20110930'
  join dc.Dcam_Lbsq c
    on a.acctid = c.acctid
   and c.lnbltp in ('1', '2')
   and c.Begndt <= To_Date('20110930', 'yyyymmdd')
   And c.Overdt > To_Date('20110930', 'yyyymmdd')
  left join d
  on a.acctid = d.acctid
  left join (select h.lncfno,nvl(sum(i.tranam), 0) as 抵质押物认定价值
    from dc.Dcam_Lscf h
    join dc.Dcam_Bspg i
      on h.contno = i.contno
      group by h.lncfno) s
   on a.lncfno = s.lncfno
 where a.retnfs = '1'
   and a.disbdt < '20110930'
   and a.initam <= 5000000

新的执行计划: 这样看了,这条执行计划是多么的优美...咳咳.

13	---------------------------------------------------------------------------------------------------------------------------------------
14	| Id  | Operation                              | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
15	---------------------------------------------------------------------------------------------------------------------------------------
16	|   0 | SELECT STATEMENT                       |                      |       |       |       |  3198K(100)|          |       |       |
17	|*  1 |  HASH JOIN                             |                      |  2101K|   713M|   104M|  3198K  (1)| 12:26:26 |       |       |
18	|   2 |   PARTITION RANGE ITERATOR             |                      |  2097K|    80M|       |   937K  (1)| 03:38:45 |     1 |    10 |
19	|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID   | DCAM_LBSQ            |  2097K|    80M|       |   937K  (1)| 03:38:45 |     1 |    10 |
20	|*  4 |     INDEX RANGE SCAN                   | IDX_DCAM_LBSQ_ACCTID |   668K|       |       | 61383   (1)| 00:14:20 |     1 |    10 |
21	|*  5 |   HASH JOIN RIGHT OUTER                |                      |   357K|   107M|  8944K|  2252K  (1)| 08:45:32 |       |       |
22	|   6 |    VIEW                                |                      | 85512 |  7933K|       |  3881   (2)| 00:00:55 |       |       |
23	|   7 |     HASH GROUP BY                      |                      | 85512 |  4258K|     9M|  3881   (2)| 00:00:55 |       |       |
24	|*  8 |      HASH JOIN                         |                      | 85512 |  4258K|       |  3198   (2)| 00:00:45 |       |       |
25	|   9 |       TABLE ACCESS FULL                | DCAM_BSPG            | 51086 |  1147K|       |   252   (1)| 00:00:04 |       |       |
26	|* 10 |       TABLE ACCESS FULL                | DCAM_LSCF            | 86603 |  2368K|       |  2944   (2)| 00:00:42 |       |       |
27	|* 11 |    HASH JOIN RIGHT OUTER               |                      |   357K|    75M|  4544K|  2244K  (1)| 08:43:44 |       |       |
28	|  12 |     PARTITION RANGE ITERATOR           |                      | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |     1 |    10 |
29	|  13 |      VIEW                              |                      | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |       |       |
30	|* 14 |       TABLE ACCESS BY LOCAL INDEX ROWID| DCAM_LBSQ            | 77288 |  3622K|       |  2197K  (1)| 08:32:45 |     1 |    10 |
31	|* 15 |        INDEX RANGE SCAN                | IDX_DCAM_LBSQ_ACCTID |  1630K|       |       | 61415   (1)| 00:14:20 |     1 |    10 |
32	|* 16 |     HASH JOIN                          |                      |   357K|    58M|    51M| 44239   (1)| 00:10:20 |       |       |
33	|* 17 |      TABLE ACCESS FULL                 | LNB_LNCF             |   356K|    47M|       |  3481   (2)| 00:00:49 |       |       |
34	|  18 |      TABLE ACCESS FULL                 | KNC_ACID             |    13M|   435M|       | 13062   (2)| 00:03:03 |       |       |
35	---------------------------------------------------------------------------------------------------------------------------------------
36	 
37	Predicate Information (identified by operation id):
38	---------------------------------------------------
39	 
40	   1 - access("A"."ACCTID"="C"."ACCTID")
41	   4 - access("C"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C"."BEGNDT"<=TO_DATE(' 2011-09-30 
42	              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
43	       filter(("C"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND INTERNAL_FUNCTION("C"."LNBLTP")))
44	   5 - access("A"."LNCFNO"="S"."LNCFNO")
45	   8 - access("H"."CONTNO"="I"."CONTNO")
46	  10 - filter("H"."CONTNO" IS NOT NULL)
47	  11 - access("A"."ACCTID"="D"."ACCTID")
48	  14 - filter(("D"."INPTDT"<=TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."INPTDT">=TO_DATE(' 2011-09-01 
49	              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
50	  15 - access("D"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."BEGNDT"<=TO_DATE(' 2011-09-30 
51	              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
52	       filter(("D"."OVERDT">TO_DATE(' 2011-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND INTERNAL_FUNCTION("D"."LNBLTP")))
53	  16 - access("A"."LNCFNO"="B"."DATAVL")
54	  17 - filter(("A"."INITAM"<=5000000 AND "A"."DISBDT"<'20110930' AND "A"."RETNFS"='1'))

洛阳城头见洛阳

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
SQL优化-标量子查询的改写

原语句：with d as (select * from dc.Dcam_Lbsq d where d.inptdt between to_date(substr('20110930', 1, 6) || '01', 'yyyymmdd') and to_date('20110930', 'yyyymmdd') and d.lnbl
复制链接

扫一扫