Oracle Disable Indexes and Constraints During Insert

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

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

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

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

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;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s