I can't think of the subject name (have not words).
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%' ORDERBY 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?)
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%' ORDERBY 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
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?
Have a nice day. Leonid. (Leonids.Voroncovs@dati.lv)