SQL性能优化,对这方面不太懂。。求教大神

select to_char(t.currmonthcount / 10000, 'fm9999990.9990'),
to_char(t.currmonthmoney / 10000, 'fm9999990.90'),
to_char(t.premonthcount / 10000, 'fm9999990.9990'),
to_char(t.premonthmoney / 10000, 'fm9999990.90'),
to_char(t.curryearcount / 10000, 'fm9999990.9990'),
to_char(t.curryearmoney / 10000, 'fm9999990.90'),
to_char(t.preyearcount0 / 10000, 'fm9999990.9990'),
to_char(t.preyearmoney0 / 10000, 'fm9999990.90'),
to_char(t.preyearcount / 10000, 'fm9999990.9990'),
to_char(t.preyearmoney / 10000, 'fm9999990.90')
from (select '00' as salechnl,'00' as risktype,
(select sumzccontanaly('201706', a.managecom, '', '0', '4') from dual) currmonthcount,
(select sumzccontanaly('201706', a.managecom, '', '0', '5') from dual) currmonthmoney,
(select sumzccontanaly('201705', a.managecom, '', '0', '4') from dual) premonthcount,
(select sumzccontanaly('201705', a.managecom, '', '0', '5') from dual) premonthmoney,
(select sumzccontanaly('201706', a.managecom, '', '1', '4') from dual) curryearcount,
(select sumzccontanaly('201706', a.managecom, '', '1', '5') from dual) curryearmoney,
(select sumzccontanaly('201606', a.managecom, '', '0', '4') from dual) preyearcount0,
(select sumzccontanaly('201606', a.managecom, '', '0', '5') from dual) preyearmoney0,
(select sumzccontanaly('201606', a.managecom, '', '1', '4') from dual) preyearcount,
(select sumzccontanaly('201606', a.managecom, '', '1', '5') from dual) preyearmoney
from (select '8605' managecom from dual) a
union all
select a.salechnl salechnl, a.salechnl risktype,
(select ZccontAnaly('201706', a.managecom,a.salechnl, '', '0', '2') from dual) currmonthcount,
(select ZccontAnaly('201706', a.managecom, a.salechnl, '', '0', '3') from dual) currmonthmoney,
(select ZccontAnaly('201705', a.managecom, a.salechnl,'','0', '2')from dual) premonthcount,
(select ZccontAnaly('201705', a.managecom, a.salechnl, '', '0', '3')from dual) premonthmoney,
(select ZccontAnaly('201706', a.managecom,a.salechnl,'','1','2')from dual) curryearcount,
(select ZccontAnaly('201706', a.managecom,a.salechnl, '', '1','3') from dual) curryearmoney,
(select ZccontAnaly('201606',a.managecom,a.salechnl, '', '0','2') from dual) preyearcount0,
(select ZccontAnaly('201606',a.managecom, a.salechnl, '', '0', '3') from dual) preyearmoney0,
(select ZccontAnaly('201606', a.managecom, a.salechnl, '', '1', '2')from dual) preyearcount,
(select ZccontAnaly('201606', a.managecom,a.salechnl, '', '1', '3') from dual) preyearmoney
from (select code salechnl, '8605' managecom from ldcode1 where codetype = 'riskcodegroup' and code in ('01', '07')) a
union all
select a.salechnl salechnl,a.risktype risktype,
(select ZccontAnaly('201706', a.managecom,a.salechnl, a.risktype,'0','0') from dual) currmonthcount,
(select ZccontAnaly('201706', a.managecom,a.salechnl, a.risktype, '0', '1') from dual) currmonthmoney,
(select ZccontAnaly('201705', a.managecom, a.salechnl, a.risktype, '0', '0')from dual) premonthcount,
(select ZccontAnaly('201705',a.managecom,a.salechnl,a.risktype, '0','1')from dual) premonthmoney,
(select ZccontAnaly('201706',a.managecom,a.salechnl,a.risktype,'1','0')from dual) curryearcount,
(select ZccontAnaly('201706',a.managecom,a.salechnl,a.risktype, '1','1')from dual) curryearmoney,
(select ZccontAnaly('201606',a.managecom,a.salechnl,a.risktype,'0','0')from dual) preyearcount0,
(select ZccontAnaly('201606',a.managecom,a.salechnl,a.risktype,'0','1')from dual) preyearmoney0,
(select ZccontAnaly('201606',a.managecom,a.salechnl,a.risktype,'1','0')from dual) preyearcount,
(select ZccontAnaly('201606',a.managecom,a.salechnl,a.risktype,'1','1')from dual) preyearmoney
from (select code salechnl,code1 risktype,codename codename,'8605' managecom from ldcode1 where codetype = 'riskcodegroup'
and code in ('02', '03')) a
union all
select a.salechnl salechnl,a.risktype risktype,
(select sumzccontanaly('201706',a.managecom,a.salechnl,'0','0')from dual) currmonthcount,
(select sumzccontanaly('201706',a.managecom,a.salechnl, '0', '1')from dual) currmonthmoney,
(select sumzccontanaly('201705',a.managecom,a.salechnl,'0','0')from dual) premonthcount,
(select sumzccontanaly('201705',a.managecom, a.salechnl, '0', '1')from dual) premonthmoney,
(select sumzccontanaly('201706',a.managecom,a.salechnl,'1','0')from dual) curryearcount,
(select sumzccontanaly('201706',a.managecom,a.salechnl,'1','1')from dual) curryearmoney,
(select sumzccontanaly('201606',a.managecom,a.salechnl,'0','0')from dual) preyearcount0,
(select sumzccontanaly('201606',a.managecom,a.salechnl,'0','1') from dual) preyearmoney0,
(select sumzccontanaly('201606',a.managecom, a.salechnl, '1','0')from dual) preyearcount,
(select sumzccontanaly('201606', a.managecom,a.salechnl,'1','1')from dual) preyearmoney
from (select salechnl salechnl,'00' risktype,'' codename,'8605' managecom from (select '02' salechnl from dual
union all
select '03' salechnl from dual)) a) t
order by t.salechnl, t.risktype

3个回答

尽量不要用子查询,用left join代替

用explain等查看执行计划。主要使一些查询负担中的,尝试建立索引等来提升速度

建议使用多表查询代替子查询

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!