Quickly Generate and Load data in Oracle

The fastest way I've found over the years to generate and load data into Oracle, is to use sqlldr in direct mode reading from a linux pipe with all constraints disabled. Since  everything on linux is a file, any program can generate data and write it to the pipe/file.

Disabling all constraints first dramatically increases the speed of loading. Indexes need to be rebuilt after direct loading since they are invalidated.

Create a loader.ctl file which directs sqlldr how and where to load the data. The control file below instructs sqlldr to read the comma separated column data (CSV) from the /tmp/loadData.pipe file and write it to the table in direct mode which bypasses logging for speed. It should be noted that the data in the table is not recoverable should the database need to be restored unless you take a backup after the  load. Only the initial load is not logged. All future transaction against the data are logged and recoverable.


 OPTIONS (DIRECT=TRUE)
           UNRECOVERABLE
  load data
    infile '/tmp/loadData.pipe'
    APPEND
    into table
    fields terminated by "," optionally enclosed by '"'
    ( ,  ,  )


Create the pipe on linux because pipes are only programmatic on windows:

mkfifo /tmp/loadData.pipe

Disable constraints so the data will load faster:


  BEGIN
    FOR i IN
      (SELECT constraint_name, table_name 
       FROM user_constraints 
       WHERE table_name='')
    LOOP
      EXECUTE immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name || '';
    END LOOP;
  END;
  /

Execute whatever program will generate the data and have it open and write the the file loadData.pipe file.

When the data generation program finishes:

Reenable the constraints:


  BEGIN
    FOR i IN
      (SELECT constraint_name, table_name 
       FROM user_constraints 
       WHERE table_name='')
    LOOP
      EXECUTE immediate 'alter table ' || i.table_name || ' enable constraint ' || i.constraint_name || '';
    END LOOP;
  END;
  /


Rebuild indexes they are invalidated by direct load:

  BEGIN

    FOR i IN

      (SELECT index_name 

       FROM dba_indexes 

       WHERE status != 'VALID' 

                       AND table_name='SCM_PACKAGE_ASSET_STATUS')

    LOOP

      EXECUTE immediate 'alter index ' || i.index_name || 'rebuild';

    END LOOP;

  END;

  /

Compute statistics:



EXEC DBMS_STATS.gather_table_stats('schemaName', 'tableName', cascade => TRUE, estimate_percent => dbms_stats.auto_sample_size);