Is update statistics really necessary ?

Hi, I would like to clarify some questions about rule versus cost query optmizer in Informix. I have Informix On Line Dynamic Server version 7.24. My database has about 300GB of size. I have to apply update statistics to get performance but it is consuming about 12 hours to finish. This time is becoming critical to my environment. So, I am thinking to use another approach. Is it possible to set the optmizer to work by rule (not use cost) ? I thing that if I use this approach the decision of the optmizer will be based in predefined rules, so will be not necessary to apply update statistics, won't it ? What I will have to set in my environment to work in this way (only set OPTCMPIND to 0) ? Thanks in advance, Fred Cox

Frederico Cox wrote: > > Hi, > > I would like to clarify some questions about rule versus cost query > optmizer in Informix. > > I have Informix On Line Dynamic Server version 7.24. My database has about > 300GB of size. I have to apply update statistics to get performance but it > is consuming about 12 hours to finish. This time is becoming critical to > my environment. How are you doing the UPDATE STATISTICS? With a single monolithic statement on the whole database? That WILL take forever. You need to do each table, indeed each index of each table, separately. That will take MUCH less time even doing each table sequentially but you can do many tables together in parallel and get that time down to an hour or three at most. Get my dostats.ec, have it output the commands for the entire database to a file, and edit the file to create a separate background dbaccess for each table's stat commands. Either that or just run one copy of dostats per table. Dostats will peform the minimal set of commands to get you a useful set of stats for each table in the least time possible. Options allow you to adjust the confidence and sampling for tables that you know need more or less detailed stats. Dostats.ec is part of the package utils2_ak in the IIUG Software Repository. Art S. Kagel [Unworkable approach SNIPPED!]