Archives of the TeradataForum

Message Posted: Tue, 06 Mar 2007 @ 10:34:16 GMT

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

Goutham P:

From reading the previous replies, I think that most of the bases (possibilities) of your question have been answered. However, for your consideration my observation is that the OLAP functions are still going to do internally the same type of processing as a derived table. They must calculate the aggregartion (SUM/AVG/et al) for all rows or those of a partition and match them to each row in the original table. The difference is that we don't code the join condition like we do in a derived table (ie. your query). All we see in the EXPLAIN is that it is doing a STAT function. The estimated time and the number of rows is usually fairly consistent with the explain of a derived table. It might be my false impression, but to me the run of an OLAP function seems to take slightly longer - again, at this time I have not taken the time to validate that impression.

Regardless of efficiency of OLAP, your query could be improved by not using the OLAP and putting the aggregation into your derived table. That way you are not joining only unique rows to detail rows, you are joining summary rows (including the value you are selecting) to detail rows. At the same time as has already been pointed out to you, since you are not comparing the aggregated nor the OLAP result - why do a join at all? In your query this is a MAJOR waste of time and system resources. Chrysler use to build cars because they could. When Iaoccoa (spelled wrong? - sorry Lee) came along they started building cars that people actually wanted to buy. Then Chrysler had a reason to use Teradata too.

It looks like you are joining simply because you can with no apparent benefit to anyone. Instead it is a detriment. Join only when it buys you a favorable result not attainable any other way. Just a thought.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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