oracle 并发读取数据方案比较

ig9k33v1aj

用ROWNUM控制
说明
效率一般
需要提前知道有多少条满足条件的记录
select * from table1 b
where b.rowid in
( select k.rid from
(select t.*, rownum rn from  (select a.rowid rid from table1 a) t where rownum < 10000 * n) k
where rn >= 10000*(n-1)
);

--n为线程号
--10000只是举例，表示一个线程取多少数据
Rowid 取模
说明
可以非常平均地分配记录到不同的线程上
对于每一天记录，都需要读取N次(N为并发数)，效率不高
只适用于Oracle
--举例
-- 线程1
  select * from table_name where mod(ora_hash(rowid),10)=0;
......
--线程10
  select * from table_name where mod(ora_hash(rowid),10)=9;

rowid范围扫描
说明
最高效的数据同步方式：对于每一条数据只需要扫描一次
需要有查询 dba_extents 和 dba_objects两个数据字段的查询权限
对于小表的查询，并发数设为1，否则有些线程会拿不到任何数据
建议至少每100MB才增加一个并发，如100MB大小，并发数1；200MB，并发数2；但总并发数不要超过10个
只适用于Oracle
--非分区表

select grp,
dbms_rowid.rowid_create(1, data_object_id, lo_fno, lo_block, 0) min_rid,
dbms_rowid.rowid_create(1, data_object_id, hi_fno, hi_block, 10000) max_rid
from (select distinct grp,
first_value(relative_fno) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id + blocks - 1) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over(partition by grp) sum_blocks
from (select relative_fno,
block_id,
blocks,
trunc((sum(blocks)
over(order by relative_fno, block_id) - 0.01) /
(sum(blocks) over() / 10)) grp
from dba_extents
where segment_name = 'POPT_FENHONG_NEWCONSUME_MES'
and owner = 'CREATER_USER')) v,
(select data_object_id
from dba_objects
where object_name = 'POPT_FENHONG_NEWCONSUME_MES'
and owner = 'CREATER_USER')
order by grp;

GRP MIN_RID            MAX_RID
---------- ------------------ ------------------
0 AAENXwABHAAADIFAAA AAENXwABUAAABkECcQ
1 AAENXwABVAAAAyFAAA AAENXwABjAAABkECcQ
2 AAENXwABkAAAAyFAAA AAENXwAB1AAIZgECcQ
3 AAENXwAB2AABTmFAAA AAENXwAB8AAAakECcQ
4 AAENXwAB9AAAWqFAAA AAENXwACEAAAXcECcQ
5 AAENXwACEAAAYOFAAA AAENXwACLAAAKKECcQ
6 AAENXwACMAAAImFAAA AAENXwACSAAAImECcQ
7 AAENXwACTAAAHCFAAA AAENXwACaAAAGQECcQ
8 AAENXwACaAAAGQFAAA AAENXwAChAAAFeECcQ
9 AAENXwACiAAAD6FAAA AAENXwACpAAAD6ECcQ

--分区表


select grp,
dbms_rowid.rowid_create(1, data_object_id, lo_fno, lo_block, 0) min_rid,
dbms_rowid.rowid_create(1, data_object_id, hi_fno, hi_block, 10000) max_rid
from (select distinct grp,
first_value(relative_fno) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) lo_fno,
first_value(block_id) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) lo_block,
last_value(relative_fno) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) hi_fno,
last_value(block_id + blocks - 1) over(partition by grp order by relative_fno, block_id rows between unbounded preceding and unbounded following) hi_block,
sum(blocks) over(partition by grp) sum_blocks
from (select relative_fno,
block_id,
blocks,
trunc((sum(blocks)
over(order by relative_fno, block_id) - 0.01) /
(sum(blocks) over() / 10)) grp
from dba_extents
where segment_name = 'POPT_TOTAL_LOGIN_ALL_HIS'
and owner = 'CREATER_USER'
and partition_name = 'PARTITION_20110101')) v,
(select data_object_id
from dba_objects
where object_name = 'POPT_TOTAL_LOGIN_ALL_HIS'
and owner = 'CREATER_USER'
and subobject_name = 'PARTITION_20110101')
order by grp;

GRP MIN_RID            MAX_RID
---------- ------------------ ------------------
0 AAD/wHACqAAJjuFAAA AAD/wHACwAAI2aECcQ
1 AAD/wHACxAAInkFAAA AAD/wHAC3AAIAgECcQ
2 AAD/wHAC4AAH1kFAAA AAD/wHAC+AAHeIECcQ
3 AAD/wHAC/AAHSaFAAA AAD/wHADFAAG1gECcQ
4 AAD/wHADGAAGueFAAA AAD/wHADMAAGT6ECcQ
5 AAD/wHADNAAGJwFAAA AAD/wHAD1AAPcSECcQ
6 AAD/wHAD1AAPjUFAAA AAD/wHAD5AAOn8ECcQ
7 AAD/wHAD6AAOL0FAAA AAD/wHAD+AAMdqECcQ
8 AAD/wHAD/AAMD4FAAA AAD/wHAEFAAK0MECcQ
9 AAD/wHAEGAAKjAFAAA AAD/wHAEMAAJykECcQ