Archives of the TeradataForum

Message Posted: Fri, 22 Jun 2001 @ 10:47:10 GMT

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

All,

Every week-end we build a datamart Atomic Fact from our 3rd normal form data warehouse. The final step of this transformation joins the 3rd normal form fact table (200 million records) to an attribute table (15 million records) which shares the same Primary Index. I also have to left outer join the 3rd normal form fact table to an exchange rate table (10,000 records) inorder to convert all revenue information (stored as local currencies) into US Dollars. We have revenue information for about 30% of our data in the 3rd normal form fact table.

The problem is that the primary index of the Exchange Rate table is different than the 2 other tables and it doesn't contain any of the columns which make up the primary index of the 2 other tables. Also, eventhough the Exchange rate table is small, a cross-prodcut is out of the question for the optimizer.

Therefore, the optimizer:

1)-redistributes the 15 million record table to all Amps hashed by the primary index of the exchange rate table, sorts, then performs a merge join

2)-redistributes the resulting spool file to all Amps hashed by the primary index of the 200 million record table, sorts, then performs a merge join

3)-groups then redistributes the resulting spool file based upon the primarary index of the datamart Atomic fact table

Without the Exchange rate table, the optimizer:

1)-performs a merge join of the 200 million and 15 million record tables (same PI)

2)-groups then redistributes the resulting spool file based upon the primarary index of the datamart Atomic fact table

This tiny, little exchange rate table causes the query to perform about 150% slower than without it.

Does anybody have any suggestions or experiences regarding this type of problem?

Any feedback would be greatly apprciated.

Thanks,

Claybourne Barrineau

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