Sorting in a parallel database system is expensive, as it requires all rows available in one place. The rows of a Teradata table are distributed evenly across all Amps. Sorting would require moving all rows to one AMP. This would be a skewed, not scalable operation.

Teradata uses another sort approach. Here is how it works:

At first, the table rows are sorted on each AMP where they reside. This is always the last step of the execution plan. A higher number of AMPs permits a higher sort performance as all AMPs are sorting in parallel. The sorted rows are put into a spool table.

After AMPs finished their local sort step, the local spools will be returned to the requesting client. For performance reasons, Teradata creates a buffer and informs the client that the result set is available.

The client fetches as many rows as needed (keep in mind that most times, we “abort” our request after having a certain amount of rows available in SQL Assistant).

Each fetch request causes each AMP to move its top row into the buffer, where they are merged globally in sort order. Subsequently, each AMP puts its next row into the buffer where they are merged by the sort order. This continues until the buffer is full and can is sent via the attached network to the client. The merge process itself is done by the BYNET software (which handles the merge buffer on its own)

The whole process continues as long as the client fetches rows from the result set or no more rows are available.

There are two advantages to this approach: