Path: utzoo!attcan!uunet!peregrine!elroy!cit-vax!citron
From: citron@cit-vax.Caltech.Edu (Mark C. Citron)
Newsgroups: comp.databases
Subject: need HELP with index problem
Keywords: index informix
Message-ID: <8973@cit-vax.Caltech.Edu>
Date: 23 Dec 88 18:15:11 GMT
Reply-To: citron@cit-vax.UUCP (Mark C. Citron)
Organization: California Institute of Technology
Lines: 28

Hope someone can understand what is going wrong here. I have a table in
a database that has three indices: 
a composite unique index made up of date, name1 and slotnumber
an index of name2
an index of unitnumber

there are about 50,000 rows in the table. In about 90% of the rows the
unitnumber is 0. In time these will be changed to other numbers (the
slots will be "filled"). 

Now here is the problem: When I update the unitnumber field
and that field already contains a number OTHER THAN 0 
the update goes very fast.  GREAT!
Updating the unitnumber field to 0 (independent of what it was) also
goes very fast (less than a sec).
HOWEVER, when I update the unitnumber field
and that field already contains a 0, the update takes forever (about 30
sec). There is alot of disk accessing.

This is an Informix database. I might guess that the problem is
that although unitnumber is indexed, there are so many rows with
the same unit number that Informix has trouble locating the
record to update. Is this correct? How do I get around this?
This performance is awful!

Thanks for any (ANY!) suggestions. Write or post.
citron@csvax.caltech.edu
citron@cit-vax.UUCP