All Forums Database
sri krishna C 2 posts Joined 04/10
14 Apr 2010
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

srinivasa meka 10 posts Joined 01/10
20 Apr 2010

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

dnoeth 4628 posts Joined 11/04
22 Apr 2010

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

Dieter

gotuchintu 32 posts Joined 12/05
30 Apr 2010

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.

Somnath Roy

You must sign in to leave a comment.