Pls any body reply for this question. Which circumstance does the optimizing ch

Categories >> Software >> Data Warehouse >> Teradata
Suggest New Category

Pls any body reply for this question.
Which circumstance does the optimizing choose a product join?
Thanks for advance

Question Posted / dama.valli9

6 Answers
12440 Views
Cap Gemini, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / ab75150

Product join occurs in 2 circumstances.

1) Your stats are out-dated
2) One table is a substantially huge and the other is very
small. In that case, optimizer identifies that is better to
replicate the smaller tables across all amps and then
complete the join.

In later case, product join is not bad. but if the stats
are outdate, better refresh the stats and go ahead with the
execution.

Incase your stats are good and still optimizer goes the
product join path, if you really want to avoid it, you may
force the optimizer not to choose it with following command
DIAGNOSTIC NOPRODJOIN ON FOR SESSION

Is This Answer Correct ?

5 Yes

0 No

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / etlguy

Product join compares all rows in 1st table with rows in 2nd
table. This join is generally chosen by optimizer whenever
there in an inequality condition in join. This is because
hash values cannot be compared for greater than or lesser
than which results in product join. Also, product join uses
spool files for redistribution or duplication of tables,
which is not desirable.So, it is advisable to avoid product
join as far as possible.

Is This Answer Correct ?

5 Yes

0 No

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / charan

Actually we never use Product join in Real time of teradata. because it occupies more spool space and more rows it will be give.so, we dont use product join.

Is This Answer Correct ?

2 Yes

0 No

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / yuvaevergreen

Product join in not desirable unless required as it involves
over usage and unnecessary usage of resources. The query
designer should always use conditions in the query or sub
queries to reduce the no of qualifying rows. This can be
easily observed by analyzing the execution plan. So TD
assumes "product join" when there are tables involved
without any condition. Example below,
SELECT * FROM A,B;
Since no filtering condition has been used, this results in
a product join.

Is This Answer Correct ?

3 Yes

2 No

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / yuvaevergreen

Small correction to my answer mentioned above. Join
mentioned in the statement is actually cartesian product
join. SELECT * FROM A,B will result in cartesian product and
not product join. Product joins would be chosen for join
conditions containing inequality.

Is This Answer Correct ?

1 Yes

0 No

Pls any body reply for this question. Which circumstance does the optimizing choose a product join?..

Answer / shaik mahamad rafi

When ever we write the join for retrieve the data from two or more tables ,the alias names should be no matching data type to the join columns ,the optimizer produces Product join

Is This Answer Correct ?

0 Yes

0 No

Post New Answer