合 都是标量子查询惹的祸
系统又报了一个跑的慢的sql语句，看图就知道这个很恐怖的，已经跑了1天了，还需要跑6个月的时间，
把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'));**
简单分析下，查询的只有一个表，这就很奇怪了，不管这个表多么大，也不可能跑这么久的吧，但是有一个标量子查询，根据经验，如果标量子查询的表数据量很多，或者关联的列没有索引的话，标量子查询的性能将会是非常差的，我们由执行计划也可以看出标量子查询的表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*/
执行一下：
，发现这个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,+)'
