标量子查询SQL改写一则（包括WITH的改写）

来源：互联网发布：网站源码音乐连接编辑：程序博客网时间：2024/06/06 01:52

原SQL，执行了6.8小时后报ora-01555错误无法完成：

select t.operid || '|' || sum(score) || '|' ||
nvl((select sum(score)
from cs_score_operdayscore t1
where t1.operid = t.operid
and t1.scoredate > to_date(20120801, 'yyyymmdd')
and t1.scoreid in
(select dictname
from dict_item
where groupid in ('SHOPSCOREEXCHG',
'SCOREEXCHGFEE',
'SCOREEXCHGSELFBUSI',
'SCOREEXCHGSUPPERPRESENT',
'SCOREEXCHGXHNEWS',
'SCOREEXCHGMIFI',
'SCOREEXCHGTEMPPRESENT'))),
0) || '|' ||
nvl((select sum(score)
from cs_score_operdayscore t1
where t1.operid = t.operid
and t1.scoredate > to_date(20120801, 'yyyymmdd')
and t1.scoreid in
(select dictname
from dict_item
where groupid = 'NEWBUSISCORE')),
0)
from cs_score_operdayscore t
where scoredate > to_date(20120801, 'yyyymmdd')
group by operid;

sql monitor采集的执行计划如下：

问题：

cs_score_operdayscore是一张大表（1亿条以上），从谓词条件看，将近3年的数据基本上不会过滤掉多少记录，在如此大的一张表上，使用标量子查询（即select 的column列表中使用了select子句），性能是非常非常差的，需要通过外关联的方式进行改写才能提高性能。

而同一张表在标量子查询里面又被用到，这种情况可以使用WITH来进行改写；因为这个SQL是大表做统计分析，一般是DW系统或是OLTP系统晚上操作，为了缩短响应时间和资源消耗，一般建议增加并行操作，最终改写后的SQL如下：

with main as
(select operid,score,scoreid from cs_score_operdayscore where scoredate > to_date(20120801, 'yyyymmdd') )
select /*+ parallel(8) */
t.operid || '|' || sum_t || '|' ||nvl(sum_t1,0) || '|' ||nvl(sum_t2,0) from
(select operid,sum(score) sum_t from main group by operid) t,
(select operid,sum(score) sum_t1 from main where scoreid in
(select /*+ full(dict_item) */dictname
from dict_item
where groupid in ('SHOPSCOREEXCHG',
'SCOREEXCHGFEE',
'SCOREEXCHGSELFBUSI',
'SCOREEXCHGSUPPERPRESENT',
'SCOREEXCHGXHNEWS',
'SCOREEXCHGMIFI',
'SCOREEXCHGTEMPPRESENT')
)group by operid
) t1,
(select operid,sum(score) sum_t2 from main where scoreid in
(select /*+ full(dict_item) */dictname
from dict_item
where groupid = 'NEWBUSISCORE')
group by operid
) t2
where t.operid=t1.operid(+) and
t.operid=t2.operid(+) ;

这个SQL最终的执行时间是4分钟（并行度设置为8），下面是部分执行计划的内容：

0 0