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' ;
July 30, 2010

  • 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?

  • Hi Jon,

    No I haven’t tried those timings out myself but there *should* be no difference in timings either way – ‘index unusable+rebuild’ is meant to be the same as ‘index drop+recreate’, just a safer way of doing things (since the unusable index will still be visible in dba_indexes, while the dropped index could be forgotten about by mistake post table load).

    Tom Kyte: “Hey — in the end, its up to them. If they want to drop and recreate — go for it. Me, I’m a paranoid guy. I know what a missing index will do to my system. I’ve seen it. I don’t want it. I’d rather have apps break due to the unusable indexes (due to a failed reload) then to have then run without indexes for a while (bring everything else to a grinding halt)”
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2417875549841

    Andrew.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *