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
- 14260 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 |
What is meant by a Least Cost Plan?
in ur table contains the coloumns like deptno,sal empname then i want output as deptno subtotalofdept totalsal 10 3700 3700 20 3400 7100 like that
How to run a query in teradata?
What is the use of virtual processor connectivity in teradata?
What type of indexing mechanism do we need to use for a typical data warehouse?
write lock is compatiable with which type of lock?
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??
What is meant by a dispatcher?
We are migrating an oracle table into teradata, the volume of data is huge and partitioned (year wise list partition). How do i simulate the same in teradata.
A certain load is being imposed on the table and that too, every hour. The traffic in the morning is relatively low, and that of the night is very high. As per this situation, which is the most advisable utility and how is that utility supposed to be loaded?
What is a sparse index?
What are default access rights in teradata?
Teradata 
