select b.id,a.other from b,a where b.x>=a.start and b.x<=a.end
执行计划:
nested loops
table access full a 表
table access by index rowid b表
index range scan b表索引
这种情况下,b表如果记录数很少,响应很慢,50条9秒,如果是800条左右则响应时间是0.2秒左右
select b.id,a.other from a,b where b.x>=a.start and b.x<=a.endnested loops
table access full b 表
table access by index rowid a表
index range scan a表索引
这种情况下,b表50到800条记录响应都很慢,30秒左右select b.id,a.other from b,a where b.x>=a.start and b.x<=a.end and a.start>0
将a.start>0条件加上,
执行计划,是访问两个表的索引,但是响应时间仍然很慢
执行计划:
nested loops
table access full a 表
table access by index rowid b表
index range scan b表索引
这种情况下,b表如果记录数很少,响应很慢,50条9秒,如果是800条左右则响应时间是0.2秒左右
select b.id,a.other from a,b where b.x>=a.start and b.x<=a.endnested loops
table access full b 表
table access by index rowid a表
index range scan a表索引
这种情况下,b表50到800条记录响应都很慢,30秒左右select b.id,a.other from b,a where b.x>=a.start and b.x<=a.end and a.start>0
将a.start>0条件加上,
执行计划,是访问两个表的索引,但是响应时间仍然很慢
先
analyze table a compute statistics;
analyze table b compute statistics;
再执行试试