Re: Re[2]: Optimizer Flaws in 7.23 - Simple example

ALAN COWAN <alan.cowan@autodesk.com> wrote in article <5sfmnu$m2h@cssun.mathcs.emory.edu>... [ much clipped ] > select DISTINCT a.id > from dunsoft a, > OUTER > dunau b > where b.zip = a.zip > or b.tel = a.tel> > dunau 88,000 recs dunsoft 3,400 Might I suggest that this bit of SQL will force a sequential scan of the dunsoft table because you have asked for unique values of a.id and there is no index on this column. To get the distinct values the engine will need to build a temporary table. If you put an index on a.id it will go faster. The outer join combined with the 'or' means that the number of possible joined rows that must be looked at is very high, not least because if a dunau row matched both on zip and tel it would appear twice in the output. Outer joins, particularly when associated with and or or can seriously slow the engine. I would suggested that there is very little wrong with the Informix optimiser. It would be interesting to try the same query on the same data on some other products...... The problem with these sorts of things usually lies in the design of the database tables and the types of queries we try to force through them.