浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献
这种在pl/sql引擎和sql引擎之间的控制转移叫做上下文却换，每次却换时，都有额外的开销
请看下图：
但是，forall和bulk collect可以让pl/sql引擎把多个上下文却换压缩成一个，这使得在pl/sql中的要处理多行记录的sql语句执行的花费时间骤降
请再看下图：
下面详解这爷俩
㈠ 通过bulk collect 加速查询
⑴ bulk collect 的用法
采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理
可以在select into ，fetch into ， returning into语句使用bulk collect
注意在使用bulk collect时，所有的into变量都必须是collections
举几个简单例子：
① 在select into语句中使用bulk collect
declare
type sallist is table of employees.salary%type;
sals sallist;
begin
select salary bulk collect into sals from employees where rownum<=50;
--接下来使用集合中的数据
end;
/
② 在fetch into中使用bulk collect
declare
type deptrectab is table of departments%rowtype;
dept_recs deptrectab;
cursor cur is select department_id,department_name from departments where department_id>10;
begin
open cur;
fetch cur bulk collect into dept_recs;
--接下来使用集合中的数据
end;
/
③ 在returning into中使用bulk collect
create table emp as select * from employees;
declare
type numlist is table of employees.employee_id%type;
enums numlist;
type namelist is table of employees.last_name%type;
names namelist;
begin
delete emp where department_id=30
returning employee_id,last_name bulk collect into enums,names;
dbms_output.put_line('deleted'||sql%rowcount||'rows:');
for i in enums.first .. enums.last
loop
dbms_output.put_line('employee#'||enums(i)||':'||names(i));
end loop;
end;
/
deleted6rows:
employee#114:raphaely
employee#115:khoo
employee#116:baida
employee#117:tobias
employee#118:himuro
employee#119:colmenares
eate table emp as select * from employees;declare type numlist is table of employees.employee_id%type; enums numlist; type namelist is table of employees.last_name%type; names namelist;begin delete emp where department_id=30 returning employee_id,last_name bulk collect into enums,names; dbms_output.put_line('deleted'||sql%rowcount||'rows:'); for i in enums.first .. enums.last loop dbms_output.put_line('employee#'||enums(i)||':'||names(i)); end loop;end;/deleted6rows:employee#114:raphaelyemployee#115:khooemployee#116:baidaemployee#117:tobiasemployee#118:himuroemployee#119:colmenares
⑵ bulk collect 对大数据delete update的优化
这里举delete就可以了，update同理
举个案例：
需要在一个1亿行的大表中，删除1千万行数据
需求是在对数据库其他应用影响最小的情况下，以最快的速度完成
如果业务无法停止的话，可以参考下列思路：
根据rowid分片、再利用rowid排序、批量处理、回表删除
在业务无法停止的时候，选择这种方式，的确是最好的
一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力
我在做大dml时，通常选择一两千行一提交
选择业务低峰时做，对应用也不至于有太大影响
代码如下：
declare
--按rowid排序的cursor
--删除条件是oo=xx，这个需根据实际情况来定
cursor mycursor is select rowid from t where oo=xx order by rowid;
type rowid_table_type is table of rowid index by pls_integer;
v_rowid rowid_table_type;
begin
open mycursor;
loop
fetch mycursor bulk collect into v_rowid limit 5000;--5000行提交一次
exit when v_rowid.count=0;
forall i in v_rowid.first..v_rowid.last
delete t where rowid=v_rowid(i);
commit;
end loop;
close mycursor;
end;
/
⑶ 限制bulk collect 提取的记录数
语法：
fetch cursor bulk collect into ...[limit rows]；
其中，rows可以是常量，变量或者求值的结果是整数的表达式
假设你需要查询并处理1w行数据，你可以用bulk collect一次取出所有行，然后填充到一个非常大的集合中
可是，这种方法会消耗该会话的大量pga，app可能会因为pga换页而导致性能下降
这时，limit子句就非常有用，它可以帮助我们控制程序用多大内存来处理数据
例子：
declare
cursor allrows_cur is select * from employees;
type employee_aat is table of allrows_cur%rowtype index by binary_integer;
v_emp employee_aat;
begin
open allrows_cur;
loop
fetch allrows_cur bulk fetch into v_emp limit 100;
/*通过扫描集合对数据进行处理*/
for i in 1 .. v_emp.count
loop
upgrade_employee_status(v_emp(i).employee_id);
end loop;
exit when allrows_cur%notfound;
end loop;
close allrows_cur;
end;
/
⑷ 批量提取多列
需求：
提取transportation表中的油耗小于 20公里/rmb的交通具体的全部信息
代码如下：
declare
--声明集合类型
type vehtab is table of transportation%rowtype;
--初始化一个这个类型的集合
gas_quzzlers vehtab;
begin
select * bulk collect into gas_quzzlers from transportation where mileage < 20;
...
⑸ 对批量操作使用returning子句
有了returning子句后，我们可以轻松地确定刚刚完成的dml操作的结果，无须再做额外的查询工作
例子请见bulk collect 的用法的第三小点
㈡ 通过forall 加速dml
forall告诉pl/sql引擎要先把一个或多个集合的所有成员都绑定到sql语句中，然后再把语句发送给sql引擎
⑴ 语法
未完待续。。。