8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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.

Merge Partition

Related articles.

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:

Hope this helps. Regards Tim...

Back to the Top.