Oracle Database Discussions

Hi All,

Oracle 10.2 on Linux.
I have a process, where I populate a table in a for loop, with lot of business logic and this process runs every day. Generates lot of redo and archive log.
What I am trying is, put all my data in a global temporary table when the loop is running and when loop is complete, insert all the data in GTT to actual table with APPEND hint (and the target table is nologging). This will save a lot of redo and archive in turn.

If you see my observations below, while I am using GTT for inserts, still a lot of redo is generated.

Is there any way I can reduce redo further ??

--
-- Create GTT
--
SQL> create global temporary table gtt1
  2  (
  3   object_id number ,
  4   object_name varchar2(200)
  5  )
  6  on commit preserve rows ;

Table created.

--
-- Make note of redo generated in this session
--
SQL> select name , value from v$mystat a , v$statname b  where a.STATISTIC# = b.STATISTIC#  and name like 'redo size';

NAME                                  VALUE
------------------------------------- ----------
redo size                             5632

--
-- Insert data in GTT
--
SQL> begin
  2       for i in 1..1000
  3       loop
  4           insert into GTT1
  5           select object_id, object_name from user_objects where rownum < 11 ;
  6           -- commit ;
  7       end loop ;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL> select name , value from v$mystat a , v$statname b  where a.STATISTIC# = b.STATISTIC#  and name like 'redo size';

NAME                                  VALUE
------------------------------------- ----------
redo size                             170300

--
-- Amount of redo generated
--
SQL> select 170300 - 5632 from dual ;

170300-5632
-----------
     164668

--
-- Doing same thing with normal table
-- We all know, this will generate more redo than GTT
-- but doing this, just for comparing numbers
--
SQL> create table tt1
  2  (
  3   object_id number ,
  4   object_name varchar2(200)
  5  ) ;

Table created.

SQL> select name , value from v$mystat a , v$statname b  where a.STATISTIC# = b.STATISTIC#  and name like 'redo size';

NAME                              VALUE
--------------------------------- ----------
redo size                         177928

SQL> begin
  2       for i in 1..1000
  3       loop
  4           insert into TT1
  5           select object_id, object_name from user_objects where rownum < 11 ;
  6           -- commit ;
  7       end loop ;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select name , value from v$mystat a , v$statname b  where a.STATISTIC# = b.STATISTIC#  and name like 'redo size';

NAME                               VALUE
---------------------------------- ----------
redo size                          740336

SQL> select 740336 - 177928 from dual ;

740336-177928
-------------
       562408

SQL>

So, we can see that, GTT generates much less redo than normal table, but still generates some good amount of redo.
How can I reduce redo generation, when I am doing row-by-row inserts. I don't do many big bulk inserts.

Thanks in advance