UPDATE A
SET A.YE=(SELECT SUM(NVL(B.J_F,0.00))
FROM B
WHERE A.BH=B.BH
AND B.BB=0
AND (B.SCBJ IS NULL OR B.SCBJ ='N')
AND B.LRRQ<'2010-01-01')
WHERE A.BB=0 AND A.YEAR=2010 AND A.MONTH=1 AND A.GSID IS NULL;测试数据A表大约5000条数据,B表大约50000条数据,B表数据量随时间增,可能达到500000条
按说数据库量不大,可是这个简单的语句在PLUS里竟然要执行10多分钟,现在对表没建任何索引
请各位帮忙看一下怎么优化最好
SET A.YE=(SELECT SUM(NVL(B.J_F,0.00))
FROM B
WHERE A.BH=B.BH
AND B.BB=0
AND (B.SCBJ IS NULL OR B.SCBJ ='N')
AND B.LRRQ<'2010-01-01')
WHERE A.BB=0 AND A.YEAR=2010 AND A.MONTH=1 AND A.GSID IS NULL;测试数据A表大约5000条数据,B表大约50000条数据,B表数据量随时间增,可能达到500000条
按说数据库量不大,可是这个简单的语句在PLUS里竟然要执行10多分钟,现在对表没建任何索引
请各位帮忙看一下怎么优化最好
SET A.YE=(SELECT SUM(NVL(B.J_F,0.00))
FROM B
WHERE A.BH=B.BH )
就是把别的条件都去掉,如上,速度也很慢,这个ORACLE和MS/SQL怎么差距这么大
SET A.YE=(SELECT SUM(NVL(B.J_F,0.00))
FROM B
WHERE A.BH=B.BH
AND B.BB=0
AND (B.SCBJ IS NULL OR B.SCBJ ='N')
AND B.LRRQ <'2010-01-01')
WHERE exists ( SELECT 1 FROM B
WHERE A.BH=B.BH
AND B.BB=0
AND (B.SCBJ IS NULL OR B.SCBJ ='N')
AND B.LRRQ <'2010-01-01'
)AND A.BB=0 AND A.YEAR=2010 AND A.MONTH=1 AND A.GSID IS NULL;
速度提高了,但是还是时间超过1S,不知道还有没有别的办法