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