Explain Plan

Database

Explain Plan

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.

Thanks in Advance,
Sri
3 REPLIES
Enthusiast

Re: Explain Plan

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
Senior Apprentice

Re: Explain Plan

A product join is not always bad.
The optimizer might choose it for an equi-join because it's the most efficient way, especially if the duplicated table is small.

Check the estimated number of rows for the dupliaction step and divide it by the number of AMPs in your system to get the actual value.

Dieter
Enthusiast

Re: Explain Plan

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.