Hi All, I am doin an innerjoin on two tables with a condition say A.col1=B.col1, when I do explain on the query it shows that a product join is been made between the tables though there is a join condition.Can any body explain me why it's happening and how to avoid the product join.
We avoided this type of condition by using a where condition clause, which was redundant in a way; similar to, customer_id > 0 and this forced partition based scanning. Underlying fact table has customer_id at primary partition level.
Also, look into this posting from Dieter. http://www.tek-tips.com/viewthread.cfm?qid=927188&page=11
If one of the table is very small, say 100 rows, and the other table is very big, say 100 million rows, the optimizer can go for a product join and for them that's the best plan. If you are having two very big tables & optmizer is going for product join, then collect stats on the indexes & joining columns. If still it persists, use "DIAGNOSTIC NOPRODJOIN ON FOR SESSION" before executing the SQL. This will forcefully tell the optimizer not to use the product join.