Exchanging Partitions is something new. And it provides a huge benefits. By exchanging partition you can actually get data quickly in or out of partitioned table. The data that resides in the partition is exchanged with given non partitioned or partitioned table without deletion and insertion operation. 

Things to be taken care of while performing Exchange Partition Operation :

1.Two tables involved in this operation must have same column names and in same order and with same data type. Structure should be same.

However, triggers , grants , constraints ,owner ,indexes can be different and that will not affect EXCHANGE operation.

2.Local indexes’ partitions are exchanged with matching nonpartitioned indexes defined on the nonpartitioned table.

3.A range , list , hash partition or subpartition can be exchanged with nonpartitioned table.

4.Partition key should be same.

Let us see one example of Hash partitioned table with *-hash composite partition.

1.Exchange Hash Partitioned table with Range-hash composite partitioned table

Step 1 : Create a simple hash partitioned table :

SQL> CREATE TABLE TRANSACTIONS_HASH_1(
  2       TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRANSACTIONS_HASH_1_PK PRIMARY KEY(TRAN_ID)
)PARTITION BY HASH(ACCOUNT_NUMBER)
       (PARTITION H1, PARTITION H2);  3    4    5    6    7    8  

Table created.

Step 2 : Create a Range-Hash partitioned table :

SQL> CREATE TABLE TRANSACTIONS_MAIN_COMP_HASH_1(
  2       TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRAN_MAIN_COMP_HASH_1_PK PRIMARY KEY(TRAN_ID)
)
PARTITION BY RANGE (DATE_OF_TRANSACTION) SUBPARTITION BY HASH(ACCOUNT_NUMBER)
(PARTITION Y_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY'))
(SUBPARTITION P1_H1,
 SUBPARTITION P1_H2),
 PARTITION Y_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY'))
 (SUBPARTITION P2_H1,
 SUBPARTITION P2_H2),
 PARTITION Y_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY'))
 (SUBPARTITION P3_H1,
 SUBPARTITION P3_H2),
 PARTITION Y_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY'))
 (SUBPARTITION P4_H1,
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   SUBPARTITION P4_H2),
 PARTITION Y_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY'))
 (SUBPARTITION P5_H1,
 SUBPARTITION P5_H2),
 PARTITION Y_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY'))
 (SUBPARTITION P6_H1,
 SUBPARTITION P6_H2),
 PARTITION Y_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY'))
 (SUBPARTITION P7_H1,
 SUBPARTITION P7_H2),
 PARTITION Y_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY'))
 (SUBPARTITION P8_H1,
 SUBPARTITION P8_H2),
 PARTITION Y_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))
 (SUBPARTITION P9_H1,
 SUBPARTITION P9_H2)); 21   22   23   24   25   26   27   28   29   30   31   32   33   34   35  

Table created.

Step 3 : Lets insert some records in the TRANSACTIONS_MAIN_COMP_HASH_1 table . I have inserted records from one of  dummy table.

SQL> INSERT INTO TRANSACTIONS_MAIN_COMP_HASH_1 SELECT * FROM TRANSACTIONS_MAIN;

20001 rows created.

SQL> COMMIT;

Commit complete.

Step 4 : Let us now perform exchange operation :

SQL> ALTER TABLE TRANSACTIONS_MAIN_COMP_HASH_1 EXCHANGE PARTITION Y_2012 WITH TABLE TRANSACTIONS_HASH_1 WITH VALIDATION;

Table altered.

Step 5 : Let us check what happened with table data :

SQL> SELECT COUNT(*) FROM TRANSACTIONS_MAIN_COMP_HASH_1;

  COUNT(*)
----------
     17778

SQL> SELECT COUNT(*) FROM TRANSACTIONS_HASH_1;

  COUNT(*)
----------
      2223

We can see in above output , table TRANSACTIONS_MAIN_COMP_HASH_1 which previously had 20001 records now has 17778 records whereas TRANSACTIONS_HASH_1 table has 2223 records. We can here see exchange or data between two tables.

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

About The Author

Comments

  1. rohit gulati

    Thanks Jagruti, it is precisely a good article on exchange partitioning. Could you let us know how can we hash partition a huge table ( say 1 Billion rows). We intend to create 1024 partitions. We have a staging table with 1 B rows where we would add a virtual column based ORA_HASH key and create 1024 virtual buckets. Could you please guide us.

    Cheers,
    rohit

Leave a Reply

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