Re: Dynamic Optimizer problems??

Vic Goldberg <vic@tc.cornell.edu> wrote: :Ronald E. White wrote: :> :> ...seem to be having problems with the :> dynamic optimizer going to the BIG table to do its first search, then :> going to the little table last. :Have you tried doing an update statistics medium on both the big :and little tables? It sounds like the optimizer is relying on :out of date statistics..... If the original poster had said anything at all about the version he is using it would all have been easier. However we have found in ver 7.x that we sometimes need to run update statistics medium (or even high) on all columns involved in the join and sometimes even on columns that are not join columns, but part of the where clause. In some cases such an update statistics is even needed on non indexed columns to avoide the situation described. The solution to this kind of problems in ver 7.x is allways to play with update statistics, and in most cases you can solve the problem. I don't yet have enough experience in this playing, but it seems to me that the optimiser isn't as good as it should have been. The update statistics we have had to do are sometimes so extensive I start wondering what will happen down the line when the relative number of rows in tables change. May be a new set of update statistics become necessary. We have gone to the length of including a hidden menu choice in every program that turn on update statistics. In this way we can ask our users to turn it on when they run a program, and we can easily analyze what is happening, and remedy the situation without having to find the sql code that is executed and test it via dbaccess. 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