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

0    192    1

Tags:

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

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~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) 重建分区表的定义:

create table numbers (qty number(3), name varchar2(15))

partition by range (qty)

(partition p1 values less than (501),

partition p2 values less than (maxvalue));

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

示例

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

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87069 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1107

采用expdp导出表

ZFXDESKDB2:oracle:/tmp>expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:\"IN \(\'T\'\)\" SCHEMAS=LHR LOGFILE=expdp_T.log

Export: Release 11.2.0.4.0 - Production on Fri May 27 11:07:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:"IN ('T')" SCHEMAS=LHR LOGFILE=expdp_T.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2 MB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "LHR"."T" 1.406 MB 87091 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/oracle/app/oracle/admin/dlhr/dpdump/lhr_t.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 27 11:07:57 2016 elapsed 0 00:00:11

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

LHR@dlhr> drop table t;

Table dropped.

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )

2 PARTITION BY RANGE (TIME)

3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

5 PARTITION T3 VALUES LESS THAN (MAXVALUE))

6 ;

Table created.

LHR@dlhr>

导入到分区表

ZFXDESKDB2:oracle:/tmp>impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log

Import: Release 11.2.0.4.0 - Production on Fri May 27 11:12:40 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Table "LHR"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "LHR"."T" 1.406 MB 87091 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri May 27 11:12:46 2016 elapsed 0 00:00:05

查询导入后的情况:

SYS@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1083

SYS@dlhr> SELECT D.TABLE_OWNER,D.TABLE_NAME,D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='T';

TABLE_OWNER TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------ ------------------------------

LHR T T1

LHR T T2

LHR T T3

SYS@dlhr>

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

这种方法的特点是:

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

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

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

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

例一:CTAS+RENAME

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

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

LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

Table created.

LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

87069 rows created.

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)

2 from t

3 group by to_char(t.time, 'YYYYMM');

TO_CHA COUNT(1)

------ ----------

201310 85984

201605 1085

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

LHR@dlhr> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),

3 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),

4 PARTITION T3 VALUES LESS THAN (MAXVALUE))

5 AS SELECT ID, TIME FROM T;

Table created.

LHR@dlhr>

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle中如何将一个普通表转换为分区表后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

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

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部