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) );
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