Converting Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)
Author :
Gaurav Tewari
Process is very much similar to earlier releases but now the
same can be done online with no downtime, enabling concurrent DML operations
while the conversion is ongoing & avoiding multiple steps like using
redefinition or exchange partition per previous releases.
Before proceeding be ready to confirm on limitations (restrictions) –
- This can't be used to partition an index-organized table (IOT).
- This can't be used if the table has a domain indexes.
- Can only convert a table to a reference-partitioned
child table in offline mode.
1.
Create the non-partitioned table:
CREATE TABLE TEST1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT TEST1_pk PRIMARY KEY (id)
);
2.
Create Index on column created_date:
CREATE INDEX TEST1_created_date_idx ON
TEST1(created_date);
3.
Populate the table :
INSERT INTO TEST1 SELECT level, 'Description for ' || level,
ADD_MONTHS(TO_DATE('01-JAN-2020', 'DD-MON-YYYY'),
-TRUNC(DBMS_RANDOM.value(1,4)-1)*12) FROM dual CONNECT
BY level <= 10000;
COMMIT;
4.
Check the data , so data is available for each
year :
SELECT created_date, COUNT(*) FROM TEST1 GROUP BY created_date ORDER BY 1;
CREATED_D
COUNT(*)
--------- ----------
01-JAN-17
5011
01-JAN-18 6549
01-JAN-19 8600
5.
Can do partitioning ONLINE as below options without
index partitioning or with index partitioning:
-- Online operation without index partitioning
ALTER TABLE TEST1 MODIFY
PARTITION BY RANGE (created_date) (
PARTITION TEST1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY')),
PARTITION TEST1_part_2018 VALUES LESS THAN
(TO_DATE('01-JAN-2019','DD-MON-YYYY')),
PARTITION TEST1_part_2019 VALUES LESS THAN
(TO_DATE('01-JAN-2020','DD-MON-YYYY'))
)
ONLINE;
-- Online operation with modification of index
partitioning.
ALTER TABLE TEST1 MODIFY
PARTITION BY RANGE (created_date) (
PARTITION TEST1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY')),
PARTITION TEST1_part_2018 VALUES LESS THAN
(TO_DATE('01-JAN-2019','DD-MON-YYYY')),
PARTITION TEST1_part_2019 VALUES LESS THAN
(TO_DATE('01-JAN-2020','DD-MON-YYYY'))
)
ONLINE
UPDATE INDEXES
(
TEST1_pk GLOBAL,
TEST1_created_date_idx LOCAL
);
6.
Verify the partitions ( table & index )
created :
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
SELECT table_name, partition_name FROM
user_tab_partitions ORDER BY 1,2;
TABLE_NAME PARTITION_NAME
--------------------
--------------------
TEST1 TEST1_PART_2017
TEST1 TEST1_PART_2018
TEST1 TEST1_PART_2019
COLUMN index_name FORMAT A20
COLUMN partition_name FORMAT A20
SELECT index_name, partition_name,
status FROM user_ind_partitions ORDER
BY 1,2;
INDEX_NAME PARTITION_NAME STATUS
--------------------
-------------------- --------------------------------------
TEST1_CREATED_DATE_IDX TEST1_PART_2017 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2018 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2019 USABLE
Sub-partition (Composite Partition) on non-partitioned
table:
The same table as above can be sub-partitioned like to Range-Hash
partitioned table,
ALTER TABLE
TEST1 MODIFY
PARTITION BY RANGE (created_date)
SUBPARTITION BY HASH (id)(
PARTITION TEST1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY')) (
SUBPARTITION TEST1_sub_part_2017_1,
SUBPARTITION TEST1_sub_part_2017_2,
SUBPARTITION TEST1_sub_part_2017_3,
SUBPARTITION TEST1_sub_part_2017_4
),
PARTITION TEST1_part_2018 VALUES LESS THAN
(TO_DATE('01-JAN-2019','DD-MON-YYYY')) (
SUBPARTITION TEST1_sub_part_2018_1,
SUBPARTITION TEST1_sub_part_2018_2,
SUBPARTITION TEST1_sub_part_2018_3,
SUBPARTITION TEST1_sub_part_2018_4
),
PARTITION TEST1_part_2019 VALUES LESS THAN
(TO_DATE('01-JAN-2020','DD-MON-YYYY')) (
SUBPARTITION TEST1_sub_part_2019_1,
SUBPARTITION TEST1_sub_part_2019_2,
SUBPARTITION TEST1_sub_part_2019_3,
SUBPARTITION TEST1_sub_part_2019_4
)
) ONLINE
UPDATE INDEXES
(
TEST1_pk GLOBAL,
TEST1_created_date_idx LOCAL
);
Verify the creation of Sub-Paritions :
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
--------------------
-------------------- -----------------------------------------------------------
TEST1 TEST1_PART_2017 TEST1_SUB_PART_2017_1
TEST1 TEST1_PART_2017 TEST1_SUB_PART_2017_2
TEST1 TEST1_PART_2017 TEST1_SUB_PART_2017_3
TEST1 TEST1_PART_2017 TEST1_SUB_PART_2017_4
TEST1 TEST1_PART_2018 TEST1_SUB_PART_2018_1
TEST1 TEST1_PART_2018 TEST1_SUB_PART_2018_2
TEST1 TEST1_PART_2018 TEST1_SUB_PART_2018_3
TEST1 TEST1_PART_2018 TEST1_SUB_PART_2018_4
TEST1 TEST1_PART_2019 TEST1_SUB_PART_2019_1
TEST1 TEST1_PART_2019 TEST1_SUB_PART_2019_2
TEST1 TEST1_PART_2019 TEST1_SUB_PART_2019_3
TEST1 TEST1_PART_2019 TEST1_SUB_PART_2019_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
--------------------
-------------------- -------------------- -------------------------------------------------------------------
TEST1_CREATED_DATE_IDX TEST1_PART_2017 TEST1_SUB_PART_2017_1 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2017 TEST1_SUB_PART_2017_2 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2017 TEST1_SUB_PART_2017_3 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2017 TEST1_SUB_PART_2017_4 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2018 TEST1_SUB_PART_2018_1 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2018 TEST1_SUB_PART_2018_2 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2018 TEST1_SUB_PART_2018_4 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2018 TEST1_SUB_PART_2018_3 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2019 TEST1_SUB_PART_2019_1 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2019 TEST1_SUB_PART_2019_3 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2019 TEST1_SUB_PART_2019_2 USABLE
TEST1_CREATED_DATE_IDX TEST1_PART_2019 TEST1_SUB_PART_2019_4 USABLE
SQL>
Do Share your comments & experiences on Oracle 12cR2 .... as there's a good saying "Knowledge Sharing is Knowledge Gaining" ....
Cheers :)
Comments
Post a Comment