Interested to Buy Any Domain ? << Click Here >> for more details...
Pls any body reply for this question.
Which circumstance does the optimizing choose a product join?
Thanks for advance
- 6 Answers
- 14082 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 |
In which scenarios we use Volatile tables and Global temporary Tables in Real time? Please give me one real time scenario???I know wat is its use??
Explain the term 'primary key' related to relational database management system?
If the script is aborted. why it is aborted and how can you identify ?
what is the advantages of other etl tool than teradata utilities or vice versa ?(datastage/informatica)
1.what is activity count? 2.what is skew factor? how it's working explain brefily
If the query is NOT WRITTEN PROPERLY then what are the recommendations you can give to the developer ?
What is database exceptions in teradata?
What is a three-tier data warehouse?
IN A TABLE CAN WE USE PRIMARY KEY IN ONE COLUMN AND IN ANOTHER COLUMN BOTH UNIQUE AND NOT NULL CONSTRAINS.IF YES HOW?PLZ GIVE ME A EXAMPLE.
Explain Teradata performance tuning and optimization?
What is teradata and why it is used?
What's the syntax of sub string?
Teradata 
