Coalesce Partition and Subpartition

As we saw in previous posts by just defining number of partitions we can create partitions in HASH partition or *-hash subpartitions paritioning type.
Now to remove some partition we can use coalesce partition feature of oracle.

After we apply coalescing function one hash partition is removed and data are redistributed among the partitions which are suitable with hashing function of oracle.
In this post we will see coalescing partition and coalescing sub partition from one of *-hash composite partition.

1.Coalescing Partition :
Step 1 : Create table with 7 partitions.

SQL> CREATE TABLE TRAN_HASH1
(TRAN_ID NUMBER,
TRAN_DATE DATE,
AMOUNT NUMBER(10,2),
ACCOUNT_NUMBER NUMBER)
PARTITION BY HASH(TRAN_ID)
PARTITIONS 7
STORE IN (PART1,PART2);  2    3    4    5    6    7    8

Table created.

Check number of partitions in USER_PART_TABLES table.

SQL> SELECT TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT ,STATUS FROM USER_PART_TABLES WHERE TABLE_NAME='TRAN_HASH1';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
TRAN_HASH1                     HASH                    7 VALID

We can check here there are 7 partitions available. Lets coalesce partition with COALESCE PARTITION command.

SQL> ALTER TABLE TRAN_HASH1 COALESCE PARTITION;

Table altered.

Now let us check number of partitions available in USER_PART_TABLES table.

SQL> SELECT TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT ,STATUS FROM USER_PART_TABLES WHERE TABLE_NAME='TRAN_HASH1';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
TRAN_HASH1                     HASH                    6 VALID

Let us again repeat the same practice.

SQL> ALTER TABLE TRAN_HASH1 COALESCE PARTITION;

Table altered.

SQL>  SELECT TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT ,STATUS FROM USER_PART_TABLES WHERE TABLE_NAME='TRAN_HASH1';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
TRAN_HASH1                     HASH                    5 VALID

SQL>

We can see in above resultset after we apply coalesce partition command each one hash partition is removed from the list.

Coalesce subpartition :

Same like coalescing Partitions , when we perform coalesce subpartition operation , one subpartition reduces and the rows of that subpartition is distributed among remaining subpartitions as per hashing function.

Step 1 : Here I checked with group by query in user_tab_subpartitions table.

SQL> SELECT TABLE_NAME,PARTITION_NAME,COUNT(*) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='TRANSACTIONS_MAIN_COMP_HASH' GROUP BY TABLE_NAME,PARTITION_NAME;

TABLE_NAME			    PARTITION_NAME			  COUNT(*)
----------------------------------- ----------------------------------- ----------
TRANSACTIONS_MAIN_COMP_HASH	    Y_2012					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2015					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2020					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2014					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2011					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2013					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2019					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2017					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2016					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2018					 4

10 rows selected.

We can see in above resultset every partition consists of 4 subpartitions each.

Now let’s perform coalescing subpartition operation .

Step 2 : Coalesce Subpartition :

SQL> ALTER TABLE TRANSACTIONS_MAIN_COMP_HASH MODIFY PARTITION Y_2018 COALESCE SUBPARTITION;

Table altered.

Step 3 : Let’s check count of subpartitions now.

SQL> SELECT TABLE_NAME,PARTITION_NAME,COUNT(*) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='TRANSACTIONS_MAIN_COMP_HASH' GROUP BY TABLE_NAME,PARTITION_NAME;

TABLE_NAME			    PARTITION_NAME			  COUNT(*)
----------------------------------- ----------------------------------- ----------
TRANSACTIONS_MAIN_COMP_HASH	    Y_2012					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2015					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2020					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2014					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2011					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2013					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2019					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2017					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2016					 4
TRANSACTIONS_MAIN_COMP_HASH	    Y_2018					 3

10 rows selected.

SQL> 

We can see in above resultset Y_2018 partition has 3 subpartitions now which was 4 before.

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