Make indexes unusable before big insert
Make indexes unusable before doing a big insert, then rebuild them at the end, is faster than insert with indexes in place and no risk of forgetting to recreate a dropped index:
create table af ( mycol varchar2(100) ) ; create index af1 on af ( mycol ) ; alter index af1 unusable ; insert into af (mycol) select * from big_table ; select status from dba_indexes where index_name = 'AF1' ; alter index af1 rebuild ; select status from dba_indexes where index_name = 'AF1' ;
Code to make a lot of indexes unusable in one go is:
select 'alter index '||owner||'.'||index_name||' unusable ;' from dba_indexes where owner = 'MYSCHEMA' ;
Have you tried to see if making an index unusable, performing a table load, then rebuilding it is faster than dropping the index before a table load and recreating it afterward?