都是标量子查询惹的祸

0    287    1

Tags:

👉 本文共约7915个字,系统预计阅读时间或需30分钟。

目录

    系统又报了一个跑的慢的sql语句,看图就知道这个很恐怖的,已经跑了1天了,还需要跑6个月的时间,

    img

    把sql语句拿出来瞅瞅:

    --201406 XX

    select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

    SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

    CASE WHEN greatest(nvl(BAL_TOTAL,0),0) < 500 THEN '1.[0,500)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

    AND greatest(nvl(BAL_TOTAL,0),0) < 5000 THEN '2.[500,5000)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

    AND greatest(nvl(BAL_TOTAL,0),0) < 20000 THEN '3.[5000,2W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

    AND greatest(nvl(BAL_TOTAL,0),0) < 50000 THEN '4[2W,5W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

    else '其他' end 额度区间,

    CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

    else '非小企业主' end 小企业主标识

    from riskpubstrategy.lwt_ambs_cc_201406 A

    group by DELQ_LEVEL_CI,city,FQ_TOTAL,

    CASE WHEN greatest(nvl(BAL_TOTAL,0),0) < 500 THEN '1.[0,500)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

    AND greatest(nvl(BAL_TOTAL,0),0) < 5000 THEN '2.[500,5000)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

    AND greatest(nvl(BAL_TOTAL,0),0) < 20000 THEN '3.[5000,2W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

    AND greatest(nvl(BAL_TOTAL,0),0) < 50000 THEN '4[2W,5W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

    else '其他' end,

    CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

    else '非小企业主' end;

    内存中的执行计划:SELECT *** FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a50xqp45uy256',0,'advanced'));**

    img

    简单分析下,查询的只有一个表,这就很奇怪了,不管这个表多么大,也不可能跑这么久的吧,但是有一个标量子查询,根据经验,如果标量子查询的表数据量很多,或者关联的列没有索引的话,标量子查询的性能将会是非常差的,我们由执行计划也可以看出标量子查询的表RISKPUBSC.MICRO_BUSI_DATABASE的关联列没有索引,至此,我们猜测可能是由于这个标量子查询引起sql语句性能低下,猜测归猜测,那我们实验一下呢?

    先去掉标量子查询,然后执行一下,如下:

    --201406 XX

    select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

    SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

    CASE WHEN greatest(nvl(BAL_TOTAL,0),0) < 500 THEN '1.[0,500)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

    AND greatest(nvl(BAL_TOTAL,0),0) < 5000 THEN '2.[500,5000)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

    AND greatest(nvl(BAL_TOTAL,0),0) < 20000 THEN '3.[5000,2W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

    AND greatest(nvl(BAL_TOTAL,0),0) < 50000 THEN '4[2W,5W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

    else '其他' end 额度区间/*,

    CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

    *else '非小企业主' end 小企业主标识**/

    from riskpubstrategy.lwt_ambs_cc_201406 A

    group by DELQ_LEVEL_CI,city,FQ_TOTAL,

    CASE WHEN greatest(nvl(BAL_TOTAL,0),0) < 500 THEN '1.[0,500)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

    AND greatest(nvl(BAL_TOTAL,0),0) < 5000 THEN '2.[500,5000)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

    AND greatest(nvl(BAL_TOTAL,0),0) < 20000 THEN '3.[5000,2W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

    AND greatest(nvl(BAL_TOTAL,0),0) < 50000 THEN '4[2W,5W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

    else '其他' end/*,

    CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

    else '非小企业主' end*/

    执行一下:img ,发现这个sql很快的,大约274秒,也就是5分钟左右,好吧,看来真是标量子查询惹的祸,,,,,,那我们首先建立索引看看性能如何呢?

    create index ind_MICRO_BUSI_DATABASE_acct on RISKPUBSC.MICRO_BUSI_DATABASE(acct) NOLOGGING parallel 8;

    alter index ind_MICRO_BUSI_DATABASE_acct NOPARALLEL;

    然后再重新执行一下之前的语句:

    --201406 XX

    select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

    SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

    CASE WHEN greatest(nvl(BAL_TOTAL,0),0) < 500 THEN '1.[0,500)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

    AND greatest(nvl(BAL_TOTAL,0),0) < 5000 THEN '2.[500,5000)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

    AND greatest(nvl(BAL_TOTAL,0),0) < 20000 THEN '3.[5000,2W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

    AND greatest(nvl(BAL_TOTAL,0),0) < 50000 THEN '4[2W,5W)'

    WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

    本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
    AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
    验证码:
    获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

    标签:

    Avatar photo

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表回复