Oracle分区表之MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)
2021/4/12 19:25:18
本文主要是介绍Oracle分区表之MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)
In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner, but both methods required multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.
From 12.2, Alter Table <table_name> MODIFY clause can be used to convert non-partitioned table to a partitioned table.
For More details, Please see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5
本文参考自:https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2
从12.2开始,通过MODIFY TABLE可以零停机实现普通表转分区表。
一、创建测试表T1
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ); CREATE INDEX t1_created_date_idx ON t1(created_date); INSERT INTO t1 SELECT level, 'Description for ' || level, ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12) FROM dual CONNECT BY level <= 10000; COMMIT;
二、通过modify进行分区(1.在线分区 2.离线分区 3.在线分区+维护索引 4.二级分区)
-- Online operation. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ) ONLINE; -- Offline operation. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ); -- Online operation with modification of index partitioning. ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) ( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ) ONLINE UPDATE INDEXES ( t1_pk GLOBAL, t1_created_date_idx LOCAL ); --Composite Partition (Sub-Partition) a Table ALTER TABLE t1 MODIFY PARTITION BY RANGE (created_date) SUBPARTITION BY HASH (id)( PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2015_1, SUBPARTITION t1_sub_part_2015_2, SUBPARTITION t1_sub_part_2015_3, SUBPARTITION t1_sub_part_2015_4 ), PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2016_1, SUBPARTITION t1_sub_part_2016_2, SUBPARTITION t1_sub_part_2016_3, SUBPARTITION t1_sub_part_2016_4 ), PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) ( SUBPARTITION t1_sub_part_2017_1, SUBPARTITION t1_sub_part_2017_2, SUBPARTITION t1_sub_part_2017_3, SUBPARTITION t1_sub_part_2017_4 ) ) ONLINE UPDATE INDEXES ( t1_pk GLOBAL, t1_created_date_idx LOCAL );
三、分区后检查
-- Check indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS -------------------- ----------- -------- T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- Check index partitions. SELECT index_name, partition_name, status FROM user_ind_partitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME STATUS -------------------- -------------------- -------- T1_CREATED_DATE_IDX T1_PART_2015 USABLE T1_CREATED_DATE_IDX T1_PART_2016 USABLE T1_CREATED_DATE_IDX T1_PART_2017 USABLE SQL>
四、限制条件
Restrictions
There are some restrictions associated with this functionality.
- It can't be used to partition an index-organized table (IOT).
- It can't be used if the table has a domain index.
- You can only convert a table to a reference-partitioned child table in offline mode.
这篇关于Oracle分区表之MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-28pyqt 怎么打包整个项目-icode9专业技术文章分享
- 2024-09-28laravel Commands 创建带有参数的 Artisan 命令的步骤和示例-icode9专业技术文章分享
- 2024-09-28antd怎么实现渲染tiff图片-icode9专业技术文章分享
- 2024-09-28英文半角中划线和中文全角的中划线有什么区别-icode9专业技术文章分享
- 2024-09-28nvm npm 和node 他们之间有什么关系-icode9专业技术文章分享
- 2024-09-28Node Version Manager (nvm)使用教程-icode9专业技术文章分享
- 2024-09-28nvm命令太慢,是什么原因-icode9专业技术文章分享
- 2024-09-28Kotlin 如何增加、删除和修改 MutableStateFlow 中的值。-icode9专业技术文章分享
- 2024-09-28Kotlin的stateFlow.update 写法介绍-icode9专业技术文章分享
- 2024-09-28kotlin 怎么获取当前时间格式-icode9专业技术文章分享