8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
- Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle 12.2 Onward
- Partitioning an Existing Table using EXCHANGE PARTITION
- Partitioning an Existing Table using DBMS_REDEFINITION
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Setup
Create and populate a test table. You will need to repeat this between each test.
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;
We can see the data is spread across three years.
SELECT created_date, COUNT(*) FROM t1 GROUP BY created_date ORDER BY 1; CREATED_D COUNT(*) --------- ---------- 01-JAN-15 3340 01-JAN-16 3290 01-JAN-17 3370 SQL>
Partition a Table
We convert the table to a partitioned table using the ALTER TABLE ... MODIFY
command. Here are some basic examples of this operation. Adding the ONLINE
keyword allows the operation to be completed online, meaning most DML operations can continue during the conversion.
-- 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;
We gather statistics and check the table partitions. We can see the data has been split between the partitions as expected.
-- Gather statistics. EXEC DBMS_STATS.gather_table_stats(NULL, 'T1'); -- Check table partitions. SELECT table_name, partition_name, num_rows FROM user_tab_partitions ORDER BY 1,2; TABLE_NAME PARTITION_NAME NUM_ROWS -------------------- -------------------- ---------- T1 T1_PART_2015 3340 T1 T1_PART_2016 3290 T1 T1_PART_2027 3370 SQL>
When we check the indexes we see the index on the CREATED_DATE
column has been converted to a locally partitioned index. By default all prefixed indexes, an index with the partition key in the column list, will be converted to locally partitioned indexes. All indexes are left in a valid state at the end of the operation.
-- 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>
The offline operation is similar, but we omit the ONLINE
keyword. The outcome will be the same as the online operation, but DML will not be available during the operation.
-- 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')) );
We can influence the index conversion using the UPDATE INDEXES
clause. This can indicate the type of index to be rebuilt as part of the operation, as well as some storage parameters. In the following example we want the index on the CREATED_DATE
column to remain as a global index.
-- 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 GLOBAL );
After running the last example we can see the indexes as both non-partitioned, and as a result there are no index partitions.
-- Check indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS -------------------- ----------- -------- T1_CREATED_DATE_IDX NO VALID T1_PK NO VALID SQL> -- Check index partitions. SELECT index_name, partition_name, status FROM user_ind_partitions ORDER BY 1,2; no rows selected SQL>
Composite Partition (Sub-Partition) a Table
The original table can also be composite-partitioned using the ALTER TABLE ... MODIFY
command. In this example we convert the original table to a range-hash partitioned 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 );
The sub-partitions of the table and partitioned index can be displayed using the following queries.
COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN subpartition_name FORMAT A20 SELECT table_name, partition_name, subpartition_name FROM user_tab_subpartitions ORDER BY 1,2, 3; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -------------------- -------------------- -------------------- T1 T1_PART_2015 T1_SUB_PART_2015_1 T1 T1_PART_2015 T1_SUB_PART_2015_2 T1 T1_PART_2015 T1_SUB_PART_2015_3 T1 T1_PART_2015 T1_SUB_PART_2015_4 T1 T1_PART_2016 T1_SUB_PART_2016_1 T1 T1_PART_2016 T1_SUB_PART_2016_2 T1 T1_PART_2016 T1_SUB_PART_2016_3 T1 T1_PART_2016 T1_SUB_PART_2016_4 T1 T1_PART_2017 T1_SUB_PART_2017_1 T1 T1_PART_2017 T1_SUB_PART_2017_2 T1 T1_PART_2017 T1_SUB_PART_2017_3 T1 T1_PART_2017 T1_SUB_PART_2017_4 SQL> COLUMN index_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN subpartition_name FORMAT A20 SELECT index_name, partition_name, subpartition_name, status FROM user_ind_subpartitions ORDER BY 1,2; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS -------------------- -------------------- -------------------- -------- T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_1 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_2 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_3 USABLE T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_4 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_1 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_2 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_4 USABLE T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_3 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_1 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_3 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_2 USABLE T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_4 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.
For more information see:
- Converting a Non-Partitioned Table to a Partitioned Table
- ALTER TABLE
- Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle 12.2 Onward
- Partitioning an Existing Table using EXCHANGE PARTITION
- Partitioning an Existing Table using DBMS_REDEFINITION
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...