Could anyone pls me reasons for Product Join.
In which Scenaries the query uses product join.Please share me some reasons....
Based on the stats available, the optimizer will decide on the best possible plan for the execution of a query. If two tables are being joined and one of them is a pretty small table, usually the following would be the paths.
1. Product join.
2. Small table duplicated on all the amps and then join happening local to each amp(may be redistributed depending on PI).
3. if both the tables PI's are the same it will do a amp local join.
4. if the size of the tables is considerably big, there would be a sliding window merge join.
There are other types also. it is a good practice to usually run the explain plan of the query before executing it.
Here is a scenario which explains a product join occuring.
If there is a table with a PPI column defined and the partition let us suppose to be defined for a 90 day period and has rows for 86 days of data only. when you collect the partitions on the partition column, the help stats will show the partition unique values as 86. let us suppose you load 2 more days of data in that table but did not collect the stats. now you run a query joining this table with another table and selecting the 2 new dates or one of the new date in the where clause. As the optimiser is not aware(based on the stats), that there is data in the new 2 partitions, the explain plan may show that you have only one record in the table for the new partition. in this case the query will go for a product join. refreshing the stats would resolve the issue.
Usually you see the product join when one of the tables is small or the stats are not refreshed on a periodical basis on a big table.
please help me out.
how optimizer choose join strategy in teradata....
I mean based on which condition it choose particular join strategy ( merge join, hash join, nested join).