8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Online MERGE PARTITION and MERGE SUBPARTITION in Oracle Database 18c
In Oracle Database 18c the MERGE PARTITION
and MERGE SUBPARTITION
operations on heap tables can be performed online so they don't block DML. This is done by the addition of the ONLINE
keyword, which also causes local and global indexes to be updated without having to specify the UPDATE INDEXES
clause.
Related articles.
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Operation Enhancements in Oracle Database 12c Release 1 (12.1)
Online MERGE PARTITION
Create a partitioned table with a three partitions, along with a global and partitioned index.
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, code VARCHAR2(10), description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_date) ( PARTITION t1_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017 00:00:00', 'DD-MON-YYYY HH24:MI:SS')), PARTITION t1_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018 00:00:00', 'DD-MON-YYYY HH24:MI:SS')), PARTITION t1_2018 VALUES LESS THAN (MAXVALUE) ); CREATE INDEX t1_created_date_idx ON t1 (created_date) LOCAL; CREATE INDEX t1_code_idx ON t1 (code); INSERT INTO t1 VALUES ( 1, 'ONE', 'Description for ONE', TO_DATE('01-FEB-2016','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 2, 'TWO', 'Description for ONE', TO_DATE('01-FEB-2016','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('01-FEB-2016','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 4, 'FOUR', 'Description for ONE', TO_DATE('01-FEB-2016','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 5, 'ONE', 'Description for ONE', TO_DATE('01-FEB-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 6, 'TWO', 'Description for ONE', TO_DATE('01-FEB-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 7, 'THREE', 'Description for ONE', TO_DATE('01-FEB-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 8, 'FOUR', 'Description for ONE', TO_DATE('01-FEB-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES ( 9, 'ONE', 'Description for ONE', TO_DATE('01-FEB-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (10, 'TWO', 'Description for ONE', TO_DATE('01-FEB-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (11, 'THREE', 'Description for ONE', TO_DATE('01-FEB-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (12, 'FOUR', 'Description for ONE', TO_DATE('01-FEB-2018','DD-MON-YYYY')); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current table partitions and index information.
-- Table partitions. COLUMN table_name FORMAT A30 COLUMN partition_name FORMAT A30 SELECT table_name, partition_name, num_rows FROM user_tab_partitions ORDER BY 1, 2; TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- T1 T1_2016 4 T1 T1_2017 4 T1 T1_2018 4 SQL> -- Indexes. COLUMN index_name FORMAT A30 COLUMN partitioned FORMAT A11 SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO VALID T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- 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_2016 USABLE T1_CREATED_DATE_IDX T1_2017 USABLE T1_CREATED_DATE_IDX T1_2018 USABLE SQL>
Perform an online operation to merge the T1_2016
partition into the T1_2017
partition.
ALTER TABLE t1 MERGE PARTITIONS t1_2016, t1_2017 INTO PARTITION t1_2017 ONLINE; EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current status of the table partitions and indexes. Notice both the global and local indexes are left in a usable state without having to specify the UPDATE INDEXES
clause.
-- Table partitions. COLUMN table_name FORMAT A30 COLUMN partition_name FORMAT A30 SELECT table_name, partition_name, num_rows FROM user_tab_partitions ORDER BY 1, 2; TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- T1 T1_2017 8 T1 T1_2018 4 SQL> -- Indexes. COLUMN index_name FORMAT A30 COLUMN partitioned FORMAT A11 SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO VALID T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- 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_2017 USABLE T1_CREATED_DATE_IDX T1_2018 USABLE SQL>
For a comparison, perform an offline operation to merge the T1_2017
partition into the T1_2018
partition.
ALTER TABLE t1 MERGE PARTITIONS t1_2017, t1_2018 INTO PARTITION t1_2018; EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current status of the table partitions and indexes. Notice both the global and local indexes on the affected partitions are left in a unusable state because we didn't specify the UPDATE INDEXES
clause or ONLINE
keyword.
-- Table partitions. COLUMN table_name FORMAT A30 COLUMN partition_name FORMAT A30 SELECT table_name, partition_name, num_rows FROM user_tab_partitions ORDER BY 1, 2; TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- T1 T1_2018 12 SQL> -- Indexes. COLUMN index_name FORMAT A30 COLUMN partitioned FORMAT A11 SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO UNUSABLE T1_CREATED_DATE_IDX YES N/A T1_PK NO UNUSABLE SQL> -- Index partitions. COLUMN partition_name FORMAT A30 SELECT index_name, partition_name, status FROM user_ind_partitions ORDER BY 1, 2; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- T1_CREATED_DATE_IDX T1_2018 UNUSABLE SQL>
Online MERGE SUBPARTITION
Create a subpartitioned table with two partitions, each with three subpartitions, along with a global and partitioned index.
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, code VARCHAR2(10), description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_date) SUBPARTITION BY LIST (code) SUBPARTITION TEMPLATE ( SUBPARTITION small VALUES ('ONE'), SUBPARTITION medium VALUES ('TWO', 'THREE'), SUBPARTITION others VALUES (DEFAULT) ) ( PARTITION year_2017 VALUES LESS THAN ( TO_DATE('01-JAN-2018','DD-MON-YYYY')), PARTITION year_2018 VALUES LESS THAN ( TO_DATE('01-JAN-2019','DD-MON-YYYY')) ); CREATE INDEX t1_created_date_idx ON t1 (created_date) LOCAL; CREATE INDEX t1_code_idx ON t1 (code); INSERT INTO t1 VALUES (1, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES (2, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES (3, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES (4, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY')); INSERT INTO t1 VALUES (5, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (6, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (7, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2018','DD-MON-YYYY')); INSERT INTO t1 VALUES (8, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2018','DD-MON-YYYY')); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current table partition and index information.
SET LINESIZE 120 COLUMN table_name FORMAT A30 COLUMN partition_name FORMAT A30 COLUMN subpartition_name FORMAT A30 -- Table subpartitions. SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions ORDER BY 1,2,3; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ------------------------------ ---------- T1 YEAR_2017 YEAR_2017_MEDIUM 2 T1 YEAR_2017 YEAR_2017_OTHERS 1 T1 YEAR_2017 YEAR_2017_SMALL 1 T1 YEAR_2018 YEAR_2017_MEDIUM 2 T1 YEAR_2018 YEAR_2017_OTHERS 1 T1 YEAR_2018 YEAR_2017_SMALL 1 SQL> -- Indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO VALID T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- Index subpartitions. SELECT index_name, partition_name, subpartition_name, status FROM user_ind_subpartitions ORDER BY 1,2,3; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_MEDIUM USABLE T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_SMALL USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_MEDIUM USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_OTHERS USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_SMALL USABLE SQL>
Perform an online operation to merge the YEAR_2017_SMALL
subpartition into the YEAR_2017_MEDIUM
subpartition.
ALTER TABLE t1 MERGE SUBPARTITIONS year_2017_small, year_2017_medium INTO SUBPARTITION year_2017_medium ONLINE; EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current status of the table subpartitions and indexes. Notice both the global and local indexes are left in a usable state without having to specify the UPDATE INDEXES
clause.
-- Table subpartitions. SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions ORDER BY 1,2,3; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ------------------------------ ---------- T1 YEAR_2017 YEAR_2017_MEDIUM 3 T1 YEAR_2017 YEAR_2017_OTHERS 1 T1 YEAR_2018 YEAR_2017_MEDIUM 2 T1 YEAR_2018 YEAR_2017_OTHERS 1 T1 YEAR_2018 YEAR_2017_SMALL 1 SQL> -- Indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO VALID T1_CREATED_DATE_IDX YES N/A T1_PK NO VALID SQL> -- Index subpartitions. SELECT index_name, partition_name, subpartition_name, status FROM user_ind_subpartitions ORDER BY 1,2,3; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_MEDIUM USABLE T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE T1_CREATED_DATE_IDX YEAR_2017 YEAR_2018_MEDIUM USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_OTHERS USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_SMALL USABLE SQL>
For a comparison, perform an offline operation to merge the YEAR_2018_SMALL
subpartition into the YEAR_2018_MEDIUM
subpartition.
ALTER TABLE t1 MERGE SUBPARTITIONS year_2018_small, year_2018_medium INTO SUBPARTITION year_2018_medium; EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current status of the table subpartitions and indexes. Notice both the global and local indexes on the affected subpartition are left in a unusable state because we didn't specify the UPDATE INDEXES
clause or ONLINE
keyword.
-- Table subpartitions. SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions ORDER BY 1,2,3; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ------------------------------ ---------- T1 YEAR_2017 YEAR_2017_MEDIUM 3 T1 YEAR_2017 YEAR_2017_OTHERS 1 T1 YEAR_2018 YEAR_2017_MEDIUM 3 T1 YEAR_2018 YEAR_2017_OTHERS 1 SQL> -- Indexes. SELECT index_name, partitioned, status FROM user_indexes ORDER BY 1; INDEX_NAME PARTITIONED STATUS ------------------------------ ----------- -------- T1_CODE_IDX NO UNUSABLE T1_CREATED_DATE_IDX YES N/A T1_PK NO UNUSABLE SQL> -- Index subpartitions. SELECT index_name, partition_name, subpartition_name, status FROM user_ind_subpartitions ORDER BY 1,2,3; INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_MEDIUM USABLE T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_MEDIUM UNUSABLE T1_CREATED_DATE_IDX YEAR_2018 YEAR_2018_OTHERS USABLE SQL>
For more information see:
- About Merging Partitions and Subpartitions
- ALTER TABLE
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Operation Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...