Oracle 大数据量update优化

Oracle 大数据量update优化

一、基于ROWID改善update性能

oracle update 语句不支持象查新那样进行连接处理, 所以关联子表update 需要使用子查询,举个例子: 加入有一张员工考勤表, 一张员工打卡表,需要将打卡表的最早时间更新到员工表,那么应该这么写:

     update  <员工考勤表 t1> set  最早打卡时间 = (select min(打卡时间) from <员工打卡表 t2> where t1.员工编号 =t2.员工编号 and t1.时间 = t2.时间)

 这个语句在遍历update 员工考勤表时,  每条update 都会去执行子查询, 查找最早打卡时间, 所以执行效率很低, 所以当员工考勤表数据量很大时, 性能会成为瓶颈, 非常慢.

更好的一种方式, 是使用游标, 执行一次查询,取出员工的最早打卡时间, 以及考勤表的rowid, 然后循环update ,因为rowid 是硬地址, 定位相当快, 所以这个update 的性能是非常快, 可以提供性能几十倍不止, 下面是例子: 

declare

 cursor myCursor is   --声明游标myCursor

 select  A.ROWID rid, A.员工编号 no, A.时间 date, min(B.打卡时间) morningtime

  FROM 考勤表 A join 打开表 B on (A.员工编号 =B.员工编号 and A.时间=B.时间)

  WHERe <....>

   group by A.ROWID, 员工编号, 时间

  order by A.ROWID

V_COUNTER NUMBER;--声明一个number类型的变量

BEGIN

 V_VOUNTER:=0;--初始化变量值为0

 FOR row IN myCursor LOOP     --遍历游标

  UPDATe 考勤表 SET 打卡时间=row.morning WHERe ROWID = row.rid;

  V_COUNTER:=V_COUNTER+1;       --每次循环变量值+1

  IF(V_COUNTER>=1000) THEN

    COMMIT;

    V_COUNTER:=0;     --每更新1000行，V_COUNTER值为1000时候，就提交给数据库

    提交后将变量归零，继续下一个1000行更新

  END IF;

 END LOOP;

 COMMIT;

END;

二. ROWID的限制

oracle 中的ROWID是物理地址,看下面例子:

 select ROWID from Bruce_test where rownum<2;

ROWID 
------------------ ---------- 
AAABnlAAFAAAAAPAAA 
ROWID的格式如下：

数据对象编号        文件编号        块编号           行编号
OOOOOO             FFF                BBBBBB RRR

我们可以看出，从上面的rowid可以得知：
AAABnl 是数据对象编号
AAF是相关文件编号
AAAAAP是块编号
AAA 是行编号

 ROWID是表中行的唯一标识，是一个伪列.

注意ROWID有适用范围, 只能用在表:普通表，cluster table, partition table, subpartition table, index, index partitions and subpartitions(注意：不包含index-organized tables)

index-organized tables基于索引组织的表即IOT表,物理rowid是可能变化的，对于IOT表,Oracle以表的主键为基础引入UROWID(通用ROWID).