FORALL insert destination table then a FORALL delete from source table

Question

Cam the below be done without issues? Where as soon as you insert a record into the destination table you can then delete that record from the source table. Obviously this record is in memory within the loop, can you see any problems with this or can it be done in a different way.

I know all of you will say just do a direct SQL insert with APPEND and then truncate the source table.

I'm just throwing the question out there as I'm curious.

PROCEDURE copy_records_back IS

  TYPE t_act_plus_triggers_copy1 IS TABLE OF act_plus_triggers_copy1%ROWTYPE;   v_act_plus_triggers_copy1 t_act_plus_triggers_copy1;

  CURSOR c_act_plus_triggers_copy1 IS   SELECT * FROM act_plus_triggers_copy;


BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE act_plus_triggers1';

  OPEN c_act_plus_triggers_copy1;   LOOP
    FETCH c_act_plus_triggers_copy1 BULK COLLECT INTO v_act_plus_triggers_copy LIMIT 10000;  

    FORALL i IN 1..v_act_plus_triggers_copy.COUNT  
      INSERT /*+ APPEND_VALUES */ INTO act_plus_triggers1  values v_act_plus_triggers_copy(i);       


    FORALL i IN 1..v_act_plus_triggers_copy.COUNT  
    DELETE FROM act_plus_triggers_copy
    where surr_id = v_act_plus_triggers_copy(i).surr_id

    COMMIT;     
    EXIT WHEN c_act_plus_triggers_copy1%NOTFOUND;

    END LOOP;   
CLOSE c_act_plus_triggers_copy1;

END copy_records_back;

Answer 1

If the two tables have the same columns, there is a simpler method to do this without copying the data at all. You can create a partitioned table with the same columns as both tables and with only one partition. Then you can use ALTER TABLE EXCHANGE PARTITION to swap the table with the partition, then you can swap in the same way the partition with the target table (if one of the tables is partitioned, you only need to do one swap). If the data need to change tablespace (or some other physical properties), you can issue ALTER TABLE MOVE at the end to accomplish this task.

In your situation the statements for swapping two tables will look like:

-- initialization (do only once)
CREATE TABLE temp_part_table PARTITION BY HASH (surr_id) (PARTITION single) AS
SELECT * FROM act_plus_triggers_copy WHERE 1=0
/
-- swapping act_plus_triggers_copy with act_plus_triggers1
ALTER TABLE temp_part_table EXCHANGE PARTITION single WITH TABLE act_plus_triggers1
WITHOUT VALIDATION
/
ALTER TABLE temp_part_table EXCHANGE PARTITION single WITH TABLE act_plus_triggers_copy
WITHOUT VALIDATION
/
ALTER TABLE temp_part_table EXCHANGE PARTITION single WITH TABLE act_plus_triggers1
WITHOUT VALIDATION
/

There are three exchanges if you want to swap both tables. If you know that both temp_part_table (temporary partitioned table) and act_plus_triggers1 (target table) are initially empty, you can skip the first ALTER.