Re: Way to determine UPDATE STATISTICS

This is a multi-part message in MIME format. --------------551F14D74C30 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Eric_Melillo@agsea.com wrote: > > Get Art Kagels' utilities (from the iiug web site) -- dostats does a good > job of constructing all of the recommended update statistics for all > tables. I am aware of only one scenario that it doesn't handle well -- If > you have a column that heads and index and that same column heads another > index in descending order ( that's how they constructed the database ) it > does not place the column name in the parens. I fixed that problem and added several enhancements since you last downloaded dostats Eric. For example for a single column index, if the key has not already had HIGH run, because of the columns prsence in another index, dostats does the HIGH on that column without DISTRIBUTIONS ONLY so it does not have to perform the LOW at all. Also added support for Resolution for HIGH and Resolution and Confidence for Medium. Get the latest. Thanks for the plug. Let me also answer Jung-Hui's question, below. > "Jung-Hui Cheng" <cheng@dbtel.com.tw> on 12/15/98 09:18:23 PM > > Please respond to "Jung-Hui Cheng" <cheng@dbtel.com.tw> > > To: informix-list@iiug.org > cc: (bcc: Eric Melillo/AGInc) > Subject: Way to determine UPDATE STATISTICS > > Hi All; > > Is there a method to determine the rule about update statistics ? For > example, > what kind of tables should be run "UPDATE STATISTICS HIGH" and others > use "LOW". By the way, what is the "RESOLUTION" ? How it work ? See the release notes for 7.2x and later (though there are several versions of these). Ditmar reported MOST of the rules. The rules in the release notes describe the MINIMUM work needed to obtain resonably efficient statistics. The best stats are obtained by running HIGH on every column with a resolution of max(0.005, 1/nrows) the release notes rules just get us "good enough" stats in minimum time. Given that the best version of the rules is attached, as I extracted them from one set of 7.2x release notes. As to RESOLUTION - The resolution determines the portion of rows in each stats bucket, and therefore the number of buckets. In effect if resolution is the default of 0.5 then 0.5% of the rows are placed in each bucket resulting in 200 buckets the maximum resolution is 0.005% (limited by 1/nrows) or 20,000 buckets. Once the rows are assigned to buckets by key range any specific keys within the range which have more than a certain % of the rows in the bucket are broken out into an overflow bucket containing only that one key, to get better stats for outliers and reduce their effect on other calculations. Then the key ranges of the buckets are readjusted so the row count in each is again level. CONFIDENCE is used by MEDIUM to determine how close the results of using MEDIUM, with the sampling that MEDIUM does, will be to the exact results of using HIGH. A CONFIDENCE of 100 would be the same as HIGH though the maximum confidence is 90. Art S. Kagel --------------551F14D74C30 Content-Type: text/plain; charset=us-ascii; name="statistics.HOW-TO.7.2" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="statistics.HOW-TO.7.2" The following are guidelines that should be used in deciding which modes of UPDATE STATISTICS should be used for any given table. 1) Run update statistics MEDIUM for all columns in a table that DO NOT head an index. This will be a single update statistics command. The default parameters are sufficient unless the table is very large, in which case you should use a resolution of 1.0, 0.99. (Beginning with version 7.10.UD1, with the DISTRIBUTIONS ONLY option, this becomes simpler; you can execute an update statistics MEDIUM at the table level or for the entire system since the overhead of the extra columns isn't that expensive.) 2) Run update statistics HIGH for all columns which head an index. For the fastest execution time of the update statistics command in Informix-OnLine, you MUST execute one "update statistics HIGH ..." for EACH column. A single command suffices for Informix-SE. In addition when you have indices which begin with the same subset of columns, you should also run update statistics high for the first column in each which differs. For example if you had index1 -> (a,b,c,d) and index2 -> (a,b,e,f), then you would run update statistics high on "a" by itself and then on "c" and "e". In addition it would probably be a good idea to run it on "b" but it is usually not necessary. 3) For each multi-column index, execute update statistics LOW for ALL of its columns. (For the single column indices in 2) you've already executed LOW implicitly when you executed HIGH.) These steps will insure that UPDATE STATISTICS executes most rapidly because it only constructs the index information statistics once for each index. Several improvements have been made to the optimizer and the cost estimates have been adjusted to establish better query plans. These modifications, however, increased the optimizer's dependence on an accurate understanding of the underlying data distributions in certain cases. While executing complex queries involving equality predicates, if after following the above prescription, you feel that the query is not executing with sufficient rapidity, please do one of the following : - Run update statistics HIGH on columns which participate in equality join predicates but do not head indices. Having followed the prescription given earlier, columns which head indices will already have HIGH mode distributions. or - If you wish to determine if there is reason to believe that providing HIGH mode distribution information on columns which do not head indices could provide a better execution path, then 1) Turn on sqexplain output (set explain on) and re-run the query. 2) Note the estimated number of rows in the sqexplain output and the actual number of rows returned by the query. --------------551F14D74C30--