* Doing bulk data load with ORACLE

1. SQL*LOADER direct path

   Usage: 
   direct=y in the SQL*LOADER control file

   Restrictions:
   If the table is indexed, then no SELECT can be issued against the table during the load.
   Can not have active transactions on the table.
   DEFAULT column specifications defined in the database are not available when 
     loading on the direct path. Fields for which default values are desired must be 
     specified with the DEFAULTIF clause.
   <partitions>
   Can not have global indexes for a single partition or subpartition direct path loads.
   Enabled referential and check constraints on the table which the partition is a 
     member of are not allowed. 
 
   Note:
   Indexes, including function-based indexes are maintained during direct load.
   Triggers on the table are disabled during load and re-enabled afterward.
   Can work with partitioned or subpartitioned tables.
   Integrity constraints:
     NOT NULL: enforced during direct load, failed records rejected.
     UNIQUE: enforced during and after the direct load, failed reocrds NOT rejected.
             Any violations on a unique constraint will cause the index to be left 
             in "direct load state".
     FK: disabled during direct load, must be reenabed after, or use REENABLE clause.


2. Direct load insert
   Data is inserted above the high water mark.
   Data is written directly into datafiles, bypassing the buffer cache. 
   Free space in the existing data is not reused, and referential integrity 
   constraints are ignored.

   Usage:
   insert/*+ append */ into <table_name> select ... from <table_name>;

   Restrictions:

   Note:
   Can work with partitioned or subpartitioned tables.
   Oracle performs index maintenance at the end of direct-path INSERT.
   Referential integrity constraints are ignored.
   Triggers on the table fired during direct insert:


SQL> create or replace trigger t5_trg
  2    after insert or update or delete 
  3    on test5
  4    for each row
  5    begin
  6       if inserting then
  7          insert into t5_log values('insert');
  8       elsif deleting then
  9          insert into t5_log values('delete');
 10       else
 11          insert into t5_log values('update');
 12       end if;
 13     end;
 14  /

Trigger created.

SQL> select * from test5;

no rows selected

SQL> select * from t5_log;

no rows selected

SQL> desc test5
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(7)

SQL> insert/*+ append parallel */ into test5 select substr(table_name,1,7) from user_tables;

160 rows created.

SQL> select count(1) from t5_log;

  COUNT(1)
----------
       160

SQL> rollback;

Rollback complete.

SQL> select count(1) from t5_log;

  COUNT(1)
----------
         0

SQL>