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

Question

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

Answer 1

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