Re: Dynamic Optimizer problems?

What version are you running? On what hardware/OS? How recently were statistics updated? At what level? Could you post the query and explain output? I haven't had the problem of using the large table first, but I did have one where the optimizer used an index to satisfy an "order by" when doing so forced it to scan the entire table (over 1 million pages). Since the "where" clause eliminated all but the default fragment (50 pages) of the table, it would have made more sense to scan just that fragment and sort the results. I forced that to happen by using an explicit temporary table. Maybe that's the best solution for you as well. Mark Collins mcollins@us.dhl.com ========================================================================== = FROM: informix-list-owner@rmy.emory.edu Has anyone had any problems with Informix's dynamic optimizer? We're running Informix with PeopleSoft and 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. When on those rare occassions it goes to the little table first and then to the BIG table the job completes in 5 to 10 minutes. Otherwise it takes several hours. Informix seems to think that their product doesn't have a problem, and that we can correct the situation by making the little table REALLY BIG by putting in a lot of dummy records. -- Ron White ronwhite@erols.com