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

xzh2000

原帖由 棉花糖ONE 于 2008-9-11 10:43 发表


你看看楼主说的,楼主的意思是我读取了一行,然后我读取下一行的时候,这个块被flush出去,引起了不必要的物理读,用order by rowid避免了这问题

呵呵,你还在迷糊呢...

xzh2000

还有n多种办法提高批量数据处理的速度...
假如需要在2个小时内处理完毕呢?楼主该如何处理?其实用不用rowid,都可以很快地处理完...

suniori

原帖由 xzh2000 于 2008-9-11 10:48 发表
还有n多种办法提高批量数据处理的速度...
假如需要在2个小时内处理完毕呢?楼主该如何处理?其实用不用rowid,都可以很快地处理完...

这个我感兴趣，请说明

棉花糖ONE

我们现在讨论的是,楼主这里到底是不是order by rowid起到了作用

棉花糖ONE

原帖由 xzh2000 于 2008-9-11 10:48 发表
还有n多种办法提高批量数据处理的速度...
假如需要在2个小时内处理完毕呢?楼主该如何处理?其实用不用rowid,都可以很快地处理完...

请把n多种提高批量数据处理的方法都说说

xzh2000

原帖由 棉花糖ONE 于 2008-9-11 10:50 发表
我们现在讨论的是,楼主这里到底是不是order by rowid起到了作用

当然是order by rowid起了作用...

棉花糖ONE

原帖由 xzh2000 于 2008-9-11 11:27 发表


当然是order by rowid起了作用...

别这么肯定,能举个例子吗

zhp6489

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 FROM dba_objects where owner = 'OWNER' AND OBJECT_NAME = 'T1'
               AND SUBOBJECT_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;

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

rollingpig

1. 关于keep的问题，其实LZ写得很清楚，table 太大，不可能keep在内存中。

2. 为什么order by rowid 能起作用。想象一下一个极端情况，你的db_cache_size 很小，只够放一个table的block. 如果不用order by rowid, 那么，连续的每个更新的语句更新的row很有可能不在同一个block里。
如有这样的数据：
row1 : block 1
row2 : block 2
row3 : block 3
row4 : block 1
row5 : block 2
row6 : block 3
row7 : block 2
row8 : block 1

那么，逐条更新的话，每更新下一条都会导致前一个block被挤出去，相当于总共8个物理读。
而如果order by rowid
则会是这样子：
row1 : block 1
row2 : block 1
row3 : block 1
row4 : block 2
row5 : block 2
row6 : block 2
row7 : block 3
row8 : block 3
逐条更新的话，相当于3个物理读。

当然，实际情况可能更复杂，但是，总体来说，除非原始数据本来就按rowid/blockid排序，或者内存足够大，否则，order by rowid 并逐条更新的确会降低物理读(db file sequential read) .

其实可以自己构造一个测试来验证，紧记两点：
1。 内存尽量小
2。 原更新source的顺序尽量不合rowid排序

棉花糖ONE

是的,楼主说的这情况出现说明buffer cache的使用已经很有问题了,而楼主keep了表,虽然不能keep住整个表,但是有可能这时候就算没有order by rowid,而楼主之前的那情况就已经没存在了