Re: Update Statistics Low, Medium & High

This is a multi-part message in MIME format. --------------5C6612934DBB Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Clem Akins wrote: > > >In our system, we discover sometimes some tables in index searching is much > >faster while we update their statistics low or medium instead of high. > > I heard a discussion the other day which indicated that when you update > statistics "high" it causes the optimizer to evaluate every possible query > path. This evaluation might take longer than the actual query--it depends > on the indexes and the query. You are confusing the statistics level with the OPTIMIZATION level. Statistics would not really impact the number of paths explored, it would only change the costs associated with the various access methods used in those paths. Low stats maintains minimal info about the table & indexes, number of rows, number of unique keys in an index, etc. Thus costs are based on averages (e.g. the cost of an index scan for a particular value would be calculated as total # rows / unique keys, IOW the average number of rows for that key). Since data is often skewed in value (you may have a large number of nulls for a column, for example), this can cause a poor choice to be made. It may, however, be more than adequate for a large number of queries. Medium stats maintains a table of column values and occurances, based on statistical sampling (which you can tune via the RESOLUTION value). This would give a better indication of the data distribution for a row, so that if you had a value that was highly represented, it could be determined (an index scan becomes a poor choice, generally, when a high percentage of the rows in the table are returned by it - I've heard numbers as low as 7% for the optimal max) and the path chosen accordingly. For columns that contain an uneven distribution of values, having med stats can be an advantage. High stats have the same table as medium, but do not sample; all values are maintained. I've rarely seen an increase from medium to high be of much value in terms of performance; you'd have to have some pretty nasty data distributions for it to matter. Often, just increasing the sampling resolution on a medium stat can do the trick. The downside to higher stats is that the distribution table has to be read in and searched (it's read in once, then cached from around 7.12 on - prior to that, it was read in for every optimization - OUCH!). I've encountered quite a few cases where higher stats were done; dropping them increased performance because no change in the query plan resulted, but the dist tables no longer needed to be searched. The more dists you have (i.e. more columns), the worse it can be (if a column is in the where clause, it's dists will be checked). Probably the worst thing you can do is a higher stat on the entire table. You should always target indexed and filtered columns for the higher stats. Otherwise you are wasting space and time. The original poster was incorrect in surmising that the index search itself was faster with lower stats; once the plan is chosen, stats have no impact on performance. It is in the optimization phase that they make the difference. -- Dave Kosenko, Informix Professional Services *********************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney --------------5C6612934DBB Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="Disclaim" ************************************************************************* Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. --------------5C6612934DBB--