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

suniori

LZ 避重就轻

javaex

ding

ora110

如果是full scan,多块读读情况下，楼主的情况确实会减少大量io。

seamanczg1984

原帖由 fromeast 于 2008-9-8 10:22 发表


按照ROWID的结构，ROWID的顺序即是数据块的顺序，而CURSOR返回的记录的顺序即是更新的顺序，所以更新就会按数据块的物理顺序依次处理，即更新完一个数据块里的所有行，再更新下一个数据块的数据。这样一来，对于每个数据块，就只有第一行会产生物理读，其他的行，因为数据块已经在内存里了，就只都是逻辑读了。

按数据块的顺序更新可能还有另外一个好处，就是磁盘的顺序读写成本更小，因为磁头减少了磁头的寻道动作。对于单个磁盘肯定是这样，但对于做了条带的RAID是不是也有这个好处，就不清楚了。但有一点是肯定的，就是好的存储设备会根据顺序访问的规律做预读，即监测到顺序访问时，会提前把后续的数据块读入存储的缓存。

CURSOR上的HINT只对CURSOR里的SELECT其作用。从SQL TRACE文件看到的大量的db file sequential read事件是UPDATE语句等待的，不是SELECT语句等待的。

楼主分析的十分正确，拿进buffer cache的data block里面的行都更新完了，在更新下一个data block里面的行。
跟你修改cache的参数应该没有关系。

lhlzjx

LZ的思路很好，学习了

rollingpig

加一个for all, 效果会更好
declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
  cursor cur_t2 is
    select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
       T2.id2, T2.curr_count, T1.rowid row_id
    from T1, T2
    where T1.id2=T2.id2
    order by T1.rowid;
  v_counter number;
begin
  v_counter := 0;
open cur_t2;
LOOP
EXIT WHEN cur_t2%NOTFOUND;
FETCH cur_t2 bulk collect into row_id_table,currcount_table limit maxrows;
forall i in 1 .. row_id_table.count
    update T1 set curr_count=currcount_table(i)
       where rowid= row_id_table(i);
      commit;
  end loop;
end;
/
参考：
http://space.itpub.net/1249/viewspace-64339

[ 本帖最后由 rollingpig 于 2008-9-8 16:11 编辑 ]

balsilliy

支持原创～

fromeast

原帖由 rollingpig 于 2008-9-8 16:08 发表
加一个for all, 效果会更好
declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
  cursor cur_t2 is
    select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
       T2.id2, T2.curr_count, T1.rowid row_id
    from T1, T2
    where T1.id2=T2.id2
    order by T1.rowid;
  v_counter number;
begin
  v_counter := 0;
open cur_t2;
LOOP
EXIT WHEN cur_t2%NOTFOUND;
FETCH cur_t2 bulk collect into row_id_table,currcount_table limit maxrows;
forall i in 1 .. row_id_table.count
    update T1 set curr_count=currcount_table(i)
       where rowid= row_id_table(i);
      commit;
  end loop;
end;
/
参考：
http://space.itpub.net/1249/viewspace-64339

谢谢！我想应该会很有效。以前用BULK COLLECT的时候，经常莫名其妙地遇到ORA-21779错误，一直没有解决，那时是9.2.0.4。不知道9.2.0.8会不会遇到这个问题。

TO_TO_RO

BULK COLLECT 不错

goodhawk

应该跟如下的写法差不多, 同时建议不要 1000 commit 一次, 可以考虑 10000 或者 30000 左右来一次,
太频繁也是浪费时间.

CURSOR c1 IS
    SELECT object_name,object_id,status
    FROM up
    ORDER BY object_name
    FOR UPDATE OF status;

BEGIN

FOR acct IN c1 LOOP   -- process each row one at a time   
  IF acct.status='INVALID' THEN
       UPDATE up SET status='TEST'
       WHERE current of c1;
  END IF;
END LOOP;

COMMIT;
END;