oracle 根据rowid分片

对于update,delete操作,如果分区表,可以分区进行操作;
如果是一个非分区的大表,那么此时的容易引发UNDO不够,如果连接中断的话,那么回滚必然很耗时间;
所以,这里可以对非分区的大表进行rowid分片;
先验证分片数量是否正确:
declare
  cursor c_get_rid is
       select 
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID,0
     ) rid1,
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID+aa.BLOCKS-1,999) rid2
     from dba_extents aa,dba_objects bb
     where aa.segment_name=bb.OBJECT_NAME
     and aa.owner=bb.OWNER
     and aa.owner='SCOTT'
     and aa.segment_name=upper('TAB1') ;

   v_count pls_integer:=0; 
   v_sum pls_integer:=0; 

begin
   for v_row in c_get_rid loop
       select count(1) into v_count from tab1 a where  rowid>=v_row.rid1 and rowid<=v_row.rid2; 
       v_sum:=v_sum+ v_count;
   end loop;
   dbms_output.put_line('分片统计行数:'||v_sum);  
   select count(1) into v_sum from tab1;
   dbms_output.put_line('实际统计行数:'||v_sum);    
end;
发现是一样的;

--delete操作

grant select on dba_extents to scott;
grant select on dba_objects to scott;  
/*
为某个表自定义分割片数
p_range:分区的个数
*/ 
create or replace procedure p_rowid(p_table_name varchar2,p_range pls_integer,p_id pls_integer)
as
  cursor c_get_rid is
     select 
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID,0
     ) rid1, 
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID+aa.BLOCKS-1,999) rid2
     from dba_extents aa,dba_objects bb
     where aa.segment_name=bb.OBJECT_NAME
     and aa.owner=bb.OWNER
     and aa.owner='SCOTT'
     and aa.segment_name=upper(p_table_name) 
     and mod(aa.EXTENT_ID,p_range)=p_id;
     v_sql varchar2(4000);

begin

    for v_row in c_get_rid loop
       v_sql:='delete '||p_table_name||' where rowid between :1 and :2';
       execute immediate v_sql using v_row.rid1,v_row.rid2;
    end loop;    
    dbms_output.put_line('ok');
    commit;
exception
  when others then
   dbms_output.put_line(substr(sqlerrm,1,200)||dbms_utility.format_error_backtrace);  
end;
/

测试:在4个窗口执行;
exec p_rowid('tab1',4,2);

但是上面,当你在不同sql窗口执行时,无法保证数据的一致性,于是可以创建一个临时表,再结合批量更新 ,
最快的更新方式就这样诞生了…

   drop table   tab1_rowid;
   create table tab1_rowid
   as
     select 
     rownum rn,
     aa.EXTENT_ID,
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID,0
     ) rid1, 
     dbms_rowid.rowid_create(
      1,bb.DATA_OBJECT_ID,aa.relative_fno,aa.BLOCK_ID+aa.BLOCKS-1,999) rid2
     from dba_extents aa,dba_objects bb
     where aa.segment_name=bb.OBJECT_NAME
     and aa.owner=bb.OWNER
     and aa.owner='SCOTT'
     and aa.segment_name=upper('tab1');

--rowid分片加批量更新  最快的方式
create or replace procedure p_tab1_rowid(p_id pls_integer)
as
  cursor c_get_rid is
  select aa.rid1,aa.rid2 from tab1_rowid aa where  mod(aa.rn,8)=p_id;     
  v_min_rowid_tab dbms_sql.Urowid_Table; 
  v_max_rowid_tab dbms_sql.Urowid_Table;

  v_sql varchar2(4000);
  v_limit pls_integer:=1000;
begin
   open c_get_rid; 
    loop   
    fetch   c_get_rid  bulk collect into v_min_rowid_tab,v_max_rowid_tab limit  v_limit;
      forall x in  1 .. v_min_rowid_tab.count
         update tab1 t set t.object_name='aaa' where rowid between v_min_rowid_tab(x) and v_max_rowid_tab(x);  
      exit when c_get_rid%notfound;
    end loop;
   close  c_get_rid;
exception
  when others then  
   --如果有异常回滚所有  
   rollback;
dbms_output.put_line(substr(sqlerrm,1,200)||dbms_utility.format_error_backtrace);  
end;
/


--8个窗口执行
begin
  p_tab1_rowid(0);  
...
  p_tab1_rowid(7);
end;

分片参考来源:落落老师