Oracle Database - How can we load a large number of rows into an indexed existing table ?

Card Puncher Data Processing

About

If you have to perform Data Processing - (Batch|Bulk) Processing data load into a Data Warehouse or data mart, you must skip the index to minimize the generation of redo log, such as :

  1. set the indexes to the Oracle Database - Index (indices) state. They are not dropped but just setting as unusable
  2. re-enable the indexes

Why not just drop the index ?
Because the command CREATE INDEX can failed and if no one notice it, the performance goes down.
If the command to re-enable the index fails, when the users run queries that need that index, they will get an error message.

Example

This example is the end of this article : Oracle Database - Why you have still a lot of redo and archive ? The Index side effect.

gerardnico@orcl>alter index big_table_idx unusable;

Index altered.

gerardnico@orcl>alter session set skip_unusable_indexes=true;

Session altered.

gerardnico@orcl>insert /*+ APPEND */ into big_table select * from all_objects;

66652 rows created.


Statistics
----------------------------------------------------------
       6308  recursive calls
       1545  db block gets
     187973  consistent gets
          0  physical reads
    7606756  redo size
        893  bytes sent via SQL*Net to client
        966  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1550  sorts (memory)
          0  sorts (disk)
      66652  rows processed

gerardnico@orcl>alter index big_table_idx rebuild nologging;

Index altered.





Discover More
Card Puncher Data Processing
Oracle Database - Data Load

Data loading in a Oracle Database environment. In the Oracle Database, you can load data in the following elements: Table, View , Materialized View, External Table Oracle Version Utility...
Card Puncher Data Processing
Oracle Database - Why you have still a lot of redo and archive ? The Index side effect.

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...



Share this page:
Follow us:
Task Runner