All Forums Database
dixon 22 posts Joined 01/06
09 Mar 2006
on product join

how to eliminate product join in a query?

Dixon
TDUser-781 35 posts Joined 12/04
09 Mar 2006

Well, this is a bit wide question....To start, try analyzing the explain plan and collecting statistics on the join fields.Bye,TDUser

williamdieter 15 posts Joined 03/06
21 Mar 2006

Several key things you can look at:1. Ensure statistics are collected on join columns - This is especially imporatant if the column you are joining on are not unique. If your join column only has a few hundred unique values, TD might redistribute the table on those columns, and duplicate it on all AMPs. This could take an extremely long time when you have a large table. If it's a small table, then a product join is usually the least expensive.2. Make sure you are referencing the correct alias (if you have one). The query below will result in a product join (because you are joining a to a, and nothing to b)selecta.column1frommytable1 ainner joinmytable2 bona.column1 = a.column1Also, if you have an alias, you must always reference it instead of a fully qualified tablename. The query below will result in a product join:selectmytable1.column1frommytable1 ainner joinmytable2 bona.column1 = a.column1;3. Sometimes product joins happen for a good reason. Joining a small table (100 rows) to a large table (1 million rows) a product join does make sense.If you post your query, then we might be able to pick it apart, but without knowing the underlying data it could prove difficult.....still it wouldn't hurt to have another pair of eyes on it.

22 Mar 2011

Sometimes product joins happen for a good reason. Joining a small table (100 rows) to a large table (1 million rows) a product join does make sense?
can you tell me why it happens???

Thanx,
Mahesh

Koentje 23 posts Joined 09/04
01 Apr 2011

I you follow the explain plan, you will see that Teradata is reading the small table first. This table is then duplicated (copied) to all amps.
Now the big table and the small (duplicated) table are joined with a product join.
Eg. A small table with 100 rows (on 50 amps). The result after duplication are 50 tables with 100 rows each (50 * 100 = 5000 rows). This result (spool) is joined with your big table by using a product join.

This kind of ‘product join’ is very fast and different from product joins when statistics are not up to date or when the correct join condition is not added (well) in your query.

11 May 2013

Hi Koen ! Thanks for great elaboration !

Thanx,
Mahesh

You must sign in to leave a comment.