RE: Query Optimization: Wrong execution plan?

>===== Original Message From Xavi Tarafa Mercader <xavier.tarafa@corp.terra.com> ===== >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. A dynamic hash join builds a hash table on the one table then does a sequential scan through the second table, using the hashing algorithm to do the join. If I recall correctly, once the hash table is built that the hash join finishes an order of magnitude faster than an nested loop join(use of index) So if you are joining 175,000 rows with 5.5 million rows it builds the hash table on the 175,000 rows, then it can go through the 5.5 million rows an order of magnitude faster than if it was doing the nested loop join to table 2. So the engine is deciding that the overhead of building the hash table will be made up by the speed of the join. I hope this helps, Will ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------

In article <8ne9hl$l7j$1@news.xmission.com>, William Rice <ricew@operamail.com> wrote: > > >===== Original Message From Xavi Tarafa Mercader > <xavier.tarafa@corp.terra.com> ===== > >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. > A dynamic hash join builds a hash table on the one table > then does a sequential scan through the second table, using the hashing > algorithm to do the join. > > If I recall correctly, once the hash table is built that the hash join > finishes an order of magnitude faster than an nested loop join(use of index) > > So if you are joining 175,000 rows with 5.5 million rows it builds the > hash table on the 175,000 rows, then it can go through the 5.5 million rows > an order of magnitude faster than if it was doing the nested loop join to > table 2. So the engine is deciding that the overhead of building the > hash table will be made up by the speed of the join. > > I hope this helps, > Will > > ------------------------------------------------------------ > This e-mail has been sent to you courtesy of OperaMail, as a free service from > Opera Software, makers of the award-winning Web Browser, Opera. Visit us at > http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail > account is waiting at: http://www.operamail.com/ > ------------------------------------------------------------ > > When join columns for both tables are indexed, the SELECT statement will most likely use the nested loop join unless there is a large amount of data in both tables to be read (this is the case). The time cost of a query is composed of several times, Activity in Memory, Sorts, The cost of readin a row, and so ... in this case is important de cost of Sequential Access. see your configuration parameters RA_PAGES and RA_THRESHOLD. Sent via Deja.com http://www.deja.com/ Before you buy.