mysql数据仓库 建分区表_数据仓库超级大表分区方式改变

在我们的数据仓库使用oracle其中有很多超级大表，这些表的分区方式基本都是range+hash分区或list+hash分区。使用时间字段进行range或list分区，再用用户ID进行子分区。不过最近有人反映有一张表select起来很慢，进过查看发现原来这个表只进行了list分区，没有进行hash子分区。讨论后决定对该表的分区方式进行改变：从list分区改为list+hash分区。操作过称如下：

1.前期准备

为了确定迁移策略先看看这个表有多大。

select owner, segment_name, sum(bytes) / 1024 / 1024 / 1024 size_G

from dba_segments

where owner = 'user'

and segment_name = 'TABLE'

发现这个表有570多G。

这个表已经有二十多个分区，每个分区的记录数是将近10亿(是不是有点崩溃，这么大的表~)。

确定策略：为了不影响在线表的服务，先建一个预备表TABLE_NEW使用list+hash分区，然后一个分区一个分区的叫TABLE表的数据insert到TABLE_NEW表，再后将TABLE表改名为TABLE_OLD，将TABLE_NEW改成TABLE，最后对这个表给其他用户赋权限。

2. 倒出TABLE表的建表语句

通过查看TABLE表的建表DDL，得到建表语句。

查询数据库的object的DDL信息：

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','user') from dual;

修改建表语句并建TABLE_OLD表

3. 数据迁移

迁移程序日下

#!/usr/bin/bash

LOGFILE=TABLE.log

sqlplus user/password@DB <> ${LOGFILE}

set serverout on size 1000000

set timing on

alter session enable parallel dml;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

PROMPT Starting to insert into TABLE

select sysdate from dual;

declare

v_commit        number;

v_sql           varchar2(3000);

error_msg       varchar2(300);

begin

for rec in (select partition_name from dba_tab_partitions

where table_owner = 'user' and table_name = 'TABLE'

order by partition_name) loop

begin

v_sql :='insert /*+ append */into user.TABLE_NEW

(STATIS_MONTH,

....

.....)

select STATIS_MONTH,

......

......

......

from user.TABLE  partition('||rec.partition_name||')';

Execute Immediate v_sql;

v_commit := Sql%Rowcount;

commit;

select sysdate from dual;

dbms_output.put_line(rec.partition_name||' commit records'||v_commit);

--如果一个分区出错，试下一个分区

exception

when others then

error_msg := substr(sqlerrm, 1, 240);

dbms_output.put_line(||rec.partition_name||' - '||error_msg);

end;

end loop;

end;

exit;

EOD

确认每个分区的数据都迁移成功

4. --修改表名

sqlplus user/password@DB

rename TABLE to TABLE_OLD;

rename TABLE_NEW to TABLE;

5.对其他用户赋权限