Oracle sql developer : what is the fastest way to copy a partition of table to other partition with-CodePudding

I have two table t1 and t2. Both are identical and have same partitions.

We can say clone of each other. Now one of the job executed and fill one of the partition p in 4 hrs in t2 table.

In the end of the job i want to copy/cut/move all the data from that partition to t1 partition p.

Right now i am using the insert command but it is taking 30 mins time for 10 million records.

Insert into t1 select * from t2 partition p;

Can we do anything in little time.

CodePudding user response：

Create a temporary table to "exchange through". From 12.2 create table for exchange simplifies this.

For example:

create table src ( c1 ) 
partition by range ( c1 ) (
  partition p0 values less than ( 11 )
) as
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;
  
create table dst ( c1 ) 
partition by range ( c1 ) (
  partition p0 values less than ( 11 )
) as
  select * from src
  where  1 = 0;
  
create table tmp 
  for exchange with table src;
  
select count(*) from src;

  COUNT(*)
----------
        10

select count(*) from dst;

  COUNT(*)
----------
         0
  
alter table src
  exchange partition p0 
  with table tmp;
  
alter table dst
  exchange partition p0 
  with table tmp;
  
select count(*) from src;

  COUNT(*)
----------
         0

select count(*) from dst;

  COUNT(*)
----------
        10

For more details on this, see my recent Ask TOM Office Hours on archiving data

CodePudding user response：

consider Partition exchange functionality. Assuming you want to move:

ALTER TABLE t2 EXCHANGE PARTITION p WITH TABLE t1;

This requires for the table to have identical columns with the same data type, name, order, indexes, etc.