select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
count(distinct(f.ooID))
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(F.xxID=A.aaID and F.yyID=1)
or
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
or
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
where
C.ccID= ?
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName这个sql执行需要30秒左右
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)这个花的时间较长 大约25秒 G表里有5W条数据
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
or
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
-->
exists(select 1 from g,h where h.ggid(+)=g.ggid
and (G.aaID=A.aaID and G.ggID=F.xxID and F.yyID=2)
or (G.aaID=A.aaID and H.hhID=F.xxID and F.yyID=3 and h.ggid is not null))
效果如何还要看执行计划
, COUNT (DISTINCT (f.ooid))
FROM a, b, c, d, e, f
WHERE a.bbid = b.bbid
AND b.ccid = c.ccid
AND a.ddid = d.ddid(+)
AND a.eeid = e.eeid(+)
AND ( f.yyid(+) = 1 AND (a.aaid = f.xxid(+) ) -- 既然count ooid, 这里为什么要用左联接?
OR (f.yyid = 2 AND EXISTS (SELECT 1
FROM g
WHERE g.aaid = a.aaid AND g.ggid = f.xxid))
OR ( f.yyid = 3
AND EXISTS (
SELECT 1
FROM h, g
WHERE h.ggid = g.ggid
AND g.aaid = a.aaid
AND h.hhid = f.xxid)))
AND c.ccid = :1
GROUP BY a.aaid, a.aaname, a.aaip, d.ddname, e.eename;
SELECT aaid, aaname, aaip, ddname, eename, COUNT (ooid)
FROM (SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid
FROM a, b, c, d, e, f
WHERE a.bbid = b.bbid
AND b.ccid = c.ccid
AND a.ddid = d.ddid(+)
AND a.eeid = e.eeid(+)
AND a.aaid = f.xxid
AND f.yyid = 1
AND c.ccid = :1
UNION
SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid
FROM a, b, c, d, e, f, g
WHERE a.bbid = b.bbid
AND b.ccid = c.ccid
AND a.ddid = d.ddid(+)
AND a.eeid = e.eeid(+)
AND f.yyid = 2
AND g.aaid = a.aaid
AND g.ggid = f.xxid
AND c.ccid = :1
UNION
SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid
FROM a, b, c, d, e, f, g, h
WHERE a.bbid = b.bbid
AND b.ccid = c.ccid
AND a.ddid = d.ddid(+)
AND a.eeid = e.eeid(+)
AND f.yyid = 3
AND h.ggid = g.ggid
AND g.aaid = a.aaid
AND h.hhid = f.xxid
AND c.ccid = :1)
GROUP BY aaid, aaname, aaip, ddname, eename;
对查询做执行计划分析,把结果贴出来。
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
1200条数据 0.1秒
加上
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
还是0.1秒左右select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
0.2秒左右
加上group by
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
25秒select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
0.2秒左右
加上group by 还是0.2秒左右
select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
0.2秒左右(这里也是25秒 0.2秒时1条的时间)
加上group by
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
25秒
应该是关联 (exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2的问题
left join F on
(F.xxID=A.aaID and F.yyID=1)
or
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
or
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
改为
left join (
select distinct f.ooid,f.xxid,
from f
where F.yyID=1
union all
select f.ooID,G.aaID
from f join g on f.xxID=g.ggID
where f.yyID=2
union all
select f.ooid,g.aaID
from f join h on f.xxid=h.hhid
join g on h.ggid=g.ggid
where f.yyid=3
) f on A.aaid=f.xxid