Archives of the TeradataForum

Message Posted: Wed, 09 Feb 2005 @ 20:06:26 GMT


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

Subj:		Re: Sorting in teradata

From:		Michael Larkins

Naveen:

When you say:

It is the final spool file (spool1) that gets sorted, each AMP will not do sort operation.

Are you saying that you believe all of the rows selected on all the AMPs are redistributed to a single AMP and sorted in SPOOL1? I do not see that in the EXPLAIN.

Do you realize that SPOOL1 will exist on an AMP that has at least one row (group_amps) from the select? This is the same as PERM space where it resides on each and every AMP.

For the following EXPLAIN SELECT there is no redistribution mentioned in the EXPLAIN, therefore each AMP is sorting the rows in its own SPOOL1. Once the rows are sorted by each AMP having at one row, they are sent back to the user and that is where the merge takes place on the BYNET as I mentioned previously:

     EXPLAIN sel * from employee_table
     where salary > 30000
     order by salary;

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct SQL_CLASS."pseudo table" for read on a RowHash to prevent global deadlock for SQL_CLASS.employee_table.`
	`2)`	`Next, we lock SQL_CLASS.employee_table for read.`
	`3)`	`We do an all-AMPs RETRIEVE step from SQL_CLASS.employee_table by way of an all-rows scan with a condition of ("SQL_CLASS.employee_table.Salary > 30000.00") into Spool 1 (group_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with no confidence to be 4 rows. The estimated time for this step is 0.03 seconds.`
	`4)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.03 seconds.`

Also notice that the END TRANSACTION is sent to all AMPs involved - are you saying this is always one AMP?

Please explain your EXPLAIN.

Thanks,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor


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

Attachments

Library

Quick Reference