加一个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
带 limit 的 Bulk Collect , 或者说Forall 的一个简单例子
同时还用到了 dbms_sql 里预先定义好的rowid 的table类型
记录一下……
declare
row_id_table dbms_sql.Urowid_Table;
-- 每次process 几行自己确定
maxrows number default 100;
cursor cur is
select rowid from t2
where SENDTIME<=sysdate ;
begin
open cur;
LOOP
EXIT WHEN cur%NOTFOUND;
FETCH cur bulk collect into row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
insert into t1 select * from t2
where rowid = row_id_table(i) ;
forall i in 1 .. row_id_table.count
delete from t2
where rowid = row_id_table(i) ;
-- commit 加不加随你
commit;
end loop;
end;
/http://www.itpub.net/viewthread.php?tid=1052077&extra=&page=4
来看看我的方法，直接根据DBA_EXTENTS中的信息来更新
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;
