DYNAMIC HASH JOIN and SEQUENTIAL SCAN

Hello All. I have two tables: one table is small and one table is big. I want to join their. Big table has index on column which will be joined. Optimizer wants to do DYNAMIC HASH JOIN. Does optimizer do SEQUENTIAL SCAN on big table?

most likely. what does sqexplain say???? Superboer. way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- _oerend_hard.html On 3 dec, 09:22, mikz2...@mail.ru wrote: > Hello All. > > I have two tables: one table is small and one table is big. > I want to join their. > Big table has index on column which will be joined. > Optimizer wants to do DYNAMIC HASH JOIN. > Does optimizer do SEQUENTIAL SCAN on big table?

> most likely. what does sqexplain say???? Sqexplain says that big table does not have SEQUENTIAL SCAN. But here (http://www.ibm.com/developerworks/db2/zones/informix/library/ techarticle/0502fan/0502fan.html) you can find interesting article about Query Directives. This article says that "...In this join (Hash join or dynamic hash), one of the tables-usually the smaller table-is scanned and used to create a hash table in the memory. Using the hash function, each row is put in a "bucket" with other rows that have the same hash value. After the first table has been scanned and placed in a hash table, the second table is scanned once, and each row is looked up in the hash table to see if a join can be made...." I think that keywords are "...AFTER THE FIRST TABLE HAS BEEN SCANNED AND PLACED IN A HASH TABLE, THE SECOND TABLE IS SCANNED ONCE...". I.e. optimizer (or Informix) scanned all two tables. Is it right?

> I.e. optimizer (or Informix) scanned all two tables. Is it right? > Sqexplain says that big table does not have SEQUENTIAL SCAN. If that is the case then the whole index for big table is scanned???!!! assume you have a test system... and noone accesses it but you.. onstat -z dbaccess <yourdb> yourqr.sql > /dev/null assume TBLSPACE_STATS 1 in your onconfig dbaccess sysmaster <<! select * from sysptprof! this will tell you which/what gets a seq scan.. could be the index of bigtable... There are situations where a seq scan/ hash join can get results faster. ( if 20 % or more needs to be read from a table.....) is this true in your case?? if not then why isn't there an index on small table?? or if there is a correct index on small table try and use optimizer hints to avoid hash join and measure how long this will take... what is optcompind set to?? are statistics up to date???? Superboer. way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- _oerend_hard.html On 3 dec, 10:56, mikz2...@mail.ru wrote: > > most likely. what does sqexplain say???? > > Sqexplain says that big table does not have SEQUENTIAL SCAN. > > But here (http://www.ibm.com/developerworks/db2/zones/informix/library/ > techarticle/0502fan/0502fan.html) > you can find interesting article about Query Directives. This article > says that "...In this join (Hash join or dynamic hash), one of the > tables-usually the smaller table-is scanned and used to create a hash > table in the memory. Using the hash function, each row is put in a > "bucket" with other rows that have the same hash value. After the > first table has been scanned and placed in a hash table, the second > table is scanned once, and each row is looked up in the hash table to > see if a join can be made...." > I think that keywords are "...AFTER THE FIRST TABLE HAS BEEN SCANNED AND > PLACED IN A HASH TABLE, THE SECOND TABLE IS SCANNED ONCE...". > I.e. optimizer (or Informix) scanned all two tables. Is it right?

> assume you have a test system... and noone accesses it but you.. > > onstat -z> > dbaccess <yourdb> yourqr.sql > /dev/null > > assume TBLSPACE_STATS 1 in your onconfig > > dbaccess sysmaster <<! > select * from sysptprof> ! Thank you very much for your advice. I will test it tomorrow, when I will have access to system.

On Dec 3, 6:44 am, mikz2...@mail.ru wrote: > > assume you have a test system... and noone accesses it but you.. > > > onstat -z> > > dbaccess <yourdb> yourqr.sql > /dev/null > > > assume TBLSPACE_STATS 1 in your onconfig > > > dbaccess sysmaster <<! > > select * from sysptprof> > ! > > Thank you very much for your advice. I will test it tomorrow, when I > will have access to system. I think that the TABLE is scanned for both tables. Scan small table first to build the smallest hash table possible. Then the big table will scan hashing each join value and looking it up in the small hash table built earlier.