Archives of the TeradataForum

Message Posted: Wed, 02 Dec 2004 @ 00:18:25 GMT

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

In the Teradata SQL Reference - Volume 2 Statement and Transaction Processing in Chapter 3: Join Optimizations it discuss Large Table/Small Table (LT/ST) joins in some detail. But I'll try and give you an example from the Retail world.

In the Retail world your large fact tables usually are keyed by Location, Product and Date (plus maybe some other stuff). Some combination of these columns usually make up the PI.

Let's say you need to join the Location table, Product table and a large fact table within a query. You are restricting the values in you Location table and your Product table, but not necessarily by the Product Id and Location Id (Product Id and Location Id are the PI on the large fact table). You want all Locations within a Division and all Products within a Category.

What the optimizer MIGHT do is apply the restrictions (Division and Category) to the Location and Product tables and then do a Cartesian product (1=1) of these two spools to generate a spool that contains both the Product Id and the Location Id. It can then distribute this spool by hash (Product Id and Location Id) in order to do a merge join into your large fact table.

Typically this will provide you with some excellent performance against even the largest of tables. The performance is so good that many times you'll see people adding tables to a query that they don't need any columns from in order to force the LT/ST join.

Hope this helped,

Robert Meunier

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