使用rowid模拟并行执行，用户大数据量的迁移
获取创建rowid需要的信息（data_object_id, relative_fno, block_id)
[cc lang="sql"]select o.data_object_id,
e.RELATIVE_FNO,
e.BLOCK_ID MIN_BLOCK,
e.BLOCK_ID + e.BLOCKS - 1 MAX_BLOCK
from dba_extents e, dba_objects o
where e.segment_name = 'USERS_EXTRA'
AND o.object_name = 'USERS_EXTRA'
and o.data_object_id is not null
获取一个block的rowid_min & rowid_max,
dbms_rowid.rowid_create(1,
data_object_id,
RELATIVE_FNO,
MIN_BLOCK,
0) rowid_min,
dbms_rowid.rowid_create(1,
data_object_id,
RELATIVE_FNO,
MAX_BLOCK,
10000) rowid_max
创建一个完整的包含rowid的临时表
create table tmp_xf_rowid_db1 as
select dbms_rowid.rowid_create(1,
data_object_id,
RELATIVE_FNO,
MIN_BLOCK,
0) rowid_min,
dbms_rowid.rowid_create(1,
data_object_id,
RELATIVE_FNO,
MAX_BLOCK,
10000) rowid_max
from (select o.data_object_id,
e.RELATIVE_FNO,
e.BLOCK_ID MIN_BLOCK,
e.BLOCK_ID + e.BLOCKS - 1 MAX_BLOCK
from dba_extents e, dba_objects o
where e.segment_name = 'USERS_EXTRA'
AND o.object_name = 'USERS_EXTRA'
and o.data_object_id is not null)
-----给临时表增加序列号 ----
alter table tmp_xf_rowid_db1 add (id number);
-------------增加序列数据-----------
declare
i number := 1;
begin
for c in (select t1.rowid_min from tmp_xf_rowid_db1 t1) loop
update tmp_xf_rowid_db1 t2 set id = i
where t2.rowid_min = c.rowid_min;
i := i + 1;
if mod(i, 10) = 0 then
commit;
end if;
end loop;
commit;
end;
--验证序列
alter table tmp_xf_rowid_db1
add constraint pk_tmp_xf_rowid_db1 primary key (ID) using index;[/cc]
---创建临时表 tmp_xf_fb_all_sum 存储汇总信息
[cc lang="sql"]create table tmp_xf_fb_all_sum
as select * from auction_feedback_all_sum where 1=0;
[/cc]
---创建并行执行的存储过程 SP_FB_ALLSUM_DB1
[cc lang="sql"]create or replace procedure SP_FB_ALLSUM_DB1(n number) is
i number := 0;
begin
for c in (select * from tmp_xf_rowid_db1 where mod(id,4)=n ) loop
insert into tmp_xf_fb_all_sum
......
......
where b.rowid between c.rowid_min and c.rowid_max;
i := i + 1;
if mod(i, 10) = 0 then
commit;
end if;
end loop;
commit;
end SP_FB_ALLSUM_DB1;
[/cc]
--------------
--在多个session分别执行
exec SP_FB_ALLSUM_DB1(0)；
exec SP_FB_ALLSUM_DB1(1)；
exec SP_FB_ALLSUM_DB1(2)；
exec SP_FB_ALLSUM_DB1(3)；
参考资料：
http://www.orawh.com/54.html
http://blog.itpub.net/post/48/22612
[cc lang="sql"]
function ROWID_CREATE(rowid_type IN number,
object_number IN number,
relative_fno IN number,
block_number IN number,
row_number IN number)
return ROWID;
-- rowid_type - 类型(restricted=0/extended=1)
-- object_number - 对象号
-- relative_fno - relative file number
-- block_number - 文件包含的block号
-- row_number - block中的行的行号
[/cc]
================
取rowid的语句：[cc lang="sql"]
select extent_id,
dbms_rowid.rowid_create(1, data_object_id, relative_fno, block_id, 0) s,
dbms_rowid.rowid_create(1,
data_object_id,
relative_fno,
block_id + blocks - 1,
10000) e
from (select extent_id,
relative_fno,
block_id,
blocks,
nvl(partition_name, 'default') partition_name
from dba_extents
where segment_name = upper('TC_BIZ_ORDER')
and owner = upper('TBCENTER')) a,
(select nvl(subobject_name, 'default') subobject_name, data_object_id
from dba_objects
where object_name = upper('TC_BIZ_ORDER')
and owner = upper('TBCENTER')) b
where a.partition_name = b.subobject_name
[/cc]
-- EOF --
This work is licensed under a CC A-S 4.0 International License.