Oracle Database - Why you have still a lot of redo and archive ? The Index side effect.

Card Puncher Data Processing

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.

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 ?





Discover More
Card Puncher Data Processing
Oracle Database - How can we load a large number of rows into an indexed existing table ?

If you have to perform data load into a or data mart, you must skip the index to minimize the generation of redo log, such as : set the indexes to the state. They are not dropped but just setting...
Card Puncher Data Processing
Oracle Database - Redo Log (Transaction Log Files)

transaction log in Oracle are knwon as the redo log. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure. To protect against a failure...
Card Puncher Data Processing
Oracle Database - Redo Size statistics

The redo size statistics shows how much redo log your statement generated when executed. The redo size statistics is most useful when judging the efficiency of large bulk operations with statements...



Share this page:
Follow us:
Task Runner