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