大表关联查询的效率问题求教

你的过滤条件很简单，当然会导致很多记录被select，当然会走全表扫描。关键在于如何限制你返回的记录数，增加适当的索引即可。

走索引不会比全表扫描还慢的.
可能你索引建得不好吧.
WHERE条件后的字段基本上都要建索引.
而且如果是建组合索引的话
出现WHERE条件后的第一个字段必须是组合索引的第一项.
这样索引才能起到作用.
像你这种情况,除了建索引,好像没有其它什么改进的方法.
期待其它朋友有好的见解.

把索引建在a.id和b.id上，
这样建不可能比不建慢的，
试试看，

看看执行计划，是否用到索引。虽然你加了索引，但是oracle不一定会使用到，需要强制使用。如果得到的结果数据量很大时，试着加 all_rows 提示。如果表分布在多个磁盘空间，可以试试加 parallel 提示如果你的内存够大的话，可以用 cash 提示把表全load到内存中操作。
其实我觉得你的时间还算是比较正常的。

应采用子查询会快点.如:SELECT * FROM A WHERE *** = (SELECT * FROM B)
或:
SELECT * FROM A WHERE (NOT) EXISTS (SELECT * FROM B WHERE A.**=B.**)
等子查询

如果满足过滤条件的数据比较很多，走不走索引效率都相差不大。问题是我的过滤条件无法再过滤掉更多的数据，否则不符合业务逻辑。
我也试过了where条件中加入rownum=1来使返回的结果简化，可是查询效率跟之前相差不多。我现在的业务需求是只要能够知道“是否存在”满足条件的记录就行了，其实根本不关心有多少条记录满足条件，所以我认为应该还有更好的方法来实现。

select 1 from dual
where exists (select 1 from a,b where a.id=b.id and ...);

TO KingSunSha(弱水三千) :该查询确实能够大幅提高查询效率（通常不超过30妙），只是比我预期的效果还要差一些，如果能有更好的方法把查询时间提高到5妙以内就好了

你先执行一下trace看看statistics

我看过执行计划了，成本主要消耗在HASH JOIN和NESTED LOOPS上

TO KingSunSha(弱水三千) :最初用“select count(a.id)”查询时a表走全表扫描，但是用了你的方法后便不会全表扫描，能解释一下原因吗？（查询条件都是相同的）

select count()需要对所有满足条件的纪录作计数，所以必须找出所有满足条件的纪录，当oracle cbo计算出全表扫描的消耗少于走index时，执行计划就会走全表扫描。而exists条件之需要找到第一条满足条件的纪录就返回，而不需要统计所有满足条件的纪录，cbo根据statistics计算出走index消耗更少。关于全表扫描还是index扫描，取决于通过index访问的纪录数占表中总纪录数的百分比、数据分布、index中列的uniqueness等很多因素，走index并不是总是有益的。某些情况下，走全表扫描效率远远高于index扫描。所以要具体情况具体分析。你把表结构、index结构、数据分布（analyze的结果）、典型的查型条件等内容贴上来，我们再探讨一下方案。

需要详细的SQL语句才能帮你分析.同时简单描述下你的索引字段以及查询条件,表是否经常需要update/delete?还是仅仅用来select/insert? KingSunSha(弱水三千) 提出的用exists是可行的方法,但是如果要提高到5秒以内......楼主还是把问题介绍详细些.

heyixiang(子豚の愛人)，KingSunSha(弱水三千) ：两个表a,b是主子表关系，其中a有100万行，b有400万行，这两个表主要执行的sql为select,update,insert，几乎没有delete我的查询语句很简单：select count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'只不过满足查询条件的记录很多，有40万行。
该查询用到的索引如下：a表有复合索引：(id,dept,flag)，b表索引：(productid)当然，这两个表还建有其他的索引。
用了exists查询方法后效率确实提高了很多，但还没有到令人满意的程度。

请贴出执行那个select count的执行计划。

根据你的SQL语句select count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'可以看到a表的所有字段都在索引中，那你可以试试索引扫描。
select /*+ index_ffs(a 索引名称)*/ count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'
你的完整的SQL语句是不是就是这个？别又删删减减啦，对于语句优化，不怕你写的问题复杂，就怕你写的简单。

a表有复合索引：(id,dept,flag)
根据你的sql，这个索引被充分利用，非常好b表索引：(productid)
非常有可能是这个索引降低了你的效率，如果用(id, productid)做索引，当然是最理想的，但即使单单用（id）索引，也应该比(productid)效率高。100万、400万纪录只能算是很普通的表，我认为你的这句sql改成exists之后如果执行计划正确的话，运行时间应该在秒级，2-3秒钟甚至更短。

a表100W，b表400W，a.id只A表的主建，b.productid是B表的主键，b.id是外部关键字。不知道楼主的表关系是不是我猜测的这个样子。如果是这样的话，B表只有productid做索引是正常的。所以需要看你b.productid='ABC'这个条件筛选出来的记录数大概占了多大的百分比，执行计划是否走了索引扫描？你建了索引ORACLE并不一定用了。另外，A表我不赞成把flag也放到索引中，毕竟flag只有'Y'和'N'两个情况，这样对索引的大小产生影响，索引过大的话扫描话的时间也会增加，索引的合理性也是很重要的。把你的执行计划贴出来吧，我感觉主要问题出在B表上导致时间过长。

KingSunSha(弱水三千) 所说的用exists代替count绝对是必要的。

用order by
的主表ID或者你字段的时间排序试试
可以加快数据量的读取速度时间的优先级最高

用了order by 开销应该更大才是

关联查询要先对所有需要关联的表进行预算.select a.id from a where a.id exists(select b.id from b where b.id exits(....))最后做where x.id = y.id时,应满足x,y两个的数据量是最小的,那么关联查询的乘积才是最小的.查询速度也是最快的.

最终终于用KingSunSha(弱水三千) 的查询方法搞定了，当初之所以效率低可能是因为建了索引之后没有更新oracle的统计信息，现在通过下面的查询
select 1 from dual
where exists (select 1 from a,b where a.id=b.id and ...);
已经使查询速度提高到了毫秒级。以前对sql编写不是很注意，导致很多查询效率低下，在用户数据量小的情况下暴露不出来，随着数据量的增长，很多看似简单的sql都可能出现很大的效率问题，以后真得好好注意了。
多谢各位的指导！

heyixiang(子豚の愛人):你的QQ我记下了，以后有什么问题肯定还是要向你们请教的，我现在只是菜鸟级的水平，问题多多，呵呵

调试易

大表关联查询的效率问题求教

解决方案 »