update A set Status=1 WHERE exists (select * FROM (select a.Code FROM A a,B b WHERE rownum<=5 and a.ProdCode in ('013') and a.SNo=b.ID a.Status=0 and a.LevelNo=1 order by a.Code ) b WHERE A.Code=b.Code)
这个SQL我再9i上运行大约用了6分钟,可是select a.Code FROM A a,B b WHERE rownum<=5 and a.ProdCode in ('013') and a.SNo=b.ID a.Status=0 and a.LevelNo=1 order by a.Code 这个查询语句才不到1秒就运行完了其实我想要的效果就是更新表A中CODE字段的值为下面 SQL 查询出来数据,然后更新Status为1,不知道该怎么写更快点??还有不能用IN,我用in测试过,更慢select a.Code FROM A a,B b WHERE rownum<=5 and a.ProdCode in ('013') and a.SNo=b.ID a.Status=0 and a.LevelNo=1 order by a.Code
解决方案 »
- sql查询条件的组合,各位朋友,求帮忙,谢谢!
- 在装oracle 11g的时候出现 由于以下错误,Enterprise Manager 配置失败
- REDO日志大小
- 查詢兩周之間的數據
- Oracle10g Client的安装和配置
- 有sql语句优化经验的请看看
- 如何实现oracle中自增序列的重排序?
- pb调用存储过程,编译时报ora-20003错误?
- 导入导出数据问题
- 如何把字符串"6/20/01 4:56:16 PM"转换成为Oracle的日期型?jiezhi(Niu)大哥在么?
- imp 导入表数据的问题
- 我有一个oracle应用,需要对表先查询若没有记录再插入数据,否则更新,这样造成了速度特别慢,请问该如何优化?
update A set Status=1 WHERE exists
(select count(1) FROM B b WHERE A.Code=b.Code)
WHERE rownum<=5 and a.ProdCode in ('013') and a.Status=0 and a.LevelNo=1;
(select count(1) FROM B b WHERE A.Code=b.Code)
and rownum<=5 and a.ProdCode in ('013') and a.Status=0 and a.LevelNo=1;
select * FROM (select a.Code FROM A a,B b WHERE rownum<=5 and a.ProdCode in ('013') and a.SNo=b.ID a.Status=0 and a.LevelNo=1 order by a.Code ) b WHERE A.Code=b.Code
好像只有b表,没有A表啊
(select a.Code FROM B b WHERE A.Code=b.Code)
WHERE rownum<=5 and a.ProdCode ='013' and a.Status=0 and a.LevelNo=1;