Pls any body reply for this question.
Which circumstance does the optimizing choose a product join?
Thanks for advance
- 6 Answers
- 12406 Views
- Cap Gemini, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
If the PMON is not working then how do you manage and monitor all processes, resources and sessions etc.
How does hashing happens in teradata?
What is the Maximum number of volatile tables that can be created?
How do you set the session mode parameters in bteq?
Why do Hash joins usually perform better than Merge Joins?
What are the different functions included in the server software?
What is inner join and outer join?
What is meant by a node?
can we have an unconnected lkp to lookup a DB2 record against a Teradata record?
If the query is NOT WRITTEN PROPERLY then what are the recommendations you can give to the developer ?
how many modules are there in telecome domain?how to explain the architecture?
Comment whether bottleneck is an error or not.
Teradata 
