oracle 并发读取数据草案比较

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

查看更多 下一篇