If you are attempting to insert millions of rows into an oracle database, you may want to temporarily disable indexes and constraints to improve the speed of the load. Here are 5 steps to follow that will disable constraints and make indexes unusable. You need to substitute the OWNER and TABLE on line 5 and 6 respectively in each statement.

1. Disable Constraints

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN
FOR cur IN
  (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME
   FROM all_constraints
   WHERE OWNER = 'OWNER'
     AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||cur.OWNER||'.'||cur.TABLE_NAME||' MODIFY CONSTRAINT "'||cur.CONSTRAINT_NAME||'" DISABLE ';

END LOOP;

END;
/

2. Disable Indexes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN
FOR cur IN
  (SELECT OWNER,INDEX_NAME
   FROM all_indexes
   WHERE TABLE_OWNER = 'OWNER'
     AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur.OWNER||'.'||cur.INDEX_NAME||' UNUSABLE';

END LOOP;
 
END;
/

3. Insert the process to extract, transform, load your data here

4. Rebuild Indexes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN
FOR cur IN
  (SELECT OWNER,INDEX_NAME
   FROM all_indexes
   WHERE TABLE_OWNER = 'OWNER'
     AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur.OWNER||'.'||cur.INDEX_NAME||' REBUILD ONLINE';
 
END LOOP;
 
END;
/

5. Enable Constraints

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN
FOR cur IN
  (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME
   FROM all_constraints
   WHERE OWNER = 'OWNER'
     AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||cur.OWNER||'.'||cur.TABLE_NAME||' MODIFY CONSTRAINT "'||cur.CONSTRAINT_NAME||'" ENABLE ';
 
END LOOP;
 
END;
/