Query Optimization: Wrong execution plan?

Hello All, I have a problem with a query: The query is the join of two tables select * from tab1, tab2 where tab1.pk=tab2.fk There're indexes on both fields (unique index on tab1.pk and indexes with duplicates on tab2.fk). The problem is that the query takes too much time. The the tables sizes are: tab2: 5'5 Milions rows, tab1: 175.000 rows. I've executed UPDATE STATISTICS medium for all the datbase and high for those fields involved on the query. I've looked at the plan chossen and it is as follows: Estimated Cost: 714082 Estimated # of Rows Returned: 5534351 1) informix.tab1: SEQUENTIAL SCAN 2) informix.tab2: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.tab2.fk= informix.tab1.pk I've the following questions, Why does the plan read sequentially both table instead of reading one using the index? I think it would be faster. How does a "DYNAMIC HASH JOIN" work? Any suggestion about how to improve the query performance? Thank you, Xavi.

Xavi Tarafa Mercader wrote: > Any suggestion about how to improve the query performance? Set OPTCOMPIND parameter ( in onconfig file ) to 0. > Xavi. Leonid.