Archives of the TeradataForum

Message Posted: Wed, 15 Jan 2003 @ 12:51:22 GMT

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

I agree with the analysis done by the others. I just want to point out that in this case a product join is not a bad solution. Because a product join often indicates that a query isn't fully qualified, or that some statistics are missing we have come to think of the phrase "product join" to think as an indicator of a problem. But in some cases it's the best available option. I think that the query you are describing is one of these cases.

Using the product join allows TD to leave the big table in place instead of sorting it differently. You would not really want to move the 360 million rows of the daily item table across amps... I would furthermore guess that Store has a UPI, so the optimizer already knows that the number of rows in the final spool is not going to be more than the input rows of daily_item.

It is true that qualifying on store would limit the final output, but keep the product join. But then a product join on a single row does not seem that bad.

I think the real problem here is that you have to scan the entire daily items table, no matter what. The only way around that would be a secondary index on store in daily item. But that will surely increase your loadtime for daily item. Also the secondary index would be fairly non-selective. So I'm not convinced that it ever would be used.

If you don't have performance problems with the query, don't be bothered by the fact that the word "product join" appears in the explain :)

Mirjam

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