因为数量很大,所以按以下语句查询非常慢,希望大家帮忙优化一下
select count(1)
from AA c
where c.id in (select pa.aa.id
from BB p,CC pa,DD pai,EE s, FF u,GG us,
HH att
where pai.ccid=pa.id and pai.bbid=p.id and pa.aaid=c.id
and p.steid = 1002
and p.statusid in (1, 2)
and s.attid = att.id
and us.sid = s.id
and us.uid = u.id
and att.siteno='NOJ'
)
FROM AA c
WHERE c.id IN (SELECT pa.aa.id
FROM BB p,CC pa,DD pai,EE s, FF u,GG us,HH att
WHERE pai.ccid=pa.id
AND us.uid = u.id
AND pai.bbid=p.id
AND pa.aaid=c.id
AND us.sid = s.id
AND s.attid = att.id
AND p.statusid in (1, 2)
AND p.steid = 1002
AND att.siteno='NOJ'
)
表连接的语句放在WHERE条件最上面,能滤掉最多记录的条件放最下面
FROM子句中表数据量小的表放后面,数据量大的表放在前面
from AA c
where exists (select 1
from BB p, CC pa, DD pai, EE s, FF u, GG us, HH att
where pai.ccid = pa.id
and pai.bbid = p.id
and pa.aaid = c.id
and p.steid = 1002
and p.statusid in (1, 2)
and s.attid = att.id
and us.sid = s.id
and us.uid = u.id
and att.siteno = 'NOJ'
and c.id =pa.aa.id )
这个最好用,你的内部查询关联表很多,如果这些表都很大,查询会非常慢,可以从BB和HH表入手,进行多个临时表优化。
FROM AA c
WHERE c.id IN (SELECT pa.aa.id
FROM BB p,CC pa,DD pai,EE s, FF u,GG us,HH att
WHERE pai.ccid=pa.id(+)
AND us.uid = u.id (+)
AND pai.bbid=p.id (+)
AND pa.aaid=c.id (+)
AND us.sid = s.id (+)
AND s.attid = att.id (+)
AND p.statusid in (1, 2)
AND p.steid = 1002
AND att.siteno='NOJ'
)
用join较好一些。