Merging Table Partitions in Oracle

In previous post I wrote about splitting single partition into two or more partitions. In this post we will see how we can merge two or more partitions into single partition and can reduce the overhead of managing multiple partitions.

To read split partitions

Split Partition in Oracle

Here we will see example of merging different types of partiitons.

1.Merging Range Partitions :

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TRANSACTIONS		       Y_2011			      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2012_H1		      TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2012_H2		      TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2013			      TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2014			      TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2015			      TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2016			      TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2017			      TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2018			      TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2019			      TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10 rows selected.

SQL>  ALTER TABLE TRANSACTIONS MERGE PARTITIONS Y_2011,Y_2012_H1,Y_2012_H2,Y_2013,Y_2014 INTO PARTITION Y_2015_BEFORE;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TRANSACTIONS		       Y_2015			      TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2015_BEFORE		      TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2016			      TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2017			      TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2018			      TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRANSACTIONS		       Y_2019			      TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

6 rows selected.

SQL>

2.Merging List Partitions

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_EX';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
DEPT_LIST_EX		       P1_ACC			      'ACC'
DEPT_LIST_EX		       P1_DBA			      'DBA'
DEPT_LIST_EX		       P1_SLS			      'SLS'
DEPT_LIST_EX		       P2			      'HRM', 'MAR', 'QA'

SQL> ALTER TABLE DEPT_LIST_EX MERGE PARTITIONS P1_ACC,P1_DBA,P1_SLS INTO PARTITION P1;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_EX';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
DEPT_LIST_EX		       P1			      'ACC', 'DBA', 'SLS'
DEPT_LIST_EX		       P2			      'HRM', 'MAR', 'QA'

SQL>

3.Merging Interval Partitions :

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_INTERVAL';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TRAN_INTERVAL		       P1			      TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P2_H1			      TO_DATE(' 2019-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P2_H2			      TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P3			      TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P4			      TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P5			      TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P6			      TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

SQL> ALTER TABLE TRAN_INTERVAL MERGE PARTITIONS P2_H1,P2_H2 INTO PARTITION P2;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_INTERVAL';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TRAN_INTERVAL		       P1			      TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P2			      TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P3			      TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P4			      TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P5			      TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TRAN_INTERVAL		       P6			      TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

6 rows selected.

SQL> :

4.Merging *-hash Partitions :

We can merge any range-hash , list-hash , interval-hash partitions. Here I took example of list-hash partitioned table.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_HASH';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_HASH		       P1			      'SLS', 'ACC'											3 YES
DEPT_LIST_HASH		       P2			      'HRM', 'MAR'											3 YES
DEPT_LIST_HASH		       P3			      'DBA', 'QA'											4 YES

SQL> ALTER TABLE DEPT_LIST_HASH MERGE PARTITIONS P1,P2 INTO PARTITION P1_P2 SUBPARTITIONS 5;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_HASH';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_HASH		       P1_P2			      'SLS', 'ACC', 'HRM', 'MAR'									5 YES
DEPT_LIST_HASH		       P3			      'DBA', 'QA'											4 YES

SQL>

5.Merging *-list Partitions :

We can merge any range-list , list-list ,interval-list partitions. Here I took example of list-list partitioned table.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_LIST';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_LIST		       P1_L1_1			      'GJ'												3 YES
DEPT_LIST_LIST		       P1_L1_2			      'RJ'												3 YES
DEPT_LIST_LIST		       P_L2			      'MH'												2 YES
DEPT_LIST_LIST		       P_L3			      'MP'												1 YES

SQL> ALTER TABLE DEPT_LIST_LIST MERGE PARTITIONS P1_L1_1,P1_L1_2 INTO PARTITION P1_L1;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_LIST';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_LIST		       P1_L1			      'GJ', 'RJ'											1 YES
DEPT_LIST_LIST		       P_L2			      'MH'												2 YES
DEPT_LIST_LIST		       P_L3			      'MP'												1 YES

SQL>

6.Merge *-range partitions :

We can merge any list-range , range-range , interval-range partition. Here I took example of list-range partitioned table.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_RANGE';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_RANGE 	       P1			      'SLS', 'ACC'											4 YES
DEPT_LIST_RANGE 	       P2			      'HRM', 'MAR'											4 YES
DEPT_LIST_RANGE 	       P3			      'DBA'												1 YES
DEPT_LIST_RANGE 	       P4			      'QA'												1 YES

SQL> ALTER TABLE DEPT_LIST_RANGE MERGE PARTITIONS P3,P4 INTO PARTITION P3;

Table altered.

SQL> SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE,SUBPARTITION_COUNT,COMPOSITE  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_RANGE';

TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE								       SUBPARTITION_COUNT COM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------ ---
DEPT_LIST_RANGE 	       P1			      'SLS', 'ACC'											4 YES
DEPT_LIST_RANGE 	       P2			      'HRM', 'MAR'											4 YES
DEPT_LIST_RANGE 	       P3			      'QA', 'DBA'											1 YES

SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.