海量数据迁移之通过rowid切分大表

在之前的章节中，讨论过了通过 分区+并行等方式来进行超大的表的切分，通过这种方式能够极大的提高数据的平均分布，但是不是最完美的。
比如在数据量再提高几个层次，我们假设这个表目前有1T的大小。有10个分区，最大的分区有400G,那么如果我们想尽可能的平均的导出数据，使用并行就不一定能够那么奏效了。
比方说我们要求每个dump文件控制在200M总有，那样的话400G的分区就需要800个并行才能完成，在实际的数据库维护中，我们知道默认的并行数只有64个，提高几倍，也不可能超过800
所以在数据量极大的情况下，如果资源紧张，可能生成的dump就会比较大。

我们考虑使用rowid来满足我们的需求。
我们可以根据需要来指定需要生成几个dump文件。比如表subscriber有600M,那么如果按照200M为一个单位，我们需要生成3个dump文件。
如果想数据足够平均，就需要在rowid上做点功夫。
我们先设定一个参数文件，如下的格式。
可以看到表memo数据量极大，按照200M一个单位，最大的分区（P9_A3000_E5）需要800个并行。
表ICE_AGREEMENT比较小，不是分区表，我们以x来临时作为分区表的代名，在处理的时候可以方便的甄别

MEMO                                 P9_A3000_E0                           156
MEMO                                 P9_A3000_E1                           170
MEMO                                 P9_A3000_E2                           190
MEMO                                 P9_A3000_E3                           200
MEMO                                 P9_A3000_E4                           180
MEMO                                 P9_A3000_E5                           800
MEMO                                 PMAXVALUE_AMAXVALUE_EMAXVALUE         1
ICE_AGREEMENT                        x                                    36
CRIBER_HISTORY                       x                                    11

可以使用如下的脚本来完成rowid的切分。

#### $1 dba conn details
#### $2 table owner
#### $3 table_name
#### $4 subobject_name
#### $5 parallel_no
function normal_split
{
sqlplus -s $1
set linesize 200
set pages 0
set feedback off
spool list/rowid_range_$3_x.lst
select rownum || ', ' ||' rowid between '||
chr(39)||dbms_rowid.rowid_create( 1, DOI, lo_fno, lo_block, 0 ) ||chr(39)|| ' and  ' ||
chr(39)||dbms_rowid.rowid_create( 1, DOI, hi_fno, hi_block, 1000000 )||chr(39) data
from (
SELECT DISTINCT DOI, grp,
first_value(relative_fno) over (partition BY DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) lo_fno,
first_value(block_id ) over (partition BY DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) lo_block,
last_value(relative_fno) over (partition BY DOI,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 DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) hi_block,
SUM(blocks) over (partition BY DOI,grp) sum_blocks,SUBOBJECT_NAME
     FROM(
SELECT   obj.OBJECT_ID,
                 obj.SUBOBJECT_NAME,
                 obj.DATA_OBJECT_ID     as DOI,
                 ext.relative_fno,
         ext.block_id,
         ( SUM(blocks) over () ) SUM,
         (SUM(blocks) over (ORDER BY DATA_OBJECT_ID,relative_fno, block_id)-0.01 ) sum_fno ,
         TRUNC( (SUM(blocks) over (ORDER BY DATA_OBJECT_ID,relative_fno, block_id)-0.01) / (SUM(blocks) over ()/ $5 ) ) grp,
         ext.blocks
FROM     dba_extents ext, dba_objects obj
WHERE    ext.segment_name = UPPER('$3')
AND      ext.owner        = UPPER('$2')
AND      obj.owner       =  ext.owner
AND      obj.object_name     = ext.segment_name
AND      obj.DATA_OBJECT_ID IS NOT NULL
ORDER BY DATA_OBJECT_ID, relative_fno, block_id
) order by  DOI,grp
);
spool off;
EOF
}

function partition_split
{
sqlplus -s $1
set linesize 200
set pages 0
set feedback off
spool list/rowid_range_$3_$4.lst
select rownum || ', ' ||' rowid between '||
chr(39)||dbms_rowid.rowid_create( 1, DOI, lo_fno, lo_block, 0 ) ||chr(39)|| ' and  ' ||
chr(39)||dbms_rowid.rowid_create( 1, DOI, hi_fno, hi_block, 1000000 )||chr(39) data
from (
SELECT DISTINCT DOI, grp,
first_value(relative_fno) over (partition BY DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) lo_fno,
first_value(block_id ) over (partition BY DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) lo_block,
last_value(relative_fno) over (partition BY DOI,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 DOI,grp order by relative_fno, block_id rows BETWEEN unbounded preceding AND unbounded following) hi_block,
SUM(blocks) over (partition BY DOI,grp) sum_blocks,SUBOBJECT_NAME
     FROM(
SELECT   obj.OBJECT_ID,
                 obj.SUBOBJECT_NAME,
                 obj.DATA_OBJECT_ID     as DOI,
                 ext.relative_fno,
         ext.block_id,
         ( SUM(blocks) over () ) SUM,
         (SUM(blocks) over (ORDER BY DATA_OBJECT_ID,relative_fno, block_id)-0.01 ) sum_fno ,
         TRUNC( (SUM(blocks) over (ORDER BY DATA_OBJECT_ID,relative_fno, block_id)-0.01) / (SUM(blocks) over ()/ $5 ) ) grp,
         ext.blocks
FROM     dba_extents ext, dba_objects obj
WHERE    ext.segment_name = UPPER('$3')
AND      ext.owner        = UPPER('$2')
AND      obj.owner       =  ext.owner
AND      obj.object_name     = ext.segment_name
AND      obj.DATA_OBJECT_ID IS NOT NULL
AND      obj.subobject_name=UPPER('$4')
ORDER BY DATA_OBJECT_ID, relative_fno, block_id
) order by  DOI,grp
);
spool off
EOF
}

sub_partition_name=$4

if [[ $sub_partition_name = 'x' ]]
then
normal_split $1 $2 $3 x $5
else
partition_split $1 $2 $3 $4 $5
fi

脚本比较长，需要的参数有5个，因为访问dba_extents,dba_objects需要一定的权限，可以使用dba权限的账号即可。
第2个参数是表的owner，第3个参数是表名，第4个参数是分区表名（如果是分区表就是分区表名，如果不是就填x）,第5个参数就是期望使用的并行度，能够在一定程度上加快速度
简单演示一下，可以通过下面的方式来运行脚本，我们指定生成10个dump这个表不是分区表。

ksh gen_rowid.sh n1/n1 prdowner subscriber_history x 10
1,  where  rowid between 'AAB4VPAAJAAD7qAAAA' and  'AAB4VPAAJAAD/R/EJA'
2,  where  rowid between 'AAB4VPAAJAAD/SAAAA' and  'AAB4VPAAKAABV5/EJA'
3,  where  rowid between 'AAB4VPAAKAABV6AAAA' and  'AAB4VPAALAAE/p/EJA'
4,  where  rowid between 'AAB4VPAALAAE/qAAAA' and  'AAB4VPAAMAAFFh/EJA'
5,  where  rowid between 'AAB4VPAAMAAFFiAAAA' and  'AAB4VPAAyAACuh/EJA'
6,  where  rowid between 'AAB4VPAAyAACuiAAAA' and  'AAB4VPAAzAACe5/EJA'
7,  where  rowid between 'AAB4VPAAzAACe6AAAA' and  'AAB4VPAA1AACZR/EJA'
8,  where  rowid between 'AAB4VPAA1AACZSAAAA' and  'AAB4VPAA2AACWR/EJA'
9,  where  rowid between 'AAB4VPAA2AACWSAAAA' and  'AAB4VPAA4AACP5/EJA'
10,  where  rowid between 'AAB4VPAA4AACQCAAAA' and  'AAB4VPAA5AACHx/EJA'
然后我们来看看数据是否足够平均。
可以类似下面的方式验证，我们抽第1,2,10个。

SQL> select count(*)from subscriber_history  where  rowid between 'AAB4VPAAJAAD7qAAAA' and  'AAB4VPAAJAAD/R/EJA'
  2  ;

  COUNT(*)
----------
    328759

SQL> select count(*)from  subscriber_history   where  rowid between 'AAB4VPAAJAAD/SAAAA' and  'AAB4VPAAKAABV5/EJA'
  2  /

  COUNT(*)
----------
    318021

SQL> select count(*)from subscriber_history  where  rowid between 'AAB4VPAA4AACQCAAAA' and  'AAB4VPAA5AACHx/EJA';

  COUNT(*)
----------
    332638

可以看到数据还是很平均的，达到了我们的期望。

时间： 2024-07-30 10:57:06

海量数据迁移之通过rowid切分大表的相关文章

海量数据迁移之分区并行切分

在海量的数据迁移中,如果某个表特别大,可以考虑对表中的分区进行切分,比如某个表有100g,还有100个分区,那么可以考虑针对这100个分区,那么可以考虑把这100个分区看成100个表进行并行抽取,如果某个分区数据比较多,可能生成5个dump,那么着100个分区,就可能生成105个分区以上. 那么如果有100多个表,那么可能分区都算进来就可能有上千个.如何对这上千个dump进行最快的加载呢. 可以考虑基于分区的并行切分,里面可能还涉及一些算法的知识. 目前生成了如下的数据报告,我们需要基于这个报告

海量数据迁移之使用分区并行切分导入

在之前的章节中讨论过怎么把一个很大的分区表切分为若干的dump文件,在数据加载的时候能够同时做基于每个分区的数据导入,如果有些分区比较大,有几十个dump文件,那么这个分区做数据导入的时候是不能再进行并行切分了. 现在在准生产环境中先查找了如下的表,charge,memo,charge_rel数量级都过亿,而且memo表中还含有lob字段.其他两个分区尽管字段没有特殊之处,但是分区数很多.都在几百个左右. charge  133036878memo 186700029    CHARGE_REL

海量数据迁移之传输表空间(一)

在自己接触的很多的数据迁移工作中,使用外部表在一定程度上达到了系统的预期,对于增量,批量的数据迁移效果还是不错的,但是也不能停步不前,在很多限定的场景中,有很多物理迁移中使用传统方法还是相当不错的,传输表空间就是一个样例. 最近的有一个数据迁移任务是需要把一些全新的数据表迁移到另外一个库中,因为这些表在目标库中不存在,所以使用逻辑迁移就显得有些力不从心了.尽管在速度可以接受的情况下,最大的痛处就是大量的归档文件了. 因为需要在原有的schema下增加一些全新的数据表,不是很肯定传输表空间的校验是

海量数据迁移之一个误操作的问题总结

在生产环境中的数据迁移还是很惊心动魄的,毕竟生产的数据不容许有任何潜在的问题,很小的问题也可能导致业务的终端,这个时候dba的角色是很重要的,如果dba犯了一个很细小的问题,在海量数据迁移中可能会导致灾难性的结果,所以今天和大家讨论一下关于由vi误操作导致的问题及总结. 结合今天早上的例子来说明. 目前生产环境已经有大量的用户数据了,需要从老系统迁移一批用户数据过来,一切都在安装好计划进行准备和操作.我是采用了外部表的方式,把一个很大的表分为了几十上百个外部表,采用insert方式加载的. 数据

海量数据迁移之分区表批量insert性能改进

在平时的工作中接触到的分区表一般都比较大,而且分区也少则几十,多则几百,上千. 在数据迁移的时候,分区表的迁移更是块大骨头,因为数据量太大,而且有些分区表中还有一些lob字段,想直接通过sqlldr来迁移还是需要做一些额外的工作. 如果通过datapump分区导出数据,批量导入,也是一种思路,不过需要考虑好并发的进程. 通过oracle_datapump来做数据的导入,可能更为灵活,但是不是绝对的.最近就做了一些相关的数据导入测试,感触不少. 比如,目前我们需要导入的两个大表,一个是memo,一

海量数据迁移之数据加载流程

在之前的博文中分享了关于数据抽取流程的一些思路,整体来说,数据的抽取是辅助,数据的加载是关键.加载的过程中每一步需要格外关注,稍有偏差就可能造成数据的损坏或者丢失. 为了更加清晰的说明通过外部表来实现数据加载的流程,特意画了如下的流程图. 在这个图中,数据的抽取是左边的部分,可以根据需要生成对应的外部表dump文件. 这个时候可以在目标环境中也创建只读用户,外部表用户,只读用户中只存放同义词,外部表用户中存放的是需要加载的外部表,整个外部表的加载过程不会消耗额外的物理空间,而且加载啊速度极快.

【重磅推荐】MySQL大表优化方案（最全面）

当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化: 单表优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑.部署.运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的.而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量: 字段 尽量使用TINYINT.SMALLINT.MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED VARCHAR的

如何应付表数据过大的查询问题?(如何尽量避免大表关联)

原文:如何应付表数据过大的查询问题?(如何尽量避免大表关联)      一般来说,对于做B/S架构的朋友来说,更有机会遇到高并发的数据库访问情况,因为现在WEB的普及速度就像火箭升空,同时就会因为高访问量带来一系列性能问题,而数据库一直是用户与商人之间交流的重要平台.用户是没有耐心忍受一个查询需要用上10秒以上的,或者更少些,如果经常出现服务器死机或者是报查询超时,我想那将是失败的项目.做了几年的WEB工作,不才,一直没有遇到过大访问量或者是海量数据的情况.这里并不是说没有海量数据的项目就不是好

PostgreSQL 用 CTE语法 + 继承 实现平滑拆分大表

标签 PostgreSQL , 拆分大表 , 继承 , cte 背景 业务设计初期可能不会考虑到表将来会有多大,或者由于数据日积月累,单表会变得越来越大. 后面在考虑分区的话,应该怎么将单表切换成分区表呢? 这里可以用到PostgreSQL的CTE语法,以及继承功能,还有内置的分区表功能. 例子 具体步骤 1.创建分区表 2.创建继承关系,分区表继承自需要拆分的表 3.用cte转移数据 4.全部转移完成后,在事务中切换表名 例子,将tbl_big切换成哈希分区 1.创建被迁移的大表 create