Archives of the TeradataForum

Message Posted: Wed, 30 Jul 2003 @ 10:40:59 GMT

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

Hi Aniruddha,

The time taken for each join depends on the join strategy planned by the optimizer to physically perform the join. The join strategy cannot be predicted by the relational join type.

For the first query if you see the explan, the 5th Step tells that its using a product join.

"VMALL.a11 and Spool 2 are joined using a product join, with
     a join condition of ("((( CASE WHEN NOT ({RightTable}.COUNTRY_ID
     IS NULL )THEN ({RightTable}.COUNTRY_ID) ELSE
     ({RightTable}.COUNTRY_ID) END )) = VMALL.a11.Country_Id) AND (((
     CASE WHEN NOT ({RightTable}.DIST_CTR_ID IS NULL )THEN
     ({RightTable}.DIST_CTR_ID) ELSE ({RightTable}.DIST_CTR_ID) END )) =
     VMALL.a11.Dist_Ctr_Id)").  "

Product joins get their name from the fact that the number of comparisons required is the "product" of the number of qualifying rows of both tables. A product Join between a table of 1,000 rows and a table of 50 rows would require 50,000 comparisons and a potential answer set of 50,000 rows. Because all rows of one side must be compared with all rows of the other, the smaller table is always duplicated on all AMPs. Its rows then are compared with the AMP local rows of the other table. If the entire table cannot fit into memory, blocks will have to be read in more than once. Comparisons that qualify are written to spool. Larger systems will require more spool to duplicate tables.

The first join run out of spool space because of this product join.

Product Joins are caused by any of the following:

* The WHERE clause is missing.

* A Join condition is not based on equality (NOT =, LESS THAN,GREATER THAN).

* Join conditions are ORed together.

* There are too few Join conditions.

* A referenced table is not named in any Join condition.

* Table aliases are incorrectly used.

* The Optimizer determines that it is less expensive than the other Join types.

The optimizer chooses merge join for the second query which is always better than product join.

Regards,

Manoj

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