Repolulating the tables with multi-million rows for reduced redo logs

[daljitsb]

Thanks vnktummala.

I was doing the table population earlier by dropping and recreating the table as 'create table myTable as select * from' - that was generating lot of redo.
Then I changed the table and index to nologging and populated it as
INSERT /*+ APPEND */ into myTable Select * from...

I was expecting reduced redo but it actually went up. See numbers below.
Why did redo went up??

------------------------

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

46772

create table myTable as Select * from myTable_v;
Create index myTable_IX1 on myTable (col1);
Create index myTable_IX2 on myTable (col2);


select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

460488


--------------


alter table myTable nologging;

drop index myTable_IX1;
drop index myTable_IX2;
TRUNCATE TABLE myTable REUSE STORAGE;

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

520446

INSERT /*+ APPEND */ into myTable Select * from myTable;
commit;
Create index myTable_IX1 on myTable (col1) NOLOGGING;
Create index myTable_IX2 on myTable (col2) NOLOGGING;

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

1002608