Explain Plan for SELECT- MAX query

General
Highlighted
Enthusiast

Explain Plan for SELECT- MAX query

Hi

I want to understand how the max value is calculated in Teradata.

Eg: My query is 

select max(price) from item;

My understanding is Parsing Engine will ask all AMPS to share the maximum value of price column they have for table item. Each AMP will returned it max value into a SPOOL. So say if there are 20 AMPS which store portion of item table. Then all 20 AMPs will return max value they have in their vdisk. Now in SPOOL we will have 20 values , one from each AMP.

If this understanding is correct I wish to know who will pick the MAX value out of these 20 values in SPOOL. Is it the PARSING ENGINE or it will ask one of the AMP to pick the MAX value from these 20 values and return the result ?

Kindly confirm my understanding.

 

TIA

Nitin


Accepted Solutions
Teradata Employee

Re: Explain Plan for SELECT- MAX query

In the explain plan there should be a single SUM step that includes the word "global" in the description. This says that first each AMP calculates its local aggregation result, then forwards that to a chosen AMP which will complete the aggregation. That AMP will only receive as many rows as there are AMPs and will produce the single row result. The 20 rows will not be placed in a spool, for this kind of aggregation all the work will be done in memory until the final single row result is produced.
1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: Explain Plan for SELECT- MAX query

In the explain plan there should be a single SUM step that includes the word "global" in the description. This says that first each AMP calculates its local aggregation result, then forwards that to a chosen AMP which will complete the aggregation. That AMP will only receive as many rows as there are AMPs and will produce the single row result. The 20 rows will not be placed in a spool, for this kind of aggregation all the work will be done in memory until the final single row result is produced.