Archives of the TeradataForum

Message Posted: Fri, 09 Mar 2001 @ 10:53:46 GMT

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

John,

Even if there is a join condition coded, you may still end up with a product join. If a product join is the optimisers choice for the fastest way to perform a join, then it'll use it and you'll see the 'product join' in the explain output.

One case (perhaps trivial) is where you join a one-row table with a large table on a column which is not indexed on the large table. In this case, the optimiser will almost certainly duplicate the small table on all amps and then 'product join' the spool file with the large table. Because one side of the join is a spool file with just one row per amp you don't get any duplication of result rows. This is quicker because the alternative is to scan the large table and copy it into spool, then sort it ready for a merge join. A lot slower !

Cheers,

Dave

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