Re: Query Optimization: Wrong execution plan?

From: Xavi Tarafa Mercader <xavier.tarafa@corp.terra.com> > >I have a problem with a query: Are you sure it's the wrong execution plan? You're going to probably pull back most of the rows in both tables, aren't you? What happens if you run select * from tab1, tab2 where tab1.pk=tab2.fk and tab1.pk=somevalue ? Bet that doesn't do a sequential scan and hash join, does it? >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. > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com