我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理
这种在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
⑵ 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引擎
⑴ 语法 未完待续,,,
分享到:
相关推荐
但是,FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个,这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降请再看下图: 下面详解这爷俩㈠ 通过BULK COLLECT 加速查询⑴ BULK COLLECT...
记录 集合 BULK COLLECT FORALL 执行计划
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
采用bulk collect可以将查询结果一次性地加载到collections中。...可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都必须是collections.
Github:https://github.com/klout/brickhouse/tree/8fce0ac98aef422772ac89de7a620caac47ccc9d/src/main/java/brickhouse/udf/collect 使用说明: https://gitee.com/mirrors_klout/brickhouse/wikis/Collect-UDFs ...
结合一个存储过程的实例,介绍了 bulk collect 的使用
CC2540 IO CODE
bulk collect函数的使用,可以可以提高数据的访问效率
bulk collect bulk collect bulk collect例子特殊应用
sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO vsal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT INTO sal,ename BULK COLLECT ...
3.2.2.在spark shell中编写WordCount程序 1.首先启动hdfs 2.向hdfs上传一个文件到hdfs://hdp-01....flatMap(_.split(" ")).map((_,1)).reduceByKey(_ + _).sortBy(_._2,false).collect 注意:这些flatMap,map等方法是R
1.FORALL 用法小结 2.如何使用批挷定提高性能 3.FORALL 如何影响回滚 4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL...8.SQL优化学习笔记 9.给Oracle存储过程传入数组(这是自己的)
7、编辑record.c和Makefile Makefile文件的内容: CC=arm-openwrt-linux-g++ #CC=g++ DIR_OBJ=./obj DIR_INCLUDE=./include OBJ=record.o record:$(OBJ) $(CC) $(DIR_OBJ)/* -L./lib -lasound -o $@ %.o:%.c $...
使用Google Analytics的高级网站分析的方法
make all: error adding symbols: DSO missing from command line。 CXX/LD -o .build_release/examples/cpp_classification/classification.bin /usr/bin/ld: warning: libopencv_core.so.3.3, needed by /usr/...
make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/usr/local/tfs_release-2.2.16/src/new_client' Making all in dataserver make[2]: Entering directory `/usr/local/tfs_release-2.2.16/src...
Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall) 2.问题分析 我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 ...
oracle下巧用bulk collect实现cursor批量fetch的sql语句,使用oracel的朋友可以试试了