Archives of the TeradataForum

Message Posted: Mon, 26 Sep 2005 @ 17:26:17 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Redestributing large tables instead of replicating a small table

From:		Victor Sokovin

I am trying to join a large 40 million row table with a small 2600 row table. But the explain plan is re-distributing the large table instead replicating the small table on all amps.

Here is the query:

          > SELECT * FROM
          > CONTACT C
          > LEFT JOIN
          > LOCATION D
          > ON
          >   C.LOC_ID = D.LOC_ID;

Assuming LOC_ID provide a good match, one can expect > 40 mln rows in the result set?

This is not exactly a realistic query, so it is difficult to comment on the execution plan for it. Perhaps what you see is the best way to handle a request of this kind?

In my opinion, one can speak of the problem with redistribution of the large table only if 1) the join is done on the full PI of the large table (is LOC_ID the PI?) and 2) one has really restrictive WHERE clauses on the large table.

Regards,

Victor