Oracle使用游标进行分批次更新数据的6种方式及速度比对_oracle

来源:脚本之家  责任编辑:小易  

for 里的语句条件不对抄,A、B表都袭没链接条件另外2113你这种loop写法5261还不如直接写一个update,反正也4102没有做到分批提1653交。要分批提交,最简单定义个变量,每次循环+1,5000一次commit这样写if mod(i,5000)= 0 thencommit;endif;追问最开始使用update + exisits 进行条件判断进行写操作的,但是被锁表了。 后来又改为游标的。游标中是使用rowid作为条件的。但是实现的效果不理想。中间有加每1W条进行一次commit的操作。依旧长时间锁表,存储过程长时间处于等待状态。是否可能是数据库主机的内存不足造成?www.zgxue.com防采集请勿采集本网。

1.情景展示

先判断表存在与否,存在,然后就根据表名到对应的表中关联一下,然后遍历动态游标就可以了,

  一共有22w条数据, 需要将A表的主键更新至B表的指定字段,如何快速完成更新?

先 set serveroutput on; 然后创建过程及调用。 如果不行,试试END LOOP前加COMMIT。 如果还是不行,先设置一个常量,测试下游标是否正常运行,然后再改。 把游标定义更改为 cursor c_type is select * from stu a where 本来是用手机看

2.解决方案

使用merge into就可以了。 示例 MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus +

  声明:

会不会你的sql语句的问题呢,先保证这个正确 少量记录,做下debug,一般游标是一条条的循环执行,不会重复。 另外记得关闭游标

  解决方案不只一种,该文章只介绍快速游标法及代码实现;

With New_Table As (Select Logid, No, Opttime, Rownum As Row_Num From T_Tab_Log Where Part_Date = ?) Select Logid, No From New_Table t Where T.Row_Num = ? Order By Opttime Desc;

  两张表的ID和ID_CARD字段都建立了索引。 

  方式一:使用隐式游标(更新一次提交1次)

--快速游标法BEGIN  FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD                        FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2                       WHERE T1.ID_CARD = T2.ID_CARD                         AND T1.REMARK = '**市****区数据'                         AND T2.REMARK = '**市****区数据') LOOP    /* LOOP循环的是TEMP_CURSOR(逐条读取TEMP_CURSOR) */    UPDATE VIRTUAL_CARD10       SET INDEX_ID = TEMP_CURSOR.ID     WHERE ID_CARD = TEMP_CURSOR.ID_CARD;    COMMIT; --提交  END LOOP;END;

  执行时间:

  方式二:使用隐式游标(更新1000次提交1次)(推荐使用)

/* 使用隐式游标进行分批次更新 */DECLARE V_COUNT NUMBER(10);BEGIN /* 隐式游标 */ FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据') LOOP /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = TEMP_CURSOR.ID WHERE ID_CARD = TEMP_CURSOR.ID_CARD; /* 更新一次,+1 */ V_COUNT := V_COUNT + 1; /* 1000条提交1次 */ IF V_COUNT >= 1000 THEN COMMIT; --提交 V_COUNT := 0; --重置 END IF; END LOOP; COMMIT; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交END;

  执行时间:

  方式三:显式游标+分批次更新(1000条1提交)

/* 使用游标进行分批次更新 */DECLARE  V_COUNT    NUMBER(10);  V_INDEX_ID PRIMARY_INDEX10.ID%TYPE;  V_ID_CARD  PRIMARY_INDEX10.ID_CARD%TYPE;  CURSOR TEMP_CURSOR IS    SELECT T2.ID, T2.ID_CARD      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2     WHERE T1.ID_CARD = T2.ID_CARD       AND T1.REMARK = '**市****区数据'       AND T2.REMARK = '**市****区数据';BEGIN  OPEN TEMP_CURSOR;  LOOP    /* 取得一行游标数据并放到对应变量中 */    FETCH TEMP_CURSOR      INTO V_INDEX_ID, V_ID_CARD;    /* 如果没有数据则退出 */    EXIT WHEN TEMP_CURSOR%NOTFOUND;    /* 业务逻辑 */    UPDATE VIRTUAL_CARD10       SET INDEX_ID = V_INDEX_ID     WHERE ID_CARD = V_ID_CARD;    /* 更新一次,+1 */    V_COUNT := V_COUNT + 1;    /* 1000条提交1次 */    IF V_COUNT >= 1000 THEN      COMMIT; --提交      V_COUNT := 0; --重置    END IF;  END LOOP;  COMMIT; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交  CLOSE TEMP_CURSOR;END;

  执行时间:

  10000条1提交,执行时间:

  方式四:显式游标+数组(更新一次提交一次)(使用BULK COLLECT)

/* 使用游标+数组进行更新(更新一次提交一次) */DECLARE  /* 创建数组:一列多行 */  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;  /* 起别名 */  V_INDEX_ID TYPE_INDEX_ID;  V_ID_CARD  TYPE_ID_CARD;  /* 将查询出来的数据放到游标里 */  CURSOR TEMP_CURSOR IS    SELECT T2.ID, T2.ID_CARD      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2     WHERE T1.ID_CARD = T2.ID_CARD       AND T1.REMARK = '**市****区数据'       AND T2.REMARK = '**市****区数据';BEGIN  OPEN TEMP_CURSOR;  LOOP    /* 取得1000行游标数据并放到对应数组中,每次读取1000条数据 */    FETCH TEMP_CURSOR BULK COLLECT      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;    /* 如果没有数据则退出 */    EXIT WHEN TEMP_CURSOR%NOTFOUND;    /* 遍历数据 */    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP      /* 业务逻辑 */      UPDATE VIRTUAL_CARD10         SET INDEX_ID = V_INDEX_ID(I)       WHERE ID_CARD = V_ID_CARD(I);      COMMIT;    END LOOP;  END LOOP;  CLOSE TEMP_CURSOR;END;

  执行时间:

  方式五: 显式游标+数组(1000条提交一次)(使用BULK COLLECT)

/* 使用游标+数组进行更新(1000条提交一次) */DECLARE  /* 创建数组:一列多行 */  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;  /* 起别名 */  V_INDEX_ID TYPE_INDEX_ID;  V_ID_CARD  TYPE_ID_CARD;  /* 将查询出来的数据放到游标里 */  CURSOR TEMP_CURSOR IS    SELECT T2.ID, T2.ID_CARD      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2     WHERE T1.ID_CARD = T2.ID_CARD       AND T1.REMARK = '**市****区数据'       AND T2.REMARK = '**市****区数据';BEGIN  OPEN TEMP_CURSOR;  LOOP    /* 取得1000行游标数据并放到对应数组中 */    FETCH TEMP_CURSOR BULK COLLECT      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;    /* 如果没有数据则退出 */    EXIT WHEN TEMP_CURSOR%NOTFOUND;    /* 遍历数据 */    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP --或者:FOR I IN 1 .. V_INDEX_ID.COUNT LOOP      /* 业务逻辑 */      UPDATE VIRTUAL_CARD10         SET INDEX_ID = V_INDEX_ID(I)       WHERE ID_CARD = V_ID_CARD(I);      IF I >= V_INDEX_ID.LAST THEN        COMMIT; --提交      END IF;    END LOOP;  END LOOP;  CLOSE TEMP_CURSOR;END;

  执行时间:

  方式六:推荐使用(使用BULK COLLECT和FORALL)

/* 使用游标+数组进行更新(BULK COLLECT和FORALL) */DECLARE  /* 创建数组:一列多行 */  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;  /* 起别名 */  V_INDEX_ID TYPE_INDEX_ID;  V_ID_CARD  TYPE_ID_CARD;  /* 将查询出来的数据放到游标里 */  CURSOR TEMP_CURSOR IS    SELECT T2.ID, T2.ID_CARD      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2     WHERE T1.ID_CARD = T2.ID_CARD       AND T1.REMARK = '**市****区数据'       AND T2.REMARK = '**市****区数据';BEGIN  OPEN TEMP_CURSOR;  LOOP    /* 取得1000行游标数据并放到对应数组中 */    FETCH TEMP_CURSOR BULK COLLECT      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;    /* 如果没有数据则退出 */    EXIT WHEN TEMP_CURSOR%NOTFOUND;    /* 遍历数据 */    FORALL I IN 1 .. V_INDEX_ID.COUNT-- 或者V_INDEX_ID.FIRST .. V_INDEX_ID.LAST    /* 业务逻辑 */      UPDATE VIRTUAL_CARD10         SET INDEX_ID = V_INDEX_ID(I)       WHERE ID_CARD = V_ID_CARD(I);    COMMIT; --提交  END LOOP;  CLOSE TEMP_CURSOR;END;

  执行时间:

  从Oracle8开始,oracle为PL/SQL引入了两个新的数据操纵语言(DML)语句:BULK COLLECT和FORALL。

  这两个语句在PL/SQL内部进行一种数组处理;BULK COLLECT提供对数据的高速检索,FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。

  Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。 

小结:

  数据量小的时候可以用方式二,数据量大的时候推荐使用方式六;

  一定要建索引。

以上就是Oracle使用游标进行分批次更新的6种方式及速度比对的详细内容,更多关于Oracle 游标的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:Oracle 遍历游标的四种方式汇总(for、fetch、while、BULK COLLECT)Oracle数据库游标连接超出解决方案详解Oracle游标的简易用法Oracle游标的使用实例详解Oracle中游标Cursor基本用法详解详解Oracle隐式游标和显式游标Oracle存储过程游标用法分析Oracle出现超出打开游标最大数的解决方法Oracle显示游标的使用及游标for循环Oracle存储过程返回游标实例详解Oracle 游标使用总结

游标一般e69da5e6ba903231313335323631343130323136353331333361313939都是在执行sql语句,我不知道你指的是这个sql语句比较慢,还是单纯的游标速度慢。sql语句慢可以根据使用改写sql语句的写法,建立索引,建立分区等优化手段来解决。单纯游标速度慢,可以考略批量执行bulk collect这种方式提取数据使用forall 替代for 循环也可以提升效率。以下是我总结的plsql调优的重点,你可以参考一下:1. 尽早退出循环2. static的变量尽量放在循环的外层3. 因为and 和or 只要判断左边的表达式为真即可推出 逻辑判断 所以将容易出现true的逻辑表达式放在左边,这样可以省下处理的时间4. if else 和case when 同上5. 尽量使用迭代,而不是递归,因为调用存储过程或者函数,会开辟另外的内存空间6. 通常函数都是值传递,即传参数的时候会copy参数的值到子程序中对于数据量比较大的参数可以使用in out nocopy 指定引用传递,可以节省大量时间7. 可以使用关联数组,较少查找集合中元素的时间关联数组index by varchar2(xx) 类似于map,会省下很集合操作,以及时间。8. 对于cpu密集型的plsql,可以使用native 本地编译的特性alter xxx compile plsql_code_type=native ;9. 经常使用的子程序可以使用inline函数内嵌到主程序中pragma inline($FUNCTION_NAME,'yes') ;重载的程序只要拥有相同的名称都会被内嵌到program unit中plsql_optimize_level=2的时候需要声明inline使用内联plsql_optimize_level=3的编译的时候会自动的做这部分工作。不需要自己执行inline操作。可以使用PRAGMA INLINE (p1, 'NO');禁止内联当level=3的时候声明了inline会给予这个子程序更高的inline的优先级。下面的语句中(1)PRAGMA INLINE (p1, 'YES');(2)PRAGMA INLINE (p1, 'NO');(3)PRAGMA INLINE (p1, 'YES');第二行会覆盖第一行以及第三行,也就是说program unit 中奖不使用内联10. 大量数字操作的时候可以使用java存储过程11. 使用result cache功能 缓存经过计算得到的结果 relies_on($TB_NAME)指定当表tb_name的数据改变时,结果集缓存失效12. 使用update of $COLUMN_NAME on $TB_NAME for each rowwhen (new.xx>1000)可以省下需要处理时间最好都使用after 触发器,before可能会有锁定的问题内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • oracle数据库中如何处理clob字段方法介绍
  • oracle10g 安装方法
  • oracle中创建序列及序列补零实例详解
  • oracle sql性能优化系列学习二
  • oracle sql语言模糊查询--通配符like的使用教程详解
  • oracle数据表中的死锁情况解决方法
  • oracle数字类型number自增的实现代码
  • oracle表空间设置和管理浅析
  • oracle form中commit的概述及使用技巧
  • oracle 8i在p4上的安装
  • oracle 怎么提升游标速度
  • 为什么oracle 隐式游游标+rowid进行存储过程批量更...
  • 在存储过程中怎么使用游标把一个表的数据一行一行...
  • oracle 系统参数中, 怎么调游标的大小? 用sql语...
  • oracle 用游标查询以什么开头的表数据
  • oracle 使用游标更新数据问题
  • oracle存储过程 使用游标 比对临时表和业务表两张...
  • oracle 使用游标后为什么产生双份重复数据?
  • Oracle 数据量非常大(上亿)时,使用存储过程中的...
  • oracle数据库 多条bulk collect into批量处理
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页oracle 遍历游标的四种方式汇总(for、fetch、while、bulk collect)oracle数据库游标连接超出解决方案详解oracle游标的简易用法oracle游标的使用实例详解oracle中游标cursor基本用法详解详解oracle隐式游标和显式游标oracle存储过程游标用法分析oracle出现超出打开游标最大数的解决方法oracle显示游标的使用及游标for循环oracle存储过程返回游标实例详解oracle 游标使用总结oracle数据库中如何处理clob字段方法介绍oracle10g 安装方法oracle中创建序列及序列补零实例详解oracle sql性能优化系列学习二oracle sql语言模糊查询--通配符like的使用教程详解oracle数据表中的死锁情况解决方法oracle数字类型number自增的实现代码oracle表空间设置和管理浅析oracle form中commit的概述及使用技巧oracle 8i在p4上的安装oracle 查看表空间的大小及使用情linux系统(x64)安装oracle11g完oracle数据库下载及安装图文操作oracle存储过程基本语法介绍oracle 10g 安装教程[图文]oracle 如何查询被锁定表及如何解基于ora-12170 tns 连接超时解决oracle数据库tns配置方法详解oracle中to_date详细用法示例(ororacle 创建表空间详细介绍pl/sql number型数据新手学oracle常见疑问oracle中分组查询group by用法规则详解oracle与mysql自动增长列(id)的区别基于oracle expdp命令的使用详解oracle number型的深入理解oracle 外连接实现代码oracle判断指定列是否全部为数字的sql语句oracle存储过程及调用oracle 层次化查询(行政区划三级级联)
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved