oracle多表并行,并行全表扫描oracle表-Oracle

并行全表扫描oracle表

有时候，我们全量dump oracle中的数据，如何才能让dump高效的执行呢？这个问题的关注点在于1.磁盘顺序读.2.并行.前者要需要oracle本身提供数据能够基本顺序并且程序并行扫描一段数据能够一次性取一批，后者由dump程序保证。

提供下oracle rowid分段的sql,程序就是用多线程扫描每一段数据,这里就不提供了。

Java代码

select group_id,min(chartorowid(min_rowid)) min_rowid, max(chartorowid(max_rowid)) max_rowid    www.2cto.com

from (select T.min_rowid,

T.max_rowid,

T.Relative_Fno,

NTILE(分的段数) over(order by T.Relative_Fno,chartorowid(T.min_rowid)) group_id

from (select dbms_rowid.rowid_create(1,

B.data_object_id,

A.relative_fno,

A.block_id,

0) min_rowid,

dbms_rowid.rowid_create(1,

B.data_object_id,

A.relative_fno,

A.block_id + A.blocks – 1,

10000) max_rowid,A.Relative_Fno

from (select relative_fno, block_id, blocks

from sys.dba_extents    www.2cto.com

where segment_name = ‘表名’

and owner = ‘表的所有用户’

order by block_id) A,

(select data_object_id

from sys.DBA_objects

where owner = ‘表的所有用户’

and object_name = ‘表名’) B

where B.data_object_id IS NOT NULL) T)

group by group_id;