Archives of the TeradataForum

Message Posted: Fri, 09 Jul 1999 @ 16:50:03 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

A product join, as Andy points out, joins every row of the left table to every row of the right table. Typically it is chosen as the join method, when one of the tables is small. Since it basically needs to read every row of the left table once, and every row of the right table n times, where n is the number of rows in the left table, it can be expensive to execute, if the number of rows in the left table is large.

The typical alternative is merge join. The extra step, however, that is needed to run a merge join is to sort the two source tables. If the tables are already in sorted order this step is not necessary. A merge join executes very fast, since it only reads both tables once. In addition, it is possible, depending on the join selectivities, that not all the datablocks of both tables need to be read.

Whether product join or merge join is the correct choice depends, of course, on the actual table cardinalities and selectivities. The optimizer can make mistakes in it's selectivity estimates. There are ways of improving the optimizer estimates, typically by collecting statistics.

The two queries are not logically equivalent. Do you get the same answer for both? It is possible to get the same results, if the nested selects for both queries return the same distinct (acct_num,system_id) combinations. In this case, pushing the predicate into the nested select serves only to improve the optimizers selectivity estimate.

There are several different join methods. Product join and merge join are the most common ones. The other common ones are nested join, hash join, several flavors of exclusion joins (which you should be seeing in your query), and several versions of inclusion joins.

Pekka Kostamaa

	<Prev	Next>		<<First	<Prev	Next>	Last>>