Oracle中如何将一个普通表转换为分区表

0    387    1

Tags:

👉 本文共约5530个字,系统预计阅读时间或需21分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① 将一个普通表转换为分区表的常用方法(重点)

② 在线重定义的使用

③ ctas和insert的优化

④ DML语句如何开启并行操作,如何查看DML是否开启了并行

相关参考文章链接

参考文档都是MOS上How to Partition a Non-partitioned / Regular / Normal Table (文档 ID 1070693.6),已上传到云盘,大家可自行下载。

本文简介

本文介绍了4种非分区表转换为分区表的几种方法,参考文档来自于MOS。

将普通表转换成分区表有4种方法,这个在MOS文档上有说明(How to Partition a Non-partitioned / Regular / Normal Table (文档 ID 1070693.6)):

  1. Export/import method

  2. Insert with a subquery method

  3. Partition exchange method

  4. DBMS_REDEFINITION

非分区表转换为分区表的4种方法

导出/导入方法(Export/Import Method)

采用逻辑导出导入很简单,首先在源库建立分区表,然后将数据导出,然后导入到新建的分区表即可,

1) 导出表:exp usr/pswd tables=numbers file=exp.dmp

2) 删除表:drop table numbers;

3) 重建分区表的定义:

  1. 利用ignore=y来导入分区表:imp usr/pswd file=exp.dmp ignore=y

示例

创建普通表并插入测试数据

采用expdp导出表

删除原表,创建一个分区表结构:

导入到分区表

利用原表重建分区表(插入)

这种方法的特点是:

优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。

不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。

主要有2种方式,ctas和insert方式,下边分别介绍:

例一:CTAS+RENAME

利用CTAS语法在创建分区表的时候可以一起插入数据,也可以创建好表结构再insert 进去。 CTAS这种方法采用DDL语句,不产生UNDO,只产生少量REDO,建表完成后数据已经在分布到各个分区中。

创建普通表并插入测试数据

创建一个分区表,注意这里的分区表的列后边没有数据类型:

ctas性能提升

对于CTAS建表语句性能的提升可以通过如下的方式,① 加nologging ② 并行DDL ③ 查询并行,需要说明的是建表完成后根据需要将表修改为logging模式。

可以看到对T表的查询是并行的,create table也是并行的,这在源表的数据量非常大的情况下性能显著。

例二: Insert with a subquery method

这种方法就是先建立表结构然后使用insert 来实现。

看示例:

创建普通表T_LHR_20160527

insert性能提升

INSERT性能提升的方式,① 表修改为nologging ② 禁用表上的索引,可以将数据插入完成后再建索引 ③ 启用并行DML alter session enable parallel dml; ④ 采用 append方式插入

使用交换分区的方法(Partition exchange method)

这种方法的特点

优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。

不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

单个分区示例

举例来说明

创建普通表并插入测试数据

多个分区示例

交换分区的操作步骤如下:

  1. 创建分区表,假设有2个分区,P1,P2.

  2. 创建表A存放P1规则的数据。

  3. 创建表B 存放P2规则的数据。

  4. 用表A 和P1 分区交换。 把表A的数据放到到P1分区

  5. 用表B 和p2 分区交换。 把表B的数据存放到P2分区。

MOS上的例子

利用在线重定义功能(DBMS_REDEFINITION)

优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。

不足:实现上比上面两种略显复杂。

适用于各种情况。

在线重定义的大致操作流程如下:

(1)创建基础表A,如果存在,就不需要操作。

(2)创建临时的分区表B结构。

(3)开始重定义,将基表A的数据导入临时分区表B。

(4)结束重定义,完成后在DB的 Name Directory里,已经将2个表进行了交换。即此时基表A成了分区表,我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

主要过程如下所示:

MOS上的文档:

在线重定义的相关知识

在线重定义功能

这个功能只在9.2.0.4以后的版本才有,在线重定义表具有以下功能:

(1)修改表的存储参数;

(2)将表转移到其他表空间;

(3)增加并行查询选项;

(4)增加或删除分区;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复