Re: Update statistics question

Mark D. Stock wrote: > > Art S. Kagel wrote: > > > > david.ashby@workcover.nsw.gov.au wrote: > > [SNIP] > > > From these results I would contend that the update statistics > > > recomendations in the release notes pertaining to the final step > > > "update statistics low" are unnecessary. It would be better to run > > > update statistics low for the whole table not for specific columns.> > > > Actually, the recommendation is to do an UPDATE STATISTICS LOW on the > > entire index key to calculate the key counts and 2nd high and 2nd low > > key values for each index. Apparently, while faster, an UPDATE > > STATISTICS LOW on the entire table is not sufficient. (The exception > > to this rule is for single key indexes, the LOW stats will have been > > calculated when the HIGH on that column was run.) > > This isn't right. An UPDATE STATISTICS LOW at the column level does > everything on the given column that an UPDATE STATISTICS LOW on the > whole table does for every column. Or so it would seem. > > I've yet to be given a valid reason to run UPDATE STATISTICS LOW per > column. The only time I run UPDATE STATISTICS LOW per table is when > running several in parallel, or after a load. > > > 1) Update statistics MEDIUM on all columns in a table that do not head > > any index with the DISTRIBUTIONS ONLY clause. (You can do the entire > > table since the extra time is minimal.) > ^^^^^^^^^^^^^^^^^^^^^^^^^ > That's rather a sweeping statement. IMO, any time saved is a saving in > time. ;-) No point in doing a MEDIUM if you're going to replace it with > a HIGH, especially on a large table. > > > Remember the goal of the recommendations was not to save time when > > updating statistics, but to provide the best information to the > > optimizer without spending more time than neccessary. > > This is very true, in fact there are two art (excuse pun :) forms; > getting your queries to run quickly, and getting your UPDATE STATISTICS > commands to run quickly. The two are often mutually exclusive. ;-) > > The recommendations are simply that. You have to work on getting optimal > performance for your system. If a MEDIUM gives your query the same > performance as a HIGH, then you just saved time at UPDATE STATISTICS > time. > > Cheers, > -- > Mark. > > +----------------------------------------------------------+-----------+ > |Mark D. Stock - Informix SA http://www.informix.com |//////// /| > |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| > | +-----------------------------------+//// / ///| > | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| > | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| > |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| > +----------------------+-----------------------------------+-----------+ Hi there all. I,ve found the next to be very efficient in practice with respect to: 1. time needed to run 2. performance gained. First do UPDATE STATISTICS LOW DROP DISTRIBUTION for every table NEXT do UPDATE STATISTICS HIGH ON .. (field1, field2, .. fieldn) for every table and mentioning only those fields which are used in indexex. If you try it please tell me your experience. Henk Sanders hjs@worldonline.nl