文档课题：将普通表转换为分区表.
数据库：oracle 11.2.0.4
本文包含以下知识点：
a、 将一个普通表转换为分区表的常用方法
b、 在线重定义
c、 ctas和insert的优化
d、 dml语句的并行操作以及确认
参考MOS (ID 1070693.6) How to Partition a Non-partitioned/Regular/Normal Table.
a、Export/import method
b、Insert with a subquery method
c、Partition exchange method
d、DBMS_REDEFINITION
1、Export/import
1.1、相关理论
采用逻辑导出导入，首先在源库建分区表，然后将数据导出，接着导入到新建的分区表，步骤如下：
a、导出表：exp user/passwd tables=numbers file=exp.dmp
b、删除表：drop table numbers;
c、重建分区表的定义：
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));
d、运用ignore=y导入分区表：imp user/passwd file=exp.dmp ignore=y
1.2、实际操作
1.2.1、建测试表
--创建普通表并插入测试数据.
HR@orcl150> create table t (id number primary key,time date);
Table created.
HR@orcl150> insert into t select rownum,created from dba_objects;
86384 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> select to_char(t.time,'yyyymm'),count(1)
2 from t
3 group by to_char(t.time,'yyyymm');
TO_CHA COUNT(1)
------ ----------
202301 186
201308 86198
HR@orcl150> col owner for a10
HR@orcl150> col SEGMENT_NAME for a15
HR@orcl150> col PARTITION_NAME for a15
HR@orcl150> col TABLESPACE_NAME for a15
HR@orcl150> r
1 select owner,
2 segment_name,
3 partition_name,
4 segment_type,
5 tablespace_name,
6 bytes,
7 extents
8 from dba_segments
9* WHERE segment_name = 'T'
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
---------- --------------- --------------- ------------------ --------------- ---------- ----------
HR T TABLE USERS 2097152 17
1.2.2、导数据
--采用expdp导出表数据.
[oracle@leo-oel150 ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=hr_t.dmp INCLUDE=TABLE:\"IN \(\'T\'\)\" SCHEMAS=HR LOGFILE=expdp_T.log
Export: Release 11.2.0.4.0 - Production on Tue Jan 17 15:58:08 2023
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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr_t.dmp INCLUDE=TABLE:"IN ('T')" SCHEMAS=HR 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 "HR"."T" 1.395 MB 86384 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl150/dpdump/hr_t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 17 15:58:14 2023 elapsed 0 00:00:05
1.2.3、删原表&建分区表
--删除原表并创建分区表结构：
HR@orcl150> drop table t;
Table dropped.
HR@orcl150> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )
2 PARTITION BY RANGE (TIME)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201309', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('202302', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE));
Table created.
1.2.4、导入分区表
[oracle@leo-oel150 ~]$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=hr_t.dmp SCHEMAS=HR table_exists_action=APPEND LOGFILE=impdp_T.log
Import: Release 11.2.0.4.0 - Production on Tue Jan 17 16:20:44 2023
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, 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=hr_t.dmp SCHEMAS=HR table_exists_action=APPEND LOGFILE=impdp_T.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "HR"."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 "HR"."T" 1.395 MB 86384 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Jan 17 16:20:48 2023 elapsed 0 00:00:04
1.2.5、数据验证
--查询导入后的数据.
HR@orcl150> select to_char(t.time,'yyyymm'),count(1) from t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(1)
------ ----------
202301 186
201308 86198
HR@orcl150> set line 200
HR@orcl150> col table_owner for a15
HR@orcl150> col table_name for a15
HR@orcl150> col partition_name for a15
HR@orcl150> 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
--------------- --------------- ---------------
HR T T1
HR T T2
HR T T3
HR@orcl150> col owner for a10
HR@orcl150> col segment_name for a15
HR@orcl150> col partition_name for a15
HR@orcl150> col tablespace_name for a15
HR@orcl150> select owner,segment_name,partition_name,segment_type,tablespace_name,bytes,extents from dba_segments where segment_name='T';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
---------- --------------- --------------- ------------------ --------------- ---------- ----------
HR T T1 TABLE PARTITION USERS 8388608 1
HR T T3 TABLE PARTITION USERS 8388608 1
2、运用原表重建分区表
--利用原表重建分区表.
优点：方法简单易用，由于采用DDL语句，不产生UNDO只产生少量REDO，效率较高，建表后数据分布到各个分区中.
缺点：对于数据一致性需要额外考虑.因为没有办法通过手工锁定T表来保证数据的一致性，在执行CREATE TABLE和RENAME T_NEW TO T语句时的直接修改可能会丢失.若要保证一致性，需在执行完语句后对数据进行检查，而此代价是非常大的.另外在执行两个RENAME语句之间对T表的访问会失败.该种方法主要有ctas和insert两种方式.
应用场景：适用于修改不频繁访问的表，在闲时进行操作，表的数据量不宜太大.
2.1、CTAS+RENAME
利用CTAS语法在创建分区表时可以一起插入数据，也可以创建好表结构再insert .CTAS采用DDL语句，不产生UNDO只产生少量REDO.
实际操作如下：
2.1.1、CTAS
--创建普通表并插入测试数据.
HR@orcl150> drop table t purge;
Table dropped.
HR@orcl150> select owner,segment_name,partition_name,segment_type,tablespace_name,bytes,extents from dba_segments where segment_name='T';
no rows selected
HR@orcl150> create table t (id number primary key,time date);
Table created.
HR@orcl150> insert into t select rownum,created from dba_objects;
86390 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 192
201308 86198
--创建分区表，注意分区表列的后边没有数据类型.
HR@orcl150> create table t_new (id,time) partition by range (time)
2 (partition t1 values less than (to_date('201309','yyyymm')),
3 partition t2 values less than (to_date('202302','yyyymm')),
4 partition t3 values less than (maxvalue))
5 as select id,time from t;
Table created.
2.1.2、RENAME
注意：两个RENAME语句之间对T表的访问会失败.
HR@orcl150> rename t to t1;
Table renamed.
HR@orcl150> rename t_new to t;
Table renamed.
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 192
201308 86198
HR@orcl150> select owner,segment_name,partition_name,segment_type,tablespace_name,bytes,extents from dba_segments where segment_name='T'
2 ;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
---------- --------------- --------------- ------------------ --------------- ---------- ----------
HR T T1 TABLE PARTITION USERS 8388608 1
HR T T3 TABLE PARTITION USERS 8388608 1
HR@orcl150> 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
--------------- --------------- ---------------
HR T T1
HR T T2
HR T T3
2.1.3、CTAS性能提升
对于CTAS建表语句性能的提升可以通过如下方式:
a、加nologging
b、并行DDL
c、查询并行
说明：建表完成后根据需要将表修改为logging模式.
HR@orcl150> drop table t purge;
Table dropped.
HR@orcl150> rename t1 to t;
Table renamed.
HR@orcl150> create table t_new (id,time) partition by range (time)
2 (partition t1 values less than (to_date('201309','yyyymm')),
3 partition t2 values less than (to_date('202302','yyyymm')),
4 partition t3 values less than (maxvalue))
5 nologging parallel 4
6 as select /*+parallel*/ id,time from t;
Table created.
--查执行计划
HR@orcl150> explain plan for create table t_new (id,time) partition by range (time)
2 (partition t1 values less than (to_date('201309','yyyymm')),
3 partition t2 values less than (to_date('202302','yyyymm')),
4 partition t3 values less than (maxvalue))
5 nologging parallel 4
6 as select /*+parallel*/ id,time from t;
Explained.
HR@orcl150> col PLAN_TABLE_OUTPUT for a120
HR@orcl150> r
1* select * from table(dbms_xplan.display())
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 550883001
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 95582 | 2053K| 31 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 95582 | 2053K| 10 (10)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_NEW | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 95582 | 2053K| 10 (10)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T | 95582 | 2053K| 10 (10)| 00:00:01 | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
17 rows selected.
可以看到对T表的查询是并行的，create table也是并行的，在源表数据量非常大的情况下性能显著
2.2、insert with a subquery method
说明：该方法是先建立表结构然后使用insert来实现.
2.2.1、insert数据
--创建普通表T_HR_20230117.
HR@orcl150> create table t_hr_20230117 (id number primary key,time date);
Table created.
HR@orcl150> insert into t_hr_20230117 select rownum,created from dba_objects;
86396 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t_hr_20230117 t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 198
201308 86198
--创建分区表t_hr_20230117_new
HR@orcl150> create table t_hr_20230117_new (id number,time date)
2 partition by range (time)
3 (partition t1 values less than (to_date('201309','yyyymm')),
4 partition t2 values less than (to_date('202302','yyyymm')),
5 partition t3 values less than (maxvalue));
Table created.
从源表查询插入到新表中.
HR@orcl150> alter table t_hr_20230117_new nologging;
Table altered.
HR@orcl150> alter session enable parallel dml;
Session altered.
HR@orcl150> insert /*+APPEND PARALLEL*/ into t_hr_20230117_new (id,time) select * from t_hr_20230117;
86396 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> explain plan for insert /*+APPEND PARALLEL*/ into t_hr_20230117_new (id,time) select * from t_hr_20230117;
Explained.
HR@orcl150> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2483390034
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 88057 | 1891K| 10 (10)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 88057 | 1891K| 10 (10)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_HR_20230117_NEW | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 88057 | 1891K| 10 (10)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_HR_20230117 | 88057 | 1891K| 10 (10)| 00:00:01 | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
17 rows selected.
HR@orcl150> drop table t_hr_20230117;
Table dropped.
HR@orcl150> rename t_hr_20230117_new to t_hr_20230117;
Table renamed.
2.2.2、验证数据
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t_hr_20230117 t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 198
201308 86198
HR@orcl150> select d.table_owner,d.table_name,d.partition_name from dba_tab_partitions d where d.table_name='T_HR_20230117';
TABLE_OWNER TABLE_NAME PARTITION_NAME
--------------- --------------- ---------------
HR T_HR_20230117 T1
HR T_HR_20230117 T2
HR T_HR_20230117 T3
HR@orcl150> select owner,segment_name,partition_name,segment_type,tablespace_name,bytes,extents from dba_segments where segment_name='T_HR_20230117';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
---------- --------------- --------------- ------------------ --------------- ---------- ----------
HR T_HR_20230117 T1 TABLE PARTITION USERS 8388608 1
HR T_HR_20230117 T2 TABLE PARTITION USERS 8388608 1
2.2.3、性能提升
INSERT性能提升方式：
① 表修改为nologging
② 禁用表上索引，可将数据插入完成后再建索引
③ 启用并行DML
④ 采用append方式插入
2.2.4、操作过程
HR@orcl150> explain plan for insert /*+APPEND PARALLEL*/ into t_hr_20230117 (id,time) select /*+PARALLEL(t_hr_20230117,4)*/ * from t_hr_20230117;
Explained.
HR@orcl150> col PLAN_TABLE_OUTPUT for a135
HR@orcl150> r
1* select * from table(dbms_xplan.display())
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 282814601
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 86396 | 1096K| 10 (0)| 00:00:01 | | | | | |
| 1 | LOAD AS SELECT | T_HR_20230117 | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 86396 | 1096K| 10 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 86396 | 1096K| 10 (0)| 00:00:01 | 1 | 3 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_HR_20230117 | 86396 | 1096K| 10 (0)| 00:00:01 | 1 | 3 | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
16 rows selected.
HR@orcl150> commit;
Commit complete.
HR@orcl150> alter session enable parallel dml;
Session altered.
HR@orcl150> explain plan for insert /*+APPEND PARALLEL*/ into t_hr_20230117 (id,time) select /*+PARALLEL(t_hr_20230117,4)*/ * from t_hr_20230117;
Explained.
HR@orcl150> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2617619301
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 86396 | 1096K| 10 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 86396 | 1096K| 10 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T_HR_20230117 | | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 86396 | 1096K| 10 (0)| 00:00:01 | 1 | 3 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_HR_20230117 | 86396 | 1096K| 10 (0)| 00:00:01 | 1 | 3 | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
16 rows selected.
说明：执行过alter session enable parallel dml后insert和select语句均使用到并行.
3、Partition exchange method
3.1、相关知识
交换分区优点：只是对数据字典中分区和表的定义进行修改，没有数据的修改或复制，效率最高.如果对数据在分区中的分布没有进一步要求的话，实现比较简单.在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T表中，可以保证对T表插入的操作不会丢失.
缺点：仍然存在一致性问题，交换分区后RENAME T_NEW TO T之前，dml操作会出现错误或访问不到数据.如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低.适用于包含大数据量的表转到分区表中的一个分区的操作.应尽量在闲时操作.
3.2、单分区
--单分区示例，创建普通表并插入测试数据.
HR@orcl150> drop table t purge;
Table dropped.
HR@orcl150> create table t (id number primary key,time date);
Table created.
HR@orcl150> insert into t select rownum,created from dba_objects where created<=to_date('201309','yyyymm');
86198 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
201308 86198
--创建分区表.
HR@orcl150> drop table t_new purge;
Table dropped.
HR@orcl150> create table t_new (id number primary key,time date) partition by range (time)
2 (partition t1 values less than (to_date('2013-09-01','yyyy-mm-dd')),
3 partition t2 values less than (maxvalue));
Table created.
--交换数据
HR@orcl150> alter table t_new exchange partition t1 with table t;
Table altered.
--修改表名，交换分区后RENAME T_NEW TO T之前，dml操作会出现错误或访问不到数据
HR@orcl150> rename t to t_old;
Table renamed.
HR@orcl150> rename t_new to t;
Table renamed.
--查询数据
R@orcl150> select to_char(t.time,'yyyymm'),count(*) from t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
201308 86198
3.3、多分区
3.3.1、流程步骤
交换分区操作步骤如下：
a、 创建分区表，假设有2个分区p1、p2
b、 创建表A存放p1规则的表
c、 创建表B存放p2规则的表
d、 用表A和p1分区交换，把表A的数据放到p1分区
e、 用表B和p2分区交换，把表B的数据放到p2分区
3.3.2、建分区表
--创建分区表，存在分区emp_p1、emp_p2.
HR@orcl150> create table p_emp
2 (sal number(8,2))
3 partition by range(sal)
4 (partition emp_p1 values less than (10000),
5 partition emp_p2 values less than (25000));
Table created.
HR@orcl150> col EMAIL for a10
HR@orcl150> col LAST_NAME for a10
HR@orcl150> col FIRST_NAME for a10
HR@orcl150> r
1* select * from employees
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- ---------- ---------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4569 17-JUN-11 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.456 21-SEP-09 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-09 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-14 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-15 IT_PROG 6000 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-15 IT_PROG 4200 103 60
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-15 ST_MAN 5800 100 50
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-11 ST_CLERK 3500 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-13 ST_CLERK 3100 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-14 ST_CLERK 2600 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-14 ST_CLERK 2500 124 50
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- ---------- ---------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-16 SA_MAN 10500 .2 100 80
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-12 SA_REP 11000 .3 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-14 SA_REP 8600 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-15 SA_REP 7000 .15 149
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-11 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-12 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-13 MK_REP 6000 201 20
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-10 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-10 AC_ACCOUNT 8300 205 110
20 rows selected.
3.3.3、建普通表
--创建表exchtab1、exchtab2分别存放salary<10000和salary<25000的数据。
HR@orcl150> create table exchtab1 as select salary from employees where salary<10000;
Table created.
HR@orcl150> create table exchtab2 as select salary from employees where salary between 10000 and 25000;
Table created.
3.3.4、交换分区
--表exchtab1、exchtab2与emp_p1、emp_p2分区交换
HR@orcl150> alter table p_emp exchange partition emp_p1 with table exchtab1;
Table altered.
HR@orcl150> alter table p_emp exchange partition emp_p2 with table exchtab2;
Table altered.
3.3.5、数据验证
HR@orcl150> col owner for a15
HR@orcl150> col PARTITION_NAME for a20
HR@orcl150> col TABLESPACE_NAME for a20
HR@orcl150> r
1* select owner,segment_name,partition_name,segment_type,tablespace_name,bytes,extents from dba_segments where segment_name='P_EMP'
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
--------------- -------------------- -------------------- ------------------ -------------------- ---------- ----------
HR P_EMP EMP_P1 TABLE PARTITION USERS 65536 1
HR P_EMP EMP_P2 TABLE PARTITION USERS 65536 1
HR@orcl150> select d.table_owner,d.table_name,d.partition_name from dba_tab_partitions d where d.table_name='P_EMP';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ --------------------
HR P_EMP EMP_P1
HR P_EMP EMP_P2
4、在线重定义
4.1、相关概念
参考Mos文档 [ID 472449.1]，在线重定义(DBMS_REDEFINITION)分区特点如下：
优点：保证数据一致性，大部分时间内对T表都可以正常进行DML操作.只在切换的瞬间锁表，具有很高的可用性和灵活性，对各种不同的需要都能满足.而且可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作.
不足：实现略显复杂.
在线重定义操作流程如下：
a、建基础表A，若存在则不需操作.
b、建临时分区表B结构.
c、开始重定义，将基表A的数据导入临时分区表B.
d、结束重定义，完成后在DB的 Name Directory里，已经将2个表进行了交换.即此时基表A成为分区表，创建的临时分区表B 成为普通表. 此后删除表B.
使用在线重定义的限制条件：
a、There must be enough space to hold two copies of the table.
b、Primary key columns cannot be modified.
c、Tables must have primary keys.
d、Redefinition must be done within the same schema.
e、New columns added cannot be made NOT NULL until after the redefinition operation.
f、Tables cannot contain LONGs, BFILEs or User Defined Types.
g、Clustered tables cannot be redefined.
h、Tables in the SYS or SYSTEM schema cannot be redefined.
i、Tables with materialized view logs or materialized views defined on them cannot be redefined.
j、Horizontal sub setting of data cannot be performed during the redefinition.
k、如果使用基于主键的方式，则原表与重定义后的表必须有相同的主键
l、如果使用基于ROWID的方式，则不能是索引组织表
m、原表上不能有物化视图或物化视图日志
n、不能是物化视图容器表、高级队列表、索引组织表的溢出表、临时表
o、在列映射时只能使用有确定结果的表达式，如子查询就不行
p、如果中间表有新增列，则不能有NOT NULL约束
q、原表和中间表之间不能有引用完整性
r、在线重定义无法采用nologging
在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：
Bug 7007594 - ORA-600 [12261]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx
4.2、实际操作
4.2.1、建表及索引
--创建普通表T_HR_20230118_UNPART及索引
HR@orcl150> create table t_hr_20230118_unpart (id number primary key,time date);
Table created.
HR@orcl150> insert into t_hr_20230118_unpart select rownum,created from dba_objects;
86408 rows created.
HR@orcl150> commit;
Commit complete.
HR@orcl150> create index create_date_indx on t_hr_20230118_unpart(time);
Index created.
HR@orcl150> exec dbms_stats.gather_table_stats(user,'T_HR_20230118_UNPART',cascade => true);
PL/SQL procedure successfully completed.
HR@orcl150> select to_char(t.time,'yyyymm'),count(*) from t_hr_20230118_unpart t group by to_char(t.time,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 210
201308 86198
4.2.2、建临时分区表
--创建临时分区表T_HR_20230118_PART，注意此处time列已换成created_date.
HR@orcl150> create table t_hr_20230118_part (id number primary key,created_date date)
2 partition by range (created_date)
3 (partition t1 values less than (to_date('201309','yyyymm')),
4 partition t2 values less than (to_date('202302','yyyymm')),
5 partition t3 values less than (maxvalue));
Table created.
4.2.3、检查条件
--然后执行以下存储过程检查是否可以在线重定义，若返回错误则不能在线重定义.
HR@orcl150> exec dbms_redefinition.can_redef_table(user,'T_HR_20230118_UNPART',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
说明：没有返回错误说明需要转换的表可以在线重定义，此过程需要时间，根据表的大小而定.
4.2.4、在线重定义
--开始在线重定义.
HR@orcl150> exec dbms_redefinition.start_redef_table(user,'T_HR_20230118_UNPART','T_HR_20230118_PART','id id,time created_date',dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
HR@orcl150> select count(*) from t_hr_20230118_unpart;
COUNT(*)
----------
86408
HR@orcl150> select count(*) from t_hr_20230118_part;
COUNT(*)
----------
86408
HR@orcl150> exec dbms_redefinition.sync_interim_table(user,'T_HR_20230118_UNPART','T_HR_20230118_PART');
PL/SQL procedure successfully completed.
此操作结束后，数据就同步到临时分区表.如果分区表和原表列名相同，则可以不加列的转换，若不同则需要加上转换，即重新指定映射关系.
另：exec dbms_redefinition.sync_interim_table(user,'T_HR_20230118_UNPART','T_HR_20230118_PART');是同步新表.
若在执行DBMS_REDEFINITION.START_REDEF_TABLE()和DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程中，在重定义的表上执行了大量DML操作，那么可以选择执行一次或多次SYNC_INTERIM_TABLE()过程，以减少最后执行FINISH_REDEF_TABLE()过程时的锁定时间.
--在新表上创建索引，在线重定义数据只重定义数据，索引还需要单独建立.
HR@orcl150> create index create_date_indx2 on t_hr_20230118_part (created_date);
Index created.
HR@orcl150> exec dbms_stats.gather_table_stats(user,'T_HR_20230118_PART',cascade => true);
PL/SQL procedure successfully completed.
--结束重定义
HR@orcl150> exec dbms_redefinition.finish_redef_table(user,'T_HR_20230118_UNPART','T_HR_20230118_PART');
PL/SQL procedure successfully completed.
说明：结束重定义DBMS_REDEFINITION.FINISH_REDEF_TABLE的意义.基表T_HR_20230118_UNPART和临时分区表T_HR_20230118_PART进行交换后，临时分区表T_HR_20230118_PART成为普通表，基表T_HR_20230118_UNPART成为分区表.重定义的时候，基表T_HR_20230118_UNPART是可以进行DML操作的. 只有在两个表进行切换时会有短暂的锁表.
4.2.5、验证数据
--最后删除临时表并给索引重命名.
HR@orcl150> select d.table_name,partitioned from user_tables d where table_name like '%T_HR_20230118%';
TABLE_NAME PAR
------------------------------ ---
T_HR_20230118_PART NO
T_HR_20230118_UNPART YES
HR@orcl150> select d.table_name,partition_name
2 from user_tab_partitions d
3 where table_name='T_HR_20230118_UNPART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_HR_20230118_UNPART T1
T_HR_20230118_UNPART T2
T_HR_20230118_UNPART T3
HR@orcl150> drop table t_hr_20230118_part;
Table dropped.
HR@orcl150> alter index create_date_indx2 rename to create_date_indx;
Index altered.
HR@orcl150> select to_char(t.created_date,'yyyymm'),count(*) from t_hr_20230118_unpart t group by to_char(t.created_date,'yyyymm');
TO_CHA COUNT(*)
------ ----------
202301 210
201308 86198
参考网址：http://blog.itpub.net/26736162/viewspace-2109454/
将普通表转换为分区表
原创
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
如何将oralce普通表转换为分区表
本文档 ID 1985005.1 如何将oralce普通表转换为分区表，此前面试被问到过，虽然知道在线重定义，个人觉得很简单
在线重定义 普通表转分区表 分区表 SQL 重定义 -
【数据库】pgsql将普通表转换为分区表
11111111111111111111111
html javascript -
oracle 11.2 普通表转换为分区表的常见方法
oracle 11.2 普通表转换为分区表的常见方法
oracle 11.2 普通表转换为分区
