Archives of the TeradataForum

Message Posted: Thu, 12 Jul 2001 @ 11:42:05 GMT


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

Subj:		Re: Optimizing query path between large and small tables

From:		Claybourne Barrineau

Anomy Anom:

(assuming stats are collected on all columns and NUSIs involved in the join plan and filter conditions)

Just a thought...

Seemingly, what we want is for the 2 small tables to be joined via a cross-product join then duplicated on all amps. Once this is complete,then we should be able to sort the large table (amp local) based upon the 2 Join columns and join the 2 spool files.

Try creating a 2 column NUSI on the large fact table for the 2 columns used to join to the 2 small tables. Be sure to collect statistics on this new 2 column NUSI.

Execute query

If the cross-product of the 2 small tables still doesn't occur first, then you may want to try forcing this cross product in a derived table, then join the derived table to the large fact table.

Hope this helps,

Claybourne Barrineau