Oracle – optimizing insert performance

Enable/Disable foreign keys before inserting

Description :

There are many ways to optimize mass insert operation performance , I will summerize some of them shortly :

• Using Bulk collect statement inside a loop instead of a single insert statement in a loop.
• Dropping indexes before the insert and recreating them after using parallel.
• Disable foreign keys before the insert and enable them after using parralel.

In this article I’m going to discuss the Disable/Enable foreign keys way.

When inserting a row into a table , the row has to be checked and that is done by the table’s constraints.

some of the constraints are Foreign Keys to other table’s Primary Keys and that means that you have to scan the other table’s Primary Key’s index and to validate the value that is going to be inserted.

When you want to insert many rows at once or in a loop or in another way .. those Indexes scans are Significant.

If you don’t want to disable all of the table’s Foreign Keys and still you want to optimize the insert statement using that method , you can disable only the heaviest Foreign Key (the heaviest Foreign Key is the one pointing to the table that contains the highest number of rows) , by doing that you saved n times index scans.

My solutions :

without optimizing example :

<pre class=”brush:sql”>

insert into some_table
select * from other_table;

</pre>

After disabling the largest Foreign key example;

begin
execute immediate 'alter table some_table disable constraint large_table_fk';

insert into some_table
select * from other_table;
commit;

execute immediate 'alter table some_table enableconstraint large_table_fk';
end;

After disabling all Foreign keys example;

begin
disable_table_fks('some_table');

insert into some_table
select * from other_table;
commit;

enable_table_fks('some_table');
end;

disable_table_fks code :

</pre>
CREATE OR REPLACE PROCEDURE disable_table_fks(p_table_name varchar2) IS
BEGIN
for a in (select t.CONSTRAINT_NAME
from user_constraints t
where lower(t.TABLE_NAME) = lower(p_table_name)
and t.CONSTRAINT_TYPE = 'R'
and t.status = 'ENABLED') loop

execute immediate 'alter table ' || p_table_name || ' disable constraint ' || a.constraint_name;
end loop;

END disable_table_fks;
<pre>

enable_table_fks (parallel mode) code :

</pre>
CREATE OR REPLACE PROCEDURE enable_table_fks(p_table_name varchar2) IS
BEGIN
-- alter the session and the table's mode to parallel
execute immediate 'alter session force parallel ddl parallel 8';
execute immediate 'alter table ' || p_table_name || ' parallel 8';

for a in (select t.CONSTRAINT_NAME
from user_constraints t
where lower(t.TABLE_NAME) = lower(p_table_name)
and t.CONSTRAINT_TYPE = 'R'
and t.status = 'DISABLED') loop

-- first enable without validating and then validate
execute immediate 'alter table ' || p_table_name ||' enable novalidate constraint ' || a.constraint_name;
execute immediate 'alter table ' || p_table_name ||' enable validate constraint ' || a.constraint_name;
end loop;

-- alter the session and the table's mode to no parallel
execute immediate 'alter session disable parallel ddl';
execute immediate 'alter table ' || p_table_name || ' noparallel';

END enable_table_fks;
<pre>