Disable indexes for data load

investigate index disable options in Oracle 11g.

Short answer – it works mostly nicely.

Example (a full test script is given bellow):

alter index test1_i1  unusable;

insert /*+append*/ into test1 select * from dba_objects where rownum<=1000;
commit;

alter index test1_i1  rebuild;

This method saves you programmming: indexes can be created in advance.
The only things you have to do are:
disable (unusable)  them before insert.
Enable (rebuild) them after insert.

Now an important part:
A unique index still has to be dropped. Oracle takes care to guard you against error in case your data load code relies on unique index to catch unique constraints violation errors.

However there is an exception: if unique index also has a unique constraint associated with it then a programmer can explicitely disable the constraint and take the responsibility:

alter table test1 add constraint test1_i2 primary key    (owner, object_name, object_type);

alter index test1_i2 unusable;
alter table test1  disable constraint test1_i2  ;

And then the insert works:
insert /*+append*/ into test1 select * from dba_objects where rownum<=1000;

But of course you will detect the “unique key violated” errors only after trying to enable the constraint:

alter table test1  enable constraint test1_i2  ;

Full test script:

create table test1 as select * from dba_objects where rownum<=1000;

create index test1_i1 on  s28543.test1 (owner);

truncate table test1;

drop index test1_i2 ;
create UNIQUE index test1_i2 on  test1 (owner, object_name, object_type);
alter table test1 add constraint test1_i2 primary key    (owner, object_name, object_type);

alter index test1_i1  unusable;
alter index test1_i2 unusable;
select * from dba_constraints where table_name=’TEST1′;
alter table test1  disable constraint test1_i2  ;

alter table test1  enable constraint test1_i2  ;

insert /*+append*/ into test1 select * from dba_objects where rownum<=1000;
commit;
select status from dba_indexes  where index_name=’TEST1_I1′;
rem UNUSABLE

insert into test1 select * from dba_objects where rownum<=1000;
commit;

select status from dba_indexes  where index_name=’TEST1_I1′;
alter index test1_i1  rebuild;
select status from dba_indexes  where index_name=’TEST1_I1′;

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s