oracle 并发读取数据方案比较
用ROWNUM控制
说明
效率一般
需要提前知道有多少条满足条件的记录
select * from table1 bwhere 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