Re: Cost Based Optimizer

In <1993Oct1.183735.10667@informix.com> davek@informix.com (Dave Kosenko) writes: >Andrew Burt and Graeme Sargent have been going back and forth on optmizer >issues. To quell the raging debate ;-), let me offer some info on what >has changed with the optimizer in 6.0. >First of all, manual override of any kind is not provided. I can't say Shame! >for sure why the decision to not do that was made. but I'll speculate that >the amount of changes needed in the product to allow such functionality >(requires new syntax in the sql parser, changes to the sqli and sql levels >of the engine code) was not justified by the number of users who would >actually take advantage of such a feature. I can't help but wonder if this accurately allowed for the fact that it only needs one developer to use it for hundreds of users to benefit. >However, 6.0 does add some features to improve the job the optimizer >does. These features revolve around keeing more detailed statistics >on the data in the tables than ever before. In addition to those kept >previously, we will keep distributions on the actual data in the rows >for each column. There are different "levels" that you can set which >determine the amount of sampling used and the amount of distribution info >maintained for any given distribution. All this allows you to fine-tune >the info used by the optimizer in making its choices, which in turn will >result in better access strategies. Sounds great! But how long does it take to collect the stats? Does it lock the table while it does it??? >For example, take the situation where you have a query using a keyed field >for which duplicate values exist. In 5.0, the only thing we could find out >about that key is the average number of rows for that key (number of rows / >number of unique values). If the value you were searching for happened to >be unique, while some other value was very highly duplicate, the optimizer >might decide to do a sequential scan because it did not know that your value >would result in a single indexed access; it only knew the average return, >which was skewed by the highly duplicate value. >The new data distributions will help this situation by providing more >detailed info on how many rows in particular value ranges (and the number >of ranges is tuneable) exist, including special "overflow" categories >for values with many duplicates. >In the case of your matches "xyz*", the distributions could be used to >narrow down the estimate of how many values would be returned, since it >could use the known "xyz" portion to find the applicable distribution >info. But 5.0 ignores selectivity on a MATCHES comparison. It uses 0.2 regardless of whether the column is indexed or not (at least that is my information, and it is backed up by empirical tests). Are you saying categorically that this is no longer true in 6.0? >Time and tired fingers prevent me from going into more detail on this >feature. Tests run by the developers have shown improvements, many of them >substantial improvements, in access strategies with this new functionality. >The beauty of it is that the improvements are seen by "naive" users as well >as those who know the data and database intimately. >Dave >Disclaimer: These opinions are not those of Informix Software, Inc. >************************************************************************** >"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