Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 26 Jun 2002 @ 15:13:11 GMT


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


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016