oracle数据库中现总共有四张表:acpf10 a,adpf10 b,capf10 c,capfp0 d,均是上百万条数据的,已建了索引,查询时需用到这几张表里的数据。sql查询语句如下:select cap0jb from capfp0 Where cap0cno=(
select ca10cno from capf10 where ca10cid =
(select ac10ctid from acpf10 where ac10id=
(select ad10id from adpf10 where ad10acno= '780010101010009333'))) and cap0jb in(1,2);
-查询所耗时间49.78second 太慢了,于是以表关联优化如下:
select d.cap0jb from acpf10 a,adpf10 b,capf10 c,capfp0 d
where a.ac10id=b.ad10id and c.ca10cid=a.ac10ctid and d.cap0cno=c.ca10cno and d.cap0jb in(1,2) and
b.ad10acno='780010101010009333';
-查询所耗时间:都等了十分钟了还在查询中。请问大神表作关联后查询速度反而会更慢了么?即使第一种方案作查询也要四十多秒时间太长了,有没有懂的可以给个建议让查询速度再快些?拜谢了,100分在线等!!
select ca10cno from capf10 where ca10cid =
(select ac10ctid from acpf10 where ac10id=
(select ad10id from adpf10 where ad10acno= '780010101010009333'))) and cap0jb in(1,2);
-查询所耗时间49.78second 太慢了,于是以表关联优化如下:
select d.cap0jb from acpf10 a,adpf10 b,capf10 c,capfp0 d
where a.ac10id=b.ad10id and c.ca10cid=a.ac10ctid and d.cap0cno=c.ca10cno and d.cap0jb in(1,2) and
b.ad10acno='780010101010009333';
-查询所耗时间:都等了十分钟了还在查询中。请问大神表作关联后查询速度反而会更慢了么?即使第一种方案作查询也要四十多秒时间太长了,有没有懂的可以给个建议让查询速度再快些?拜谢了,100分在线等!!
FROM capfp0 c0
AND cap0jb in(1,2)
WHERE EXISTS ( SELECT 1
FROM capf10 c10
WHERE c10.ca10cno = c0.cap0cno
AND EXISTS (SELECT 1
FROM acpf10 a10
WHERE a10.ac10ctid = c10.ca10cid
AND EXISTS (SELECT 1
FROM adpf10 f10
WHERE ad10acno= '780010101010009333'
AND ad10id = ac10id )))
create index capfp0_idx on capfp0(cap0jb,cap0cno);
acpf10表,对ac10id建立索引
capf10表,对ca10cid建立索引
capfp0表,对cap0cno建立索引
select ca10cno from capf10 where ca10cid =
(select ac10ctid from acpf10 where ac10id=
(select ad10id from adpf10 where ad10acno= '780010101010009333'))) and cap0jb =1
union all
select cap0jb from capfp0 Where cap0cno=(
select ca10cno from capf10 where ca10cid =
(select ac10ctid from acpf10 where ac10id=
(select ad10id from adpf10 where ad10acno= '780010101010009333'))) and cap0jb =2;
b.ad10acno='780010101010009333';
这两个条件互换一下位置试试
执行的时间应该近似等于分别执行各子查询的时间之和。为了找出瓶颈,我建议你把查询分开,一个一个执行,看是哪里执行得慢。