Oracle Disable Constraints and Make Indexes Unusable

http://johnlevandowski.com/oracle-disable-constraints-and-make-indexes-unusable/

DECLARE 

    V_TABLENAME VARCHAR2(100) := 'ZDW_OR_ORDER_DTL';

    V_OWNER     VARCHAR2(50)  := 'BAQDW'           ;

BEGIN

  FOR CUR IN

    (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME

       FROM ALL_CONSTRAINTS

      WHERE OWNER = V_OWNER

        AND TABLE_NAME = V_TABLENAME

     )

          LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||CUR.OWNER||'.'||CUR.TABLE_NAME||' MODIFY CONSTRAINT "'||CUR.CONSTRAINT_NAME||'" DISABLE KEEP INDEX';

      END LOOP;

--==============================================================================================

  FOR cur2 IN

    (SELECT  OWNER,INDEX_NAME

       FROM  ALL_INDEXES

      WHERE  TABLE_OWNER = V_OWNER

        AND  TABLE_NAME = V_TABLENAME

     ) 

        LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur2.OWNER||'.'||cur2.INDEX_NAME||' UNUSABLE';

    END LOOP;

--==============================================================================================

  FOR CUR3 IN

    (SELECT OWNER,INDEX_NAME

       FROM all_indexes

      WHERE TABLE_OWNER = V_OWNER

        AND TABLE_NAME = V_TABLENAME

     ) 

       

  LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur3.OWNER||'.'||cur3.INDEX_NAME||' REBUILD ONLINE';

  END LOOP;

  --==============================================================================================   

       

END;

  /

  

http://johnlevandowski.com/oracle-disable-constraints-and-make-indexes-unusable/

Oracle Disable Constraints and Make Indexes Unusable

Posted on February 19, 2012 Leave a Comment

If you are attempting to insert millions of rows into an oracle database, you may want to temporarily disable constraints and make indexes unusable 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

01 02 03 04 05 06 07 08 09 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. Make Indexes Unusable

01 02 03 04 05 06 07 08 09 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

01 02 03 04 05 06 07 08 09 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

01 02 03 04 05 06 07 08 09 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; /

Share this: