Archives of the TeradataForum

Message Posted: Fri, 22 Jun 2001 @ 12:49:02 GMT

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

First, do you have up-to-date statistics on the PI of the two large tables and on all columns of the exchange rate table? (You probably do, but I'm just making sure.)

Second, you said that "even though the Exchange rate table is small, a cross-product is out of the question for the optimizer." Just out of curiosity, how would you know what the optimizer is thinking? On our system, the optimizer often performs product joins on small tables (duplicated on all AMPs), and it works nicely.

Anyhow, assuming that you have good statistics and are still getting the results described, you can get around your problem by using a derived table. Just do the join of the two large tables as a derived table (which must be evaluated first) and then join that to the exchange rate table. The SQL would look something like this:

insert into atomic_facts
select fa.stuff, ex.stuff
 from (select morestuff
         from facts, attribs
         where facts.PI = attribs.PI) fa,
   exchange_rates ex
 where ... ;

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