Re: Distributions

Art S. Kagel wrote: > > Stefan Weideneder wrote: > > > > Jay Aymond wrote: > [SNIP other stuff] > > > Do you think it's a good idea to give the optimizer detailled > > informations for columns, if the information will not cause > > a better query plan ? Don't you believe that it will slow down > > those queries that do not benefit from the detailled distributions ? > > Be carefull if you start "update statistics medium/high for table xyz". > > Only make use of "update statistics high/medium ..." when you are > > sure that the optimizer will need this additional information. > > As the author of dostats.ec I have to disagree with this statement. It > cannot hurt to have more information in the distributions it can help > the optimizer to make better decisions. It would not hurt to have more information if you would not read this information. If the optimizer generates a correct query plan without the distribution, how can additional informations help ? > Yes, there are bugs in the > optimizer and situations where the optimizer does not make the very > best decisions and the level of stats for a particular table needs to > be reduced, but this is the exception and in general use the > recommendations in the release notes for 7.2x, or dostats.ec which > implements them, to get the best balance between the quality of the > stats and the time needed to produce them. For me it is only the time > needed that prevents me from using HIGH for all tables and columns. I know, a lot of customers want to hear general statements, i.e. when should I use UPDATE STATISTICS HIGH and when is it better to use UPDATE STATSITICS MEDIUM or LOW. If it is generally better to use an UPDATE STATISTICS MEDIUM, why isn't the server running an UPDATE STATISTICS MEDIUM generally on all indexed columns? During an ordinary UPDATE STATISTICS it's always scanning all indexes and it would not cause the system to perform additional sort operations. It would be easy to produce the distribution list. What are the costs for an UPDATE STATISTICS MEDIUM/HIGH ? We need at least 12 bytes for each bin ( for an integer field ), let's say we have an average of 20 bytes. If we would have about 100 bins per column ( resolution 1 ), this would cost 1,200 bytes per column. Let's assume a table is made up of appr. 10 columns, this would cost about 12,000 bytes per table. Now if we have 5,000 tables ( in a BaaN environment we have about 8,000 to 40,000 tables ) this would cost about 60MB of memory. Maybe your systems have enough memory and fast CPUs which can manage the evaluation of the distributions, but a lot of customers do not have such machines. Finally, I would use UPDATE STATISTICS HIGH, if the optimizer would generate a wrong query plan with UPDATE STATISTICS MEDIUM. And I would avoid an UPDATE STATISTICS MEDIUM as long as the optimizer generates the correct query plan with an ordinary UPDATE STATISTICS LOW. Bye Stefan Weideneder