当前位置:  数据库>oracle

浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献

    来源: 互联网  发布时间:2014-09-07

    本文导语:  我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换,每次却换时,都有额外的开销 请看下图: 但是,FORALL和BULK COLLECT可以让PL/SQL引擎把...

我们知道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 rownum10;
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引擎

⑴ 语法

未完待续。。。


    
 
 

您可能感兴趣的文章:

  • 如何实现连接一次数据库,提交多个sql语句。(sql的批处理)
  • sqlserver中通过osql/ocmd批处理批量执行sql文件的方法
  • 批处理 动态sql
  • mysql中查询当前正在运行的SQL语句并找出mysql中运行慢的sql语句
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • Mysql增加主键或者更改表的列为主键的sql语句
  • sql语句实例 取得日志条目的sql语句
  • Mysql下在某一列后即表的某一位置添加新列的sql语句
  • SQL Server统计SQL语句执行时间的脚本
  • sql语句实例 统计页面链接的sql语句
  • 一条SQL语句搞定Sql2000 分页
  • C#中验证sql语句是否正确(不执行语句)
  • SQL Server数据库的修复SQL语句
  • SQL 语句拦截 P6SPY
  • C#使用带like的sql语句时防sql注入的方法
  • t-sql/mssql用命令行导入数据脚本的SQL语句示例
  • 在SQL Server的try...catch语句中获取错误消息代码的的语句
  • sql2005创建远程登录帐户的sql语句分享
  • SQL SERVER 查询正在实行的SQL语句
  • SQL语句中含有乘号报错的处理办法
  • sql无效字符引起的执行sql语句报错的解决方法
  • C#代码验证sql语句是否正确(只验证不执行sql)的方法
  • SQL Server中选出指定范围行的SQL语句代码
  • C#调用SQL语句时乘号的用法
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • java命名空间java.sql接口statement的类成员方法: executeupdate定义及介绍
  • 请问,这是什么错误!java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][Named Pipes]??????? SQL Server?虽然分少,但一定给,只要您是前5名回复者中最好的以为!
  • java命名空间java.sql接口connection的类成员方法: nativesql定义及介绍
  • SQL查询分析工具 SQL Workbench/J
  • java命名空间java.sql接口preparedstatement的类成员方法: executeupdate定义及介绍
  • SQL客户端软件 PKLite SQL Client
  • java命名空间java.sql接口rowid的类成员方法: getbytes定义及介绍
  • SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
  • java命名空间java.sql接口ref的类成员方法: getbasetypename定义及介绍
  • SQL客户端管理工具 SQuirreL SQL Client
  • java命名空间java.sql接口databasemetadata的类成员方法: getsqlkeywords定义及介绍
  • 如何处理此错误:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]没有执行可选特性
  • java命名空间java.sql接口rowid的类成员方法: tostring定义及介绍
  • sql2005 大数据量检索分页的sql代码
  • java命名空间javax.sql.rowset接口joinrowset的类成员方法: getwhereclause定义及介绍
  • 微软网站下载的Ms SQL Server2000 JDBC Driver,必须用英文版的SQL server2000吗?
  • java命名空间java.sql接口statement的类成员方法: execute定义及介绍
  • SQL客户端软件 PKLite SQL Client iis7站长之家
  • java命名空间java.sql接口resultset的类成员方法: getcursorname定义及介绍
  • 创建 sql server 链接服务器的sql代码
  • java命名空间java.sql接口resultsetmetadata的类成员方法: getcolumntype定义及介绍
  • import Java.sql.*; 报错包 Java.sql 不存在?请帮帮我!


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3