Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 12 Aug 2005 @ 18:37:37 GMT


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


Subj:   Explain Analysis
 
From:   Hassinger, Bill

Can someone tell me why none of the WHERE qualifiers appear in the EXPLAIN for the following query?

     SELECT CountyNbr,
     ProgramID,
     SubString(IssueDate From 1 For 6) AS PayDate,
     sum( NetPaymentAmount) As Amount
     FROM xxxxxViews.yyyyyV
     WHERE IssueDate between '20050101' AND '20050831'
      AND ProgramID <> 'ft'
      AND CountyNbr = (31)
     group by 1,2,3
     ORDER BY 1, 2, 3;
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct xxxxxProd."pseudo table" for read on a RowHash to prevent global deadlock for xxxxxProd.Payment.  
  2)Next, we lock xxxxxProd.Payment for read.  
  3)We do a SUM step to aggregate from xxxxxProd.Payment by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 2. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 2 is estimated to be 18 rows.  
  4)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. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with low confidence to be 18 rows. The estimated time for this step is 0.17 seconds.  
  5)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.  



     
  <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