Re: A never-ending query (almost)

In article <46jkpg$49l@xmission.xmission.com> dirk@xmission.xmission.com (Derrek Poulson) writes: >I've got a curious problem I'd be interested in hearing any thoughts on. >The machine is an HP9000/755 with HP-UX 9.01 and OnLine 5.01.UD1. The problem >is with a table that has several indexes, including 1 on field1 and 1 on >(field1, field2, field3, field4) (obviously I'm making up the column names). >The table has approx. 1.5 million rows and is 1 table out of 550 or so in its >particular database. A query of the form: >select * from table where field1="xxx"I'm sure field 1 is a unique index >comes back within a second or so. A query like: >select * from table where field1="xxx" and field2="yyy"this select uses 2nd index, non-unique, therefore must go thru entire index to find all matches ? have you updated statistics recently? >takes between <1 second to over an hour (!), depending on the value of field1. >I've eliminated the obvious (at least, what's obvious to me): >- tbcheck shows 4 extents >- SET EXPLAIN shows that the index on field1 is being used in the first case > and the composite index is used in the second (with an est. cost of 12 or > so; apparently the statistics are OK). >- npused is >0 (so it's not the 5.0 "negative npused" bug) >- The problem persists even after dropping & recreating the table (so if an > index is screwed up, Informix is managing to screw it up when it creates it). >I've been unsuccessful at duplicating the problem under OnLine 7.1, so it's >starting to look like an issue relating to 5.0. >If anyone's seen something similar, I'd be very interested in hearing what you >found. I'm willing to try almost anything short of recreating the database! >Thanks... >-- >--- >- Derrek (dirk@xmission.com)