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 :

Step 2 : Create a Range-Hash partitioned table :

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

Step 4 : Let us now perform exchange operation :

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

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.