前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle普通表->分区表转换(9亿数据量)

Oracle普通表->分区表转换(9亿数据量)

作者头像
Alfred Zhao
发布2019-05-24 20:48:44
1.8K0
发布2019-05-24 20:48:44
举报

背景介绍:

环境:Linux 5.5 + Oracle 10.2.0.4

某普通表T,由于前期设计不当没有分区,如今几年来的数据量已达9亿+, 空间占用大约350G,在线重定义为分区表不现实,故采取申请时间窗口停此表应用,改造为分区表。

若T表数据量适当,可选用在线重定义操作时,可参考:http://www.cnblogs.com/jyzhao/p/3876634.html

  • 1.创建分区表
  • 2.设置新建分区表为nologging, 重命名原表T为T_OLD
  • 3.并行直接路径插入
  • 4.为分区表建立索引
  • 5.rename表,恢复T表的相关应用

1.创建分区表

-- Create table 创建分区表T_PART,分区从14年6月开始。

代码语言:javascript
复制
create table T_PART
(
……
)
partition by range(time_stamp)(
  partition P20140601 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace DBS_D_JINGYU
);

使用分区添加工具添加到15年6月份。

2.设置新建分区表为nologging, 重命名原表T为T_OLD

代码语言:javascript
复制
alter table t_part nologging;

rename T to T_old;

3.并行直接路径插入

代码语言:javascript
复制
alter session enable parallel dml; 
代码语言:javascript
复制
insert /*+ append parallel(p,10) */ into t_part p select /*+ parallel(n,10) */ * from T_old n;

commit;

查看下insert的执行计划,确定都能用到并行度。

代码语言:javascript
复制
explain plan for insert /*+ append parallel(p,10) */ into t_part p select /*+ parallel(n,10) */ * from T_old n;

执行插入脚本

代码语言:javascript
复制
SQL> @/home/oracle/insert

~~~~~~~~~~~~~~~~~~~~~~~~~

已创建908792694行。

已用时间:  02: 09: 37.94

提交完成。

已用时间:  00: 08: 13.76

4.为分区表建立索引

4.1 重命名历史表的索引名

代码语言:javascript
复制
alter index PK_T rename to PK_T_bak;
alter table T_old rename constraint PK_T to PK_T_bak;
alter index IDX_T_2 rename to IDX_T_2_bak;
alter index IDX_T_3 rename to IDX_T_3_bak;

4.2 给新分区表T_PART创建主键及索引

代码语言:javascript
复制
create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  04: 39: 53.10
alter table T_PART add constraint PK_T primary key (OID, TIME_STAMP, SERIAL_NO, CITY_ID);
表已更改。
已用时间:  00: 00: 00.43
代码语言:javascript
复制
create index IDX_T_2 on T_PART (TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 27: 49.92
create index IDX_T_3 on T_PART (TIME_STAMP, CITY_ID) local tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 19: 06.74

4.3 修改索引和表为logging,noparallel

代码语言:javascript
复制
alter index PK_T logging noparallel;
alter index IDX_T_2 logging noparallel;
alter index IDX_T_3 logging noparallel;
alter table T_PART logging;

4.4 遇到的问题

建立唯一性索引时报错:

代码语言:javascript
复制
SQL> create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_i_jingyu nologging parallel 32;

create unique index PK_T on T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID) local tablespace dbs_i_jingyu nologging parallel 32

ORA-12801: 并行查询服务器 P000 中发出错误信号

ORA-01652: 无法通过 128 (在表空间 TMP 中) 扩展 temp 段

解决方式:增加临时表空间大小

代码语言:javascript
复制
alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp02.dbf' size 30G;

alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp03.dbf' size 30G;

alter tablespace TMP add tempfile '/usr3/oradata2/sysdata/tmp04.dbf' size 30G;

5.rename表,恢复T表的相关应用

rename T_PART为T,恢复T表应用。

代码语言:javascript
复制
rename T_PART to T;

根据实际情况决定是否彻底drop掉T_OLD,释放空间。

代码语言:javascript
复制
drop table T_OLD purge;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2015-04-02 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1.创建分区表
  • 2.设置新建分区表为nologging, 重命名原表T为T_OLD
  • 3.并行直接路径插入
  • 4.为分区表建立索引
    • 4.1 重命名历史表的索引名
      • 4.2 给新分区表T_PART创建主键及索引
        • 4.3 修改索引和表为logging,noparallel
          • 4.4 遇到的问题
          • 5.rename表,恢复T表的相关应用
          相关产品与服务
          日志服务
          日志服务(Cloud Log Service,CLS)是腾讯云提供的一站式日志服务平台,提供了从日志采集、日志存储到日志检索,图表分析、监控告警、日志投递等多项服务,协助用户通过日志来解决业务运维、服务监控、日志审计等场景问题。
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档