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

Leonid Vorontsov wrote: > Hi, All! Next problem with IDS 7.30.TC2 for NT (ooh...). > I have table with 200000 rows (approximately): > CREATE TABLE table1 ( > ... > field1 CHAR(11), > field2 CHAR(120), > ... > ); > I have indexes: > CREATE INDEX index1 ON table1 (field1); > CREATE INDEX index2 ON table1 (field2); > CREATE INDEX index3 ON table1 (field1, field2); > CREATE INDEX index4 ON table1 (field2, field1); > Some parameters from 'onconfig': > OPTCOMPIND 0 > OPT_GOAL 0 > 1st statement: > SELECT field1, field2 > FROM table1 > WHERE field1 LIKE '4210202116%' > ORDER BY field2; > This statement executes 3 minutes and gives 1 row. > Explain file looks like this: > QUERY: (FIRST_ROWS OPTIMIZATION) > ------ > SELECT field1, field2 FROM table1 WHERE field1 LIKE '4210202116%' ORDER> BY field2 > 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. > 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. > 2nd statement: > SELECT field1, field2 > FROM table1 > WHERE field2 LIKE 'ALERT SIA%' > ORDER BY field1; > This statement executes 3 minutes and gives 1 row. > Explain file accordingly looks like this: > QUERY: (FIRST_ROWS OPTIMIZATION) > ------ > SELECT field1, field2 FROM table1 WHERE field2 LIKE 'ALERT SIA%' ORDER> BY field1 > Estimated Cost: 133538 (why not 198169 like above?) > Estimated # of Rows Returned: 42562 (really only one) > 1) informix.table1: INDEX PATH > Filters: informix.table1.field1 LIKE '4210202116%' > (1) Index Keys: field2 Same for this query as above. > > Using MEDIUM or HIGH options in UPDATE STATISTICS statement changes > 'Estimated # of Rows Returned' only. > Is there anybody who can explain me why this 'IDS 730.TC2 for NT' engine > don't use correct execution plan? 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. Art S. Kagel