Re: Outer join / index loss - strange behaviour

In article <76teasotdajssg2csvrr42lo7nh1qvkcj2@4ax.com>, steve_roach@NOSPAMibm.net wrote: > > Hi, > > Had a weird one today. I had a query with a lot of outer joins. > > It should (according to data) have returned one row but it didn't. I > returned 4 rows, the real one with most of the values not null, and > others with nulls in various fields. I couldn't find any explaination > and the usual method of dropping bits off the query until it started > to behave didn't find the smoking gun. > > Had a look at sqexplain.out and one of the indexes was not being used. > I checked the index and it wasn't there so I re-created it and ran > update statistics high.> > This solved the problem. Weird. I didn't think that the index effected > the results of the query, only its access path. Could a corrupted > index done this? An index CAN affect the results of a query. For instance, select field2 from table1 where field1 = something; If you have an index on table1(field1, field2) then informix will never look at the data, since all required info is in the index, saving a read. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: > An index CAN affect the results of a query. For instance, > select > field2 > from > table1 > where > field1 = something; > > If you have an index on table1(field1, field2) then informix will never > look at the data, since all required info is in the index, saving a > read. This "index-only read" feature is not supposed to affect the RESULTS of a query, only the method of executing it. However, Index corruption can cause erroneous results because it could have pointers to non-existent data. If the query is executed using only an index read, Informix could return different results that if Informix accesses the underlying data. Re-creation of your index will almost always rectify the problem. Rudy