Re: I can't think of the subject name (have not words).

David Kosenko wrote: > "Art S. Kagel" <kagel@bloomberg.net> offerred: > +> > An annoyed Informix user wrote: > +> Do You see index3 on table1(field1, field2)? There isn't sorting > needed. > +WRONG WRONG WRONG! > +> There isn't table reading needed too. index3 contains all > information > +> queried and in right order. > +ALSO WRONG WRONG WRONG! > [ specifics on query and optimization plan deleted ] > This is kind of funny, because it illustrates the very reason Informix > held off so long in offering "optimizer hints" (what they have > implemented as optimizer directives). I worked for Informix for many > years in the field, and one of the most common requests from customers > was the ability to tell the optimizer how to execute the query, rather > than rely on it's cost-based decision making process. The consistent > response we used to hear from R&D was "customers always think they > know the 'faster way' to execute a query, but often times it is not > the faster way; we need to make the optimizer smarter where it is > dumb, not give users the ability to override it." > > Key players in Informix development have departed since that time, and > obviously those who were so strongly against "optimizer hints" were no > longer around to say no to the addition of this feature. So 7.30 > includes the feature, and within a few weeks we see an example of > someone complaining about Informix's poor query access plan when the > reason it is so poor is that they used optimizer directives > incorrectly, i.e. they "told" the optimizer to use a strategy that > turns out to be poor. Then the Informix product is blamed for it. > Makes me wonder if the previous philosophy was not the wiser one in > the long run... Ouch. Being that I was one the loudest voices calling fot this particular feature (FIRST_ROWS) that hurt! However, let me explain that this is not a case of the baby getting hold of the matches, but rather, of using a hammer to drive a nail. The purpose of the FIRST_ROWS optimization option is for those situations where getting back the first few rows from a query is more important than the time taken to complete the query. If that was the original poster's goal then he cannot turn around and complain when the optimizer does what he asked for it to do because the total time is too long. From the posting I had to assume, in my reply, that the total cost and time of the entire query were the main concern, in which case the FIRST_ROWS is not the desired optimizer goal. If, rather, the first rows response time was the most important criterion, then the problem is that the poster is using the wrong performance measurement metric. Of course, if memory serves, there is only one row which matches the query so the FIRST_ROWS optimization goal is irrelevant since the time to completion is exactly equal to the total cost so in this case FIRST_ROWS will NEVER be "better". Art S. Kagel