Oracle 12c 新特性 --- ONLINE Move Partition
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 12c 新特性 --- ONLINE Move Partition，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6084字，纯文字阅读大概需要9分钟。
内容图文
概述
ALTER TABLE ... MOVE PARTITION becomes non-blocking online DDL while DML operations continue to run uninterrupted on the partition that is being moved. Global indexes are maintained during the move partition, so a manual index rebuild is no longer required.
The online partitioning movement removes the read-only state for the actual MOVE PARTITION command.
ALTER TABLE……MOVE分区变成了非阻塞的在线DDL，而DML操作继续在被移动的分区上不受干扰地运行。在移动分区期间维护全局索引，因此不再需要手工索引重建。
在线分区移动删除了实际移动分区命令的只读状态。
Use the ALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITION statement to move a table partition or subpartition, respectively.
When you use the ONLINE keyword with either of these statements, DML operations can continue to run uninterrupted on the partition or subpartition that is being moved. If you do not include the ONLINE keyword, then DML operations are not permitted on the data in the partition or subpartition until the move operation is complete.
When you include the UPDATE INDEXES clause, these statements maintain both local and global indexes during the move. Therefore, using the ONLINE keyword with these statements eliminates the time it takes to regain partition performance after the move by maintaining global indexes and manually rebuilding indexes.
使用ALTER TABLE……移动分区语句或ALTER TABLE…移动子分区语句，分别移动表分区或分区。
当您使用这些语句的在线关键字时，DML操作可以继续在被移动的分区或分区上持续运行。如果不包括在线关键字，则在分区或子分区中的数据上不允许DML操作，直到移动操作完成为止。
当包含UPDATE INDEXES子句时，这些语句在移动过程中维护本地和全局索引。因此，在这些语句中使用在线关键字消除了通过维护全局索引和手工重建索引来恢复分区性能所需的时间。
实验
1) online move a table partition.
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);
Table created.
SQL> INSERT INTO t1
SELECT level,
'Description for ' || level,
CASE
WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
END
FROM dual
CONNECT BY level <= 1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1');
PL/SQL procedure successfully completed.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> SELECT table_name,
partition_name,
TABLESPACE_NAME,
num_rows
FROM user_tab_partitions where table_name='T1'
ORDER BY 1,2;
2 3 4 5 6
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
T1 PART_2014 USERS 500
T1 PART_2015 USERS 500
SQL> ALTER TABLE t1 MOVE PARTITION part_2015 TABLESPACE test UPDATE INDEXES;
Table altered.
SQL> SELECT table_name,
partition_name,
TABLESPACE_NAME,
num_rows
FROM user_tab_partitions where table_name='T1'
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
T1 PART_2014 USERS 500
T1 PART_2015 TEST 500
2) online move a table sub-partition.
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> CREATE TABLE t1
(id NUMBER,
description VARCHAR2(50),
created_date DATE)
PARTITION BY RANGE (created_date)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);
Table created.
SQL> INSERT INTO t1
SELECT level,
'Description for ' || level,
CASE
WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
END
FROM dual
CONNECT BY level <= 1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1', granularity => 'SUBPARTITION');
PL/SQL procedure successfully completed.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN TABLESPACE_NAME FORMAT A20
SQL> set lines 200
SQL> SELECT table_name,
partition_name,
subpartition_name,
TABLESPACE_NAME,
num_rows
FROM user_tab_subpartitions where table_name='T1'
ORDER BY 1,2,3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- -------------------- ----------
T1 PART_2014 SYS_SUBP346 USERS 107
T1 PART_2014 SYS_SUBP347 USERS 136
T1 PART_2014 SYS_SUBP348 USERS 121
T1 PART_2014 SYS_SUBP349 USERS 136
T1 PART_2015 SYS_SUBP350 USERS 127
T1 PART_2015 SYS_SUBP351 USERS 108
T1 PART_2015 SYS_SUBP352 USERS 140
T1 PART_2015 SYS_SUBP353 USERS 125
8 rows selected.
SQL> ALTER TABLE t1 MOVE SUBPARTITION SYS_SUBP353 ONLINE TABLESPACE test UPDATE INDEXES;
Table altered.
SQL> SELECT table_name,
partition_name,
subpartition_name,
TABLESPACE_NAME,
num_rows
FROM user_tab_subpartitions where table_name='T1'
ORDER BY 1,2,3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- -------------------- ----------
T1 PART_2014 SYS_SUBP346 USERS 107
T1 PART_2014 SYS_SUBP347 USERS 136
T1 PART_2014 SYS_SUBP348 USERS 121
T1 PART_2014 SYS_SUBP349 USERS 136
T1 PART_2015 SYS_SUBP350 USERS 127
T1 PART_2015 SYS_SUBP351 USERS 108
T1 PART_2015 SYS_SUBP352 USERS 140
T1 PART_2015 SYS_SUBP353 TEST 125
8 rows selected.
内容总结
以上是互联网集市为您收集整理的Oracle 12c 新特性 --- ONLINE Move Partition全部内容，希望文章能够帮你解决Oracle 12c 新特性 --- ONLINE Move Partition所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】