Business Intelligence

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 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....

February 19, 2012