对于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);
对于update,delete操作,如果分区表,可以分区进行操作;
如果是一个非分区的大表,那么此
阅读背景：
oracle 根据rowid分片
来源：互联网
分享到：
