浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献
发布时间:2024-01-14 01:04:27
我们知道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
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引擎
⑴ 语法
未完待续。。。
猜你喜欢
- 一、理论知识准备1.确定假设函数 如:y=2x+7 其中,(x,y)是一组数据,设共有m个2.误差cost 用平方误差代价函数 3.减小误差
- 代码自动填充了空格 问题在使用pycharm的代码编辑器时,常常懒得写空格,如下图,但这是不符合代码规范的,而且也会影响可读性。解决方法py
- 有在论坛上看到一帖,《请教查询出连续日期记录的方法》,截图如下:Insus.NET尝试写了程序并做了测试,可以得到预期的结果,SQL代码可参
- Mysql数据库、数据库表、数据基础操作笔记分享给大家,供大家参考,具体内容如下一、数据库操作1.创建数据库Create dat
- 1, 创建pytorch 的Tensor张量:torch.rand((3,224,224)) #创建随机值的三维张量,大小为(3,224,2
- 错误信息UnicodeDecodeError: ‘utf-8' codec can't decode byte 0xce i
- 在之前的Python办公自动化案专题中,我们已经介绍了如何有选择的提取某些页面进行合并。但是很多时候,我们并不会预知希望提取的页号,而是希望
- 网页制作中是免不了与表格打交道的。表格是网页制作的基础,在网页布局中起着举足轻重的作用,熟练掌握表格的操作,是你制作网页的辅路石,也是你成为
- 在上篇文章给大家介绍过Django 多环境配置详解,感兴趣的朋友可以点击查阅,今天继续给大家介绍django 多环境配置的相关内容,本文重点
- 项目背景 在之前的一个项目中用Python的Flask写了一个提供公共基础服务的Rest应用,上面大佬的意思是需要将这一部分封装成容器化服
- 前言sys模块是与python解释器交互的一个接口。sys 模块提供了许多函数和变量来处理 Python 运行时环境的不同部分。处理命令行参
- ES在之前的博客已有介绍,提供很多接口,本文介绍如何使用python批量导入。ES官网上有较多说明文档,仔细研究并结合搜索引擎应该不难使用。
- 本例中以CentOS6.6下修改MySQL5.1.73举例说明。1.首先输入“service mysqld status”查看当前mysql
- 最近将Jesse James Garrett的《用户体验的要素》一书读了两遍,做一些简要的摘录并添加一些个人注释。当然,一本好书绝对不是简单
- 依次前移,实现聊友们的发言的更迭:function form1_onsubmit()if document.form1.
- 1、说明Python实现异步IO非常简单,asyncio是Python 3.4版本引入的标准库,直接内置了对异步IO的支持。asyncio的
- Yahoo发布了一款基于FireFox的插件,名叫YSlow,这个插件可以分析网站的页面,并告诉你为了提高网站性能,如何基于某些规则而进行优
- Python中将列表转换成为数据框有两种情况:第一种是两个不同列表转换成一个数据框,第二种是一个包含不同子列表的列表转换成为数据框。第一种:
- 本篇博文主要讲解Python爬虫实例,重点包括爬虫技术架构,组成爬虫的关键模块:URL管理器、HTML下载器和HTML解析器。爬虫简单架构程
- --利用T-SQL语句,实现数据库的备份与还原的功能 ----体现了SQL Server中的四个知识点: ----1. 获取SQL Serv