Oracle – optimizing insert performance

Dropping indexes before inserting and recreating after

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 Dropping indexes before and recreate them after way.

When inserting a row into a table , the relevant indexes also need to “know” about this.

Some of the indexes are unique indexes and they need to validate that there are no duplications , some of them are bitmap like indexes and some of them are normal indexes.

My solutions :

without optimizing example :

insert into some_table
select * from other_table;

After dropping unique indexes example;

begin
execute immediate 'alter table some_table drop constraint unique_constraint';

execute immediate 'drop index unique_constraint';

insert into some_table
select * from other_table;
commit;

execute immediate 'create unique index unique_constraint on some_table (unique_constraint_column)';
end;

After dropping and recreating indexes parallel example;

begin
drop_unique_constraint('some_table' , 'unique_constraint');

insert into some_table
select * from other_table;
commit;

create_uk_parallel('some_table' , 'unique_constraint' , 'unique_constraint_columns);
end;

drop_unique_constraint code :

CREATE OR REPLACE PROCEDURE drop_unique_constraint(p_table_name varchar2 , p_con varchar2) IS
BEGIN
execute immediate 'alter table ' || p_table_name || ' drop constraint ' || p_con;

execute immediate 'drop index' || p_con;

END drop_unique_constraint;

create_uk_parallel (parallel mode) code :

CREATE OR REPLACE PROCEDURE create_uk_parallel(p_table_name varchar2 , p_con varchar2 , p_cols varchar2) IS
BEGIN
-- create the uk without enabling and validating it

execute immediate 'create unique index ' || pcon || ' on ' || p_table || ' (' || p_cols || ') parrallel 8';

execute immediate 'alter table ' || p_table || ' add constraint ' || p_con || ' unique (' || p_cols || ') disable';

execute immediate 'alter table ' || p_table || ' enable novalidate constraint ' || p_con;

execute immediate 'alter session force parallel ddl parallel 8';

execute immediate 'alter table ' || p_table || ' parallel 8';

-- perform the validate part , which is the most difficult one in parallel mode

execute immediate 'alter table ' || p_table || ' enable validate constraint';

-- back to the original state

execute immediate 'alter session disable parallel ddl';

execute immediate 'alter table ' || p_table || ' noparallel';

execute immediate 'alter index ' || p_con || ' noparallel';

END create_uk_parallel;