select cardrptactorg.orgno,
sum(cardrptactorg.RPTAMT10) as aa,
NVL(sum((select sum(t1.rptnum10 - 1)
from cardrptacttlr t1
where t1.rptnum10 <> '0'
and t1.tlrtype = 'A'
and t1.orgno in
(SELECT DISTINCT toli.orgno
FROM orglvinfo toli
START WITH (toli.orgno in
(cardrptactorg.orgno))
CONNECT BY NOCYCLE
PRIOR toli.orgno =
toli.porgno)
and t1.actdate =
cardrptactorg.actdate)),
'0') as bb,
sum(cardrptactorg.RPTAMT0) as cc,
NVL(sum((select sum(t.rptnum0 - 1)
from cardrptacttlr t
where t.rptnum0 <> '0'
and t.tlrtype = 'A'
and t.orgno in
(SELECT DISTINCT toli.orgno
FROM orglvinfo toli
START WITH (toli.orgno in
(cardrptactorg.orgno))
CONNECT BY NOCYCLE
PRIOR toli.orgno =
toli.porgno)
and t.actdate =
cardrptactorg.actdate)),
'0') as dd,
sum(cardrptactorg.RPTAMT11) as ee,
sum(cardrptactorg.RPTNUM11) as ff
from cardrptactorg
where 1 = 1
and cardrptactorg.orgno in (020)
and cardrptactorg.ACTDATE <= '20141226'
GROUP by cardrptactorg.orgno
效率非常低，该怎么优化一下？？？？
------解决思路----------------------
orgno
ACTDATE
列上建立复合索引试试
------解决思路----------------------
看了下你的两个子查询语句基本是一样的，可以把你的两个子查询写成一个查询放在from后面与cardrptactorg进行连接
先试试吧，上面仅从语句角度分析，如果还慢的话，请提供执行计划，根据执行计划进行优化
------解决思路----------------------
执行计划， 表结构贴出来。。。。
------解决思路----------------------
where t.rptnum0 <> '0'
这种都改成where t.rptnum0 <> 0
你还是把表结构和执行计划贴出来， 让大家一起帮你分析吧。
------解决思路----------------------
分析函数使用改写这段 NVL(sum((select sum(t.rptnum0 - 1)
不要这么多SQL全圈套在SELECT里面，如果里面数据量大你能快才怪。
------解决思路----------------------
in 换成 exists 简历符合索引 像这样的cardrptactorg.orgno in (020) 干嘛不直接用等于？