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

Art S. Kagel wrote: > > Leonid Vorontsov wrote: splited > > > Estimated Cost: 198169 (wow!) > > Estimated # of Rows Returned: 42562 (really only one) > > This is a problem I pointed out 7.3 beta testing. It was being worked > on and I had thought they found it. You could try UPDATE STATISTICS > HIGH ... RESOLUTION 0.5 or 0.05 to generate finer statistics. A LIKE > clause such as this may match the distribution ranges of several > buckets. With the higher resolution there will be more OVERFLOW values > and a better estimate. If this does not get you better cost and count > estimates then call tech support and report it so that the priority of > fixing the problem is raised. Alredy done. > > > 1) informix.table1: INDEX PATH (why not SEQUENTIAL SCAN with temporary > > table sorting?) > > > Filters: informix.table1.field1 LIKE '4210202116%' > > > (1) Index Keys: field2 > > The reason that the index is used instead of sorting is that you have > FIRST_ROWS optimization turned on! This new feature will STRONGLY > favor using an index that matches an ORDER BY clause so that the delay > caused by collecting N rows of data before sorting begins can be > eliminated. You are right in assuming that a table scan for a query > like this one will tend to be faster overall. If you want FIRST_ROWS > optimization on by default then set it off for this query using SET > OPTIMIZATION ALL_ROWS before the query and SET OPTIMIZATION FIRST_ROWS > after or use the optimizer directive {+ALL_ROWS} (or {-FIRST_ROWS}) in > the query itself. Do You see index3 on table1(field1, field2)? There isn't sorting needed. There isn't table reading needed too. index3 contains all information queried and in right order. > splited > > BTW the single column indexes (index1 and index2) are redundant as the > other two, multi-column, indexes can be used for any query that can use > these two. The additional I/O cost of the wider indexes is minimal and > actually these indexes may be MORE efficient than the single key > indexes if the key duplication in field1 and field2 is high. There is my error. I simply forgot drop it. > > Art S. Kagel