Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!mailrus!ames!lll-lcc!unisoft!mtxinu!sybase!calvin!ben
From: ben@calvin.sybase.com (ben ullrich)
Newsgroups: comp.databases
Subject: Re: need HELP with index problem
Message-ID: <2484@sybase.sybase.com>
Date: 26 Dec 88 05:38:34 GMT
References: <8973@cit-vax.Caltech.Edu>
Sender: news@sybase.sybase.com
Reply-To: ben%calvin.sybase.com@sun.com (ben ullrich)
Organization: sybase, inc., emeryville, ca
Lines: 44

In article <8973@cit-vax.Caltech.Edu> citron@cit-vax.UUCP (Mark C. Citron) writes:
>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
>
>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).

yes. the length of time involves finding the row to update, not what it ends up
being.

>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!

your assumption about ``having trouble locating the record to update''
sounds correct to me. with all those rows having the same unit number, it
probably has to do something really time consuming like a table scan.

it also sounds to me like you're using only the unit number to qualify the row.
i'd suggest using other fields in the row (preferably ones contained in one
of the other indexes described above) to further qualify the row. hopefully
informix is smart enough to use one of the other indexes when you give it
fields within it instead of doing a table scan through all those 0 unit numbers.


...ben
---
ben ullrich					consider my words disclaimed
sybase, inc.		"everybody gets so much information all day long that
emeryville, ca			they lose their common sense." -- gertrude stein
(415) 596 - 3654
ben%sybase.com@sun.com		{pyramid,pacbell,sun,lll-tis,capmkt}!sybase!ben