disable indexes during an insert to table

[byrdfarmer]

What happens when I do the following:
1. disable index on table x
2. insert 10000 records to table x
3. enable index on table x

Does the database realize what I did and start some process to review the inserted data and incorporate it into the index? If not, is there a way to make it do so? Aside from dropping the entire index and recreating of course.

I am just trying to move a bottleneck around.

 

[vpshriyan]

Hi,

The following activities will take place:
1. Disbands active index.
2. Fills the datapages of extents allocated for the table.
3. Considers all the currently existing data in the table for index and creates & activates the index.

Not only does it reviews the inserted data, but it considers the earlier data also. You may check the following statements for clarifying the fundamentals:

create table tab1 ( f1 char(1), f2 smallint);
create unique index ix_tab1_u on tab1 (f1);
begin;
insert into tab1 values ('A',1);
insert into tab1 values ('B',1);
insert into tab1 values ('C',1);
commit;
set indexes for tab1 disabled;
begin;
insert into tab1 values ('A',1); -- successful
commit;
set indexes for tab1 enabled; -- failure

Regards,
Shriyan

 

[byrdfarmer]

Sweet - Are you a moderator here? I have noted the exceptional quality of your posts.

 

[vpshriyan]

Hi byrd,

You are welcome. No, I'm not a moderator to this forum, but an active participant & contributor to technical queries, plus an ardent informix product user and supporter as well.

Thanks once again for your compliments. Nice to note that you find my postings helpful and of requisite quality.

Regards,
Shriyan