利用rowid快速在线更新海量数据

dfgd686 · 发表于 2008-9-18 18:36

学习了，顶一个！

Tomac · 发表于 2008-9-18 18:40

原帖由 zhp6489 于 2008-9-11 11:36 发表
declare
maxrows number default 1000;
maxblocks number default 8;
v_partition_name varchar2(30);
v_relative_fno number;
v_block_id    number;
v_blocks       number;
v_endblocks    number;
v_object_id    number;
rowid_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
v_rowid       urowid;
v_rowid2       urowid;
cursor cur_extents is
   select PARTITION_NAME, RELATIVE_FNO, block_id, blocks
   from dba_extents a
   where a.owner = 'OWNER' and a.segment_name = 'T1'
   ORDER BY a.EXTENT_ID;
begin
  open cur_extents;
  LOOP
EXIT WHEN cur_extents%NOTFOUND;
FETCH cur_extents into v_partition_name, v_relative_fno, v_block_id, v_blocks;
v_endblocks := 0;
IF v_partition_name iS NULL THEN
   SELECT OBJECT_ID INTO v_object_id FROM dba_objects where owner = 'OWNER' AND OBJECT_NAME = 'T1'
            AND OBJECT_TYPE = 'TABLE';
ELSE
   SELECT OBJECT_ID INTO v_object_id FROMdba_objects where owner = 'OWNER' AND OBJECT_NAME = 'T1'
            ANDSUBOBJECT_NAME = v_partition_name AND OBJECT_TYPE = 'TABLE PARTITION';
END IF;

while v_endblocks < v_blocks loop
      v_rowid := dbms_rowid.rowid_create(1,v_object_id, v_relative_fno, v_block_id + v_endblocks, 0);
      v_endblocks := v_endblocks + maxblocks;
      if v_endblocks > v_blocks THEN
         v_rowid2 :=dbms_rowid.rowid_create(1, v_object_id, v_relative_fno, v_block_id +v_blocks - 1, 1000);
      else
         v_rowid2 :=dbms_rowid.rowid_create(1, v_object_id, v_relative_fno, v_block_id +maxblocks - 1, 1000);
      end if;

      select /*+ ROWID(T1) */T1.ROWID, T2.curr_count
      bulk collect into rowid_table, currcount_table
      FROM OWNER.T1 T1, OWNER.T2 T2
      WHERE T1.ID2 = T2.ID2 and T1.rowid between v_rowid AND v_rowid2;

      forall i in 1 .. rowid_table.count
               update OWNER.T1 set curr_count=currcount_table(i)
               where rowid= rowid_table(i);
         commit;
end loop;
  end loop;
  close cur_extents;
end;

其中查询语句中的提示可以根据具体情况进行调整