Redo Reduction by Using Global Tempoary Table

There are about 1M rows in the BIG_TABLE. The goal is to load data (transformed in some way in real situation) from BIG_TABLE to MY_BIG_TABLE.The following two procedures were compared:

• Procedure test_pro

create or replace procedure test_pro IS
 CURSOR BIG_TABLE_CUR
  IS
    SELECT id ,owner ,object_name ,object_type ,created ,status from big_table;
 
  TYPE BIG_TABLE_TYPE
  IS TABLE OF BIG_TABLE_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  BIG_TABLE_REC BIG_TABLE_TYPE;
 
BEGIN
 
  OPEN BIG_TABLE_CUR;
  LOOP
    FETCH BIG_TABLE_CUR BULK COLLECT
    INTO BIG_TABLE_REC LIMIT 2000;
    EXIT WHEN BIG_TABLE_REC.COUNT = 0;
    FORALL i IN 1 .. BIG_TABLE_REC.count 
    INSERT INTO my_big_table VALUES BIG_TABLE_REC (i);
    COMMIT;
  END LOOP;
  CLOSE BIG_TABLE_CUR;
END;

• Procedure test_pro2, here my_big_table_gtt is a global tempoary table

create or replace procedure test_pro2 IS
 CURSOR BIG_TABLE_CUR
  IS
    SELECT 
        id ,owner ,object_name ,object_type ,created ,status from big_table;
 
  TYPE BIG_TABLE_TYPE
  IS TABLE OF BIG_TABLE_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  BIG_TABLE_REC BIG_TABLE_TYPE;
 
BEGIN
 
  OPEN BIG_TABLE_CUR;
  LOOP
    FETCH BIG_TABLE_CUR BULK COLLECT
    INTO BIG_TABLE_REC LIMIT 2000;
    EXIT WHEN BIG_TABLE_REC.COUNT = 0;
    FORALL i IN 1 .. BIG_TABLE_REC.count 
    INSERT  INTO my_big_table_gtt VALUES BIG_TABLE_REC (i);
    COMMIT;
  END LOOP;
  CLOSE BIG_TABLE_CUR;
  execute immediate 'insert /*+ append */ into my_big_table select * from my_big_table_gtt';
  commit;
END;

I measured the redo by v$mystat in the test, the result is showed as follows:

test_pro         redo  71M    run time:  4.57 s   
test_pro2        redo   6M     run time: 4.59 s

The test database  is  in noarchivelog mode.