SQL语句中 group by 的 优化问题 在线等

[starwang]

有一个查询
SELECT bid,logid FROM BCARDTABLE
WHERE bid IN
(SELECT MAX(bid) FROM BCARDTABLE WHERE ENABLEFLAG=0 GROUP BY logid)
执行效率很低，大约5秒可以查询一次1万条记录的表。
不知道该怎么优化，
改写为
select a.bid,logid
from
(select bid,logid from BCARDTABLE) a,
(SELECT MAX(bid) bid FROM BCARDTABLE ENABLEFLAG=0 GROUP BY logid ) b
where a.blackid=b.blackid
效果一样
看了一下，主要是SELECT MAX(bid) FROM BCARDTABLE WHERE ENABLEFLAG=0 GROUP BY logid 执行速度很慢，感觉可能是group by 的原因。
请问是否还有别的优化方法。
谢谢！

[starwang]

请给个意见！

[kaoziji]

把explain plan贴出来看看

[kinglht]

SELECT bid,logid FROM BCARDTABLE a
WHERE exists
(select 'a' from
(SELECT MAX(bid) FROM BCARDTABLE WHERE ENABLEFLAG=0 GROUP BY logid) t
where a.bid=t.bid)
试试看，实在不行只能对表数据量做优化了！

[kinglht]

SELECT max(bid),logid FROM BCARDTABLE where ENABLEFLAG=0
group by logid
这个就应该可以了吧，你上面访问两遍肯定要慢啊

[lijianfeng_haier]

SELECT a.bid, logid
FROM BCARDTABLE a,
(SELECT MAX(bid) bid FROM BCARDTABLE ENABLEFLAG = 0 GROUP BY logid) b
WHERE a.blackid = b.blackid

[starwang]

谢谢大家！