About
You use the direct path insert and set the logging mode as NOLOGGING but you have still a lot of redo log generated.
Why ?
The answer is that you have an index on the table, and the index cannot just be appended to, they just be merged into. Since you need to merge data into them, you need redo in order to recover from instance failure. If your system crashed in the middle of the index operations, you would end up with a corrupt index structure. Hence, redo is generated for the index operations.
Articles Related
Indexes are expensive complex data structure
Oracle Database - Index (indices) are complex data structure, so maintaining them can be expensive.
gerardnico@orcl> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
The database is in Oracle Database - ARCHIVELOG mode.
Without Index
gerardnico@orcl>set autotrace on statistics;
gerardnico@orcl>insert into big_table select * from all_objects;
66651 rows created.
Statistics
----------------------------------------------------------
6062 recursive calls
9053 db block gets
189602 consistent gets
1 physical reads
7533724 redo size
901 bytes sent via SQL*Net to client
952 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1542 sorts (memory)
0 sorts (disk)
66651 rows processed
The data of the table generate 7,5MB of redo log.
With the index
gerardnico@orcl>set autotrace on statistics;
gerardnico@orcl>create index big_table_idx on big_table(owner,object_type,object_name);
Index created.
gerardnico@orcl>insert into big_table select * from all_objects;
66652 rows created.
Statistics
----------------------------------------------------------
6163 recursive calls
174495 db block gets
192120 consistent gets
462 physical reads
33037788 redo size
906 bytes sent via SQL*Net to client
952 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1542 sorts (memory)
0 sorts (disk)
66652 rows processed
With the index, 33MB of redo are generated. 23MB only for the index !
How to minimize the generation of redo log with an UNUSABLE index
Oracle Database - How can we load a large number of rows into an indexed existing table ?