select count(*) from cccccc c, nnnnnn n
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time < sysdate
这个是sql当n表的数据量比较大的时候,这个语句要执行很多时间。看了下执行计划,对n表进行的是全表扫描,请教,应该咋个处理法注:n表有个唯一索引id。
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time < sysdate
这个是sql当n表的数据量比较大的时候,这个语句要执行很多时间。看了下执行计划,对n表进行的是全表扫描,请教,应该咋个处理法注:n表有个唯一索引id。
where c.batch_no = n.batch_no
and c.status = 1 and n.status = 1
and c.prepare_time < sysdate
*改成主件
大表放前面
先写关联
过滤记录多的放后面
而后修改查询如下:select count(1) from
(select batch_no from cccccc where status = 1) c,
(select batch_no from dddddd where status = 1) n
where c.batch_no = n.batch_no;
select count(*) from cccccc c, nnnnnn n
where c.status = 1 and n.status = 1 and c.batch_no = n.batch_no
and c.prepare_time < sysdate 可以这样修改一下:
select count(*) from cccccc c inner join nnnnnn n on c.batch_no = n.batch_no and n.status = 1
where c.status = 1 and c.prepare_time < sysdate
再分析索引
这样子后再执行你的sql, io 及速度应该快了很多
( select batch_no from nnnnn n where n.status = 1) a
inner join
( select batch_no from cccccc c where c.status = 1 and and c.prepare_time < sysdate ) b
on c.batch_no = n.batch_no
你看看这样怎么样?