Move billion of rows between partitioned tables with exchange partition

Oracle Partitioning has been introduced in version 8.0 in 1997 which is a long while ago; I remember starting implementing it myself with version 8.1.6 which is also a while ago. I recently had the opportunity to revisit it to move billion of rows from one partitioned table to another partitioned table and the resources I found online seemed a bit confusing/unclear to me -- thus this blog to have a clear documentation the next time I'll have to do this.

The scenario is as below (I have done this in an Autonamous Database version 19c but it applies to any recent database, 12c included, whether it is autonomous or ... manual? :)):

• 2 range partitioned tables (same structure), partitioned by day on a date column
• The first table named FROM_TABLE has 3.2 billion rows and a lot of daily partitions:

SQL> select count(*) from FROM_TABLE;

  COUNT(*)
----------
3253093566

SQL>

• The second table named TO_TABLE has 2.8 billion rows and is also daily partitioned:

SQL> select count(*) from TO_TABLE;

  COUNT(*)
----------
2835819105

SQL>

• The goal is to transfer all the data from FROM_TABLE to TO_TABLE to then reach a 6 billion rows TO_TABLE table (6088912671 exactly).

SQL> select 2835819105+3253093566 from dual;

  COUNT(*)
----------
6088912671

SQL>

• The 2 tables are QUERY HIGH compressed.

The number of rows in these tables making an INSERT AS SELECT too slow, let's go with exchanging the partitions as both tables are partitioned the same way (both tables are basically the same with just different data and we want all the data into the same table: TO_TABLE).
A first thing to know about Oracle Partitioning is that you cannot directly exchange partitions between 2 partitioned tables. This could appear as a severe limitation for our scenario but there is a very simple way to work that around: using a temporary non partitioned table as shown on the below schema:

Here is how it works:

Create a for exchange TEMP table

SQL> create table TEMP for exchange with table FROM_TABLE;
Table created.  
SQL>




Exchange a partition from FROM_TABLE to TEMP

SQL> alter table FROM_TABLE exchange partition for (TO_DATE(' 2024-06-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) with table TEMP;
Table altered.
Elapsed: 00:00:00.14
SQL>

As this operation is a metadata only one, you see it is very fast, it took no time to move quite a bit of data:

SQL> select count(*) from TEMP;
  COUNT(*)
----------
  55157674
SQL>

Indeed, this partition has 55 million lines, this is a competitive timing.
You may wonder where/how do I get that TO_DATE(' 2024-06-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'); this is the HIGH_VALUE column from user_tab_partitions.

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name = 'FROM_TABLE';
TABLE_NAME           PARTITION_NAME       HIGH_VALUE
----------------- -------------------- ------------------------------------------------------------------------------------------------------------------------
FROM_TABLE         SYS_P1234            TO_DATE(' 2024-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
FROM_TABLE         SYS_P1567            TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
. . .
SQL>

Note that you can omit the gregorian calendar thing, we have been using this calendar for around 700 years, it is pretty safe to assume the date is in the gregorian calendar. This is also very useful to very quickly count the number of rows in a partition, I'll show an example below.



Exchange the partition from TEMP to TO_TABLE

We can now finalize the move by exchanging the partition to the target TO_TABLE partition:

SQL> alter table TO_TABLE exchange partition for (TO_DATE(' 2024-06-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) with table TEMP;
Table altered.
Elapsed: 00:00:00.45
SQL>

Same here, very fast operation. We can check that TEMP does not contain any row any more and the rows are now in the TO_TABLE table:

SQL> select count(*) from TEMP;
  COUNT(*)
----------
         0
SQL> select count(*) from TO_TABLE partition for (TO_DATE(' 2024-06-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
  COUNT(*)
----------
  55157674
SQL>

That was easy and fast, now the question is how to make that process more or less automatic to run that against a lot of partitions. What I did is I first queried user_tab_partitions to get the HIGH_VALUE of each partitions of the FROM_TABLE. As HIGH_VALUE is a LONG (deprecated since version 8.0 . . .), you need PL/SQL to manage it so I simply pasted the list of all the HIGH_VALUE into a file and generated all the commands:

$ cat all_high_values.txt | awk '{print "alter table FROM_TABLE exchange partition for ("$0") with table TEMP;"; print "alter table TO_TABLE exchange partition for ("$0") with table TEMP;";}' > move_part.sql
$

And I executed the generated SQL file:

SQL> @move_part
Table altered.
Elapsed: 00:00:00.10
Table altered.
Elapsed: 00:00:04.43
etc . . .
SQL>

After a few minutes moving all the partitions, I could check and confirm the expected number of rows in each of the table involved in this process:

SQL> select count(*) from TO_TABLE;
  COUNT(*)
----------
6088912671
Elapsed: 00:00:37.06
SQL> select count(*) from FROM_TABLE;
  COUNT(*)
----------
         0
Elapsed: 00:00:00.01
SQL> select count(*) from TEMP;
  COUNT(*)
----------
         0
Elapsed: 00:00:00.01
SQL> drop table TEMP;
Table dropped.
Elapsed: 00:00:00.13
SQL>

A last note is that my FROM_TABLE and TO_TABLE are QUERY HIGH compressed but the exchange table is not compressed; the partition will nonetheless stayed compressed during their journey from FROM_TABLE and TO_TABLE.

And this is how to move billion of rows between partitioned tables with exchange partition!