oracle 并发读取数据方案比较

[size=large][b]用ROWNUM控制[/b][/size]

说明
效率一般
需要提前知道有多少条满足条件的记录

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只是举例，表示一个线程取多少数据

[size=large][b]Rowid 取模[/b][/size]

说明
可以非常平均地分配记录到不同的线程上
对于每一天记录，都需要读取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;


[size=large][b]rowid范围扫描[/b][/size]

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

[b]--非分区表[/b]

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

[b]--分区表[/b]

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