Archives of the TeradataForum

Message Posted: Tue, 11 Oct 2005 @ 14:02:14 GMT

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

I was told (many moons ago) that there would be a feature of the optimiser in V2R5 that would address some of the queries which fall foul of this redistribution. At the time it was described to me as "early aggregation", and I have since heard it referred to as "partial group by" although I am not convinced these are one and the same thing.

Basically, I was told that when doing queries such as this :-

     sel
     t1.small_table_code
     ,t1.small_table_description
     ,sum(t2.large_table_measure)

     from small_table t1
     ,large_table t2

     where
     t1.small_table_pi=t2.large_table_column

     group by 1,2;

the optimiser would aggregate the large table as the first step (no redistribution of large table, no duplication of small table), then redistribute the resultant (much smaller) spool file, then join to the small table for the look up.

I usually write the SQL (using a derived table) to get the same effect, but was told that the optimiser would recognise these types of queries and do it for me (and for all the non-technical users we have who don't know how to write derived table queries). However, we are at V2R5.0 now and I have been unable to write a query which demonstrates this functionality.

Has anybody heard of this or got any clue what I am talking about or did I just dream it ?

Cheers

Howard

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