declare
type ref_cursor is ref cursor;
type type_rowid is table of rowid;
v_cursor ref_cursor;
v_rowid type_rowid;
begin
open v_cursor for ' select rowid rid from 表名 order by rowid';
loop
fetch v_cursor bulk collect into v_rowid limit 10000;
if v_rowid.count>0 then
forall i in v_rowid.first..v_rowid.last
delete from 表名
where rowid = v_rowid(i);
commit;
end if;
exit when v_cursor%notfound;
end loop;
close v_cursor;
end;
Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。
type ref_cursor is ref cursor;
type type_rowid is table of rowid;
v_cursor ref_cursor;
v_rowid type_rowid;
begin
open v_cursor for ' select rowid rid from 表名 order by rowid';
loop
fetch v_cursor bulk collect into v_rowid limit 10000;
if v_rowid.count>0 then
forall i in v_rowid.first..v_rowid.last
delete from 表名
where rowid = v_rowid(i);
commit;
end if;
exit when v_cursor%notfound;
end loop;
close v_cursor;
end;
/
通过bulk collect减少loop处理的开销,
使用Bulk Collect提高Oracle查询效率
Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时,所有的into变量都必须是collections.
Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared
Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk
Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。
另外,除了Bulk Collect批查询外,我们还可以使用FORALL语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。