Re: Cost Based Optimizer

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 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. 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. 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. 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