Question

oracle语句查询时间过长，请帮忙优化。谢谢！

select sum(a.C)
from tableA a,
(SELECT i.A, i.B
FROM tableB i
WHERE i.date >= to_date('2015-12-07', 'yyyy-mm-dd')
and i.date < to_date('2015-12-07', 'yyyy-mm-dd') + 1
AND i.B = '111111') i
where 1 = 1
and i.A = a.A
and i.B = a.B
and a.B = '111111'

Answer 1

with t as (
select o.menuid
from t_sys_operation o inner join t_sys_role_operation ro on o.sysname = ro.sysname and o.powerid = ro.powerid and o.action = ro.action
inner join t_sys_role_user u
on ro.roleid=u.roleid
where u.userid = '10135089047031902'
)
select *
from t_sys_menu t1
where exists(select 1 from t_sys_menu t2 where t1.id=t2.parentid and
exists(select 1 from t where t.menuid=t2.id))

Answer 2

不要用子查询，这里需要优化。
用EXISTS会好很多。
select sum(a.C) from tableA a where
EXISTS (SELECT i.A, i.B FROM tableB i
WHERE i.date >= to_date('2015-12-07', 'yyyy-mm-dd')
and i.date < to_date('2015-12-07', 'yyyy-mm-dd') + 1
AND i.B = '111111' and i.A = a.A
and i.B = a.B
and a.B = '111111' )

追问

谢谢，不过你说的这种也在我尝试的诸多方法当中。所以，只能把分给上面给我提供了新思路的朋友了。

追答

好的