背景:公司要求将数据库中表超过2G的表进行分区。刚好我们负责的表有5张大于2G的。所以需要进行表分区。
所谓分区,讲的的通俗点,就是根据某个字段的范围进行划分,将表分成几块。以后查询,维护都更加方便。
比如可以根据创建时间,2018年的数据放入一个分区,以后每3年存入一个分区。
实际环境:我负责的这个表,大小是12G作右,接近7千万条数据。
因为这个表已经使用了很多年了,刚开始建表的时候没有考虑到数据会变的这么大。所以没有建分区。现在的思路是重新建立一张分区表和源表一模一样,不同的是该表进行了分区。然后把7千万条数据导入到该表。最后将两个表互换名字。
难点:难点有几个:1.导入数据,7千万的数据不能简单的insert...select。因为它是自动commit。可能最后commit时服务器都撑爆了。 2.换名字。两个表互换名字,如果将新表插入了数据,此时源表还在进行数据的增删改怎么办,这样数据就没有同步了。
3.索引的问题,建表的时候不要建索引,数据插入完后再建索引。不然插入数据时效率较低。
实际操作:由于公司的保密,语句不能copy出来。
第一:根据源表创建分区表,根据创建时间字段,18年以前的数据为一分区,后面每年一分区。
create table A_temp as select * from A where 1=0 partition by RANGE(create_time)
INTERVAL (numtoyminterval(1,'year'))
(partition part_t01 values less than(to_date('2018-01-01', 'yyyy-mm-dd')));
第二:表注释和字段注释以及默认值和不为空的设置,付权限,同义词。
comment on table A_temp is 'XXX';
comment on column A.xxx is 'ccc';......
第三:插入数据,采用并行插入,提供效率。尽量在用户使用量小的时候做表分区。比如我们在晚上11:30部署,基本没有用户做增删改操作。并且设置源表为只读。
alter table A read only;
//分批插入
declare
vd_start_date date;
vd_end_date date;
begin
vd_start_date:=to_date('20090301','YYYYMMDD'),
vd_end_date:=to_date('20191130','YYYYMMDD');
while vd_start_date<=vd_end_date
loop
insert /* append parallel(A,4) */ into A_temp A
select /* append parallel(T,4) */ from A
where created_date>=vd_start_date and
created_date<add_month(v_start_date,3);
commit;
vd_start_date:=add_month(vd_start_date,3);
end loop;
end;
/;
第五:建索引,约束。数据插入完后再建索引和约束。效率比较高
create index xxx on A_temp(xxx);...
第六:建同义词。
create public synonym for A_temp;
第七:解除原表只读。
alter table A read write;
第八:给新表赋权限:
grant select on A_temp to XXX;
第九:表名交换。
alter table A rename to A_new;
alter table A_temp rename to A;
这样,就重新建了一张表A_temp.和表A一样,并且是已经分区了的。从而替代了A.
最后可以删除A_new.