 |
 |
Archives of the TeradataForum
Message Posted: Wed, 26 Jun 2002 @ 15:13:11 GMT
| Subj: | | Re: Ranking and concatenation |
| |
| From: | | John Street |
In fact the explain fore the OVER and the derived table solution gives exactly the same plan!
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock ADMIN.test1 for read.
| |
| | 2) | Next, we do a SUM step to aggregate from ADMIN.test1 by way of an all-rows scan with no residual conditions, and the grouping
identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 2. The size of Spool 2 is estimated with
high confidence to be 1 row.
| |
| | 3) | We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 1 row. The estimated time for this step is 0.17 seconds.
| |
| | 4) | We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by way of an all-rows scan into Spool 8 (Last Use), which is assumed to
be redistributed by value to all AMPs. The result rows are put into Spool 4, which is built locally on the AMPs.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 4 are sent back to the user as the result of statement 1.
| |
Cheers
John Street
Senior Principal Consultant,
Relationship Technology Solutions for Financial Services,
NCR Limited
| |
