Re: optimizing - sqexplain

Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> wrote: :Yes, I agree with all that. There is one rule that can be useful, :though. Many optimisers have difficulty with subqueries, especially :nested or complex ones. So, the one rule is: use joins instead of :subqueries where possible. Yes in general terms this is ok. However there is one type of subquery that works very well with all Informix engines and is very usefull: select ... from sometable where somefield in (select xx from anothertable where ...) This is fast (when done right), often makes for simple statements and can be used in updates and deletes as well. This is one of the reasons I say there is no cookbook solutions, only knowledge and experience. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org