Archives of the TeradataForum

Message Posted: Fri, 22 Jun 2001 @ 14:50:07 GMT

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

While this approach will change the path, I'm not sure that it will bebetter.

First some questions.

How small is small for the exchange rate table and how big is your system?

What is the PI of the new Atomic fact table? Since it does an extra redistribution in both cases, I'm assuming it is different than all three tables.

Geoffrey's approach should change the plan, but should also run much longer potentially, because it may now have to redistribute the 200 M X 15 M result to the amps to be joined to the exchange rate table. The other option is for the optimizer to now know that a duplication of the exchange rate table and then product join is much more attractive; however, the product join will now potentially take much longer so I doubt that the new approach will run much faster even in the best case (but you should still potentially try it)

I would definitely concentrate on statistics if the exchange rate table is very small but at 30 % of 15 Million rows, that probably isn't small enough to be duplicated. You could try collecting statistics on the exchange rate code column in the 15 M row table or dropping it if it is collected.

The reason that it take 150 % longer is that the first approach is all amp local which is an extremely fast join. When you add more tables then the join plan would naturally be much slower.

I'd have to see the tables and play a bit to see if there was muchI could do with this. Probably not much other than a join index or denormalized 15 M row table to store the exchange rate with null for the 70 % missing. Both have resource and batch load issues for the DW update times.

This join may not be so bad if you only going after a small amount of the table at a time, i.e. User queries with constraints rather than building a denormalized fact table for everything at once, or if you had a way to constrain the exchange rate table to reduce the rows that qualify (not an option, right).

The only other option is to deal with the join times you are currently getting.

Thanks,

Eric

EJK
Eric J. Kohut
Senior Solutions Consultant - Teradata Solutions Group - Retail
Certified Teradata Master
NCR Corp.

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