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

Leonid Vorontsov <Leonids.Voroncovs@dati.lv> offerred: CREATE TABLE table1 ( field1 CHAR(11), field2 CHAR(120), ); 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); +OPTCOMPIND 0 +OPT_GOAL 0 You have set things up so that IDS will use an index to access the table whenever possible. It won't even consider a hash join or sequential scan if useable indexes are present. +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) + +1) informix.table1: INDEX PATH (why not SEQUENTIAL SCAN with temporary +table sorting?) Because you told it NOT to do so with the config params above! When you added the ORDER BY, you created a query that cannot return even the first row until all rows have been retrieved and sorted. + + Filters: informix.table1.field1 LIKE '4210202116%' + + (1) Index Keys: field2 + +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?) You don't think that sorting by a 120 character column (as in the first query) should be more costly than sorting by an 11 character column (as in this query)? +Estimated # of Rows Returned: 42562 (really only one) Note that the est rows returned is the same. It is an estimate, and is only as good as the stats available. WIth low stats only, it will use a selectivity factor of 1/5 for a LIKE clause (42562 * 5 == approx 200000, which you stated as the volume of data in the table). + +1) informix.table1: INDEX PATH + + Filters: informix.table1.field1 LIKE '4210202116%' + + (1) Index Keys: field2 + +Using MEDIUM or HIGH options in UPDATE STATISTICS statement changes +'Estimated # of Rows Returned' only. With more accurate stats on the distribution of values in field1, it should be able to get a bit closer. But since you are using a LIKE, it cannot get completely accurate. It should come up with the number of rows contained in the distribution bin containing those particular field values. If you want a more accurate estimate, use a lower RESOLUTION value for your update stats high. +Is there anybody who can explain me why this 'IDS 730.TC2 for NT' engine +don't use correct execution plan? It has chosen the plan you told it to prefer with the settings above. To let it choose the best possible plan, set OPT_GOAL to -1 and OPTCOMPIND to 2. -- Dave Kosenko davek@summitdata.com Director of Training Services (732) 469-4070 Summit Data Group (an Informix Authorized Education Center)