在Oracle性能优化时,用exists替代in,用表链接替代exists,关于前者,一般效果比较明显,exists效率明显比in高,但是如果要想表连接的效率比exists高,必须在from子句中,将记录多的表放在前面,记录少的表放后面。
关于select... bulk collect into ... limit ...或fetch... bulk collect into ... limit ...句型:
在使用如上句型时,通常我们都会用for或forall循环进行insert/update/delete操作。
for/forall循环方法有好几种,如
第1种:
for tp in tmp.FIRST.. tmp.LAST loop .... end loop;
第2种:
for tp in 1 .. tmp.COUNT loop .... end loop;
第3种:
for tp in indecs of tmp loop .... end loop;
上面的第1种方法有一个致使的弱点,就是在select... bulk collect into ... limit ...或fetch... bulk collect into ... limit ...没有取到数据时,如果没有exit,则第一种方法会报错:ORA-06502: PL/SQL: numeric or value error。因为tmp.FIRST访问不存在,为空值。必须对错误进行错误处理。而在嵌套的循环中,内层的循环是不能用exit的,所有必然遇到这种错误。
第2种方法不会出现这种问题,第3种方法没有试过。
借鉴网上的做法,给出一种使用绑定变量的批量删除数据的存储过程:
PROCEDURE RemoveBat2DjaRecords(参数)
AS type RowIdArray is table of rowid index by binary_integer;
rowIds RowIdArray;
BEGIN loop select rowid BULK COLLECT into rowIds from 表名
where 查询条件 and rownum <= 1000;
exit when SQL%NOTFOUND;
forall k in 1 .. rowIds.COUNT
delete from 表名 where rowid = rowIds(k);
commit;
end loop;
EXCEPTION when OTHERS then rollback;
END RemoveBat2DjaRecords;
上面的1000条是一个可以设定的数,根据你的服务器性能可以扩大或缩小。