谁能把这个sql优化下

[classnameextendsjava]

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条数据

[hebo2005]

贴执行计划，表结构、数据、索引情况，然后大家才好分析啊

[小灰狼W]

两个exists可以写到一起
(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))
效果如何还要看执行计划

[Adebayor]

帮顶

[mark620]

创建的索引列出来看看。

[classnameextendsjava]

把or改成union拆成3段 中间那个要25秒 但是时间好像花在group by上面 为什么前后两段的group by没有花多少时间呢 总共查出来才1200条数据

[KingSunSha]

先把查询改成Oracle的传统语法

SELECT   a.aaid, a.aaname, a.aaip, d.ddname, e.eename
       , 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;

[KingSunSha]

改掉把对F表的左联接,转换成UNION

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;

对查询做执行计划分析，把结果贴出来。

[王铎开]

G表有没有主键？

[王铎开]

G表有没有索引？

[shiyiwan]

#7

用了DISTINCT可以用UNION ALL了

[classnameextendsjava]

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
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秒左右

[classnameextendsjava]

搞错了
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的问题

[KingSunSha]

满足F.yyID=2条件的纪录有多少？join G表之后有多少记录？

[超叔csdn]

不知道这样是不是会快点？
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

[classnameextendsjava]

只能拆成小块写了