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

David Kosenko wrote: > > 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);There is my error - index1 and index2 not needed. I simply forgot drop it, sorry. > > +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. There is a good idea to use indexes for selecting and ordering in the Your first sentence. I don't understand Your second sentence, why IDS won't consider all (even though somewhat) execution plans - documentation tells us about cost based optimizer. > > +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?) SEQUENTIAL SCAN - is my joke, do You understand me? There is above mentioned index3 on the table. > > 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. This sentence is not correct. In index3 field2 is alredy sorted, but IDS chooses reading through all index2 pages, I don't understand why IDS not uses index3 or even though index1 that will be faster. > > + > + 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)? No, I don't. 120 character field alredy sorted in index4. > > +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%' There is my error (magic key combinations Ctrl-Ins and Shift-Ins, I am sorry). Must be: Filters: informix.table1.field2 LIKE 'ALERT SIA%' > + > + (1) Index Keys: field2 There is my error too. Must be: (1) Index Keys: field1 > + > +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.With this values execution plans is more ridiculous. > > -- > Dave Kosenko davek@summitdata.com > Director of Training Services (732) 469-4070 > Summit Data Group (an Informix Authorized Education Center)