Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 20 Apr 2011 @ 11:14:43 GMT


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


Subj:   Drop ji and then recreate it to avoid overhead?
 
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, April 20, 2011 06:57 -->

I have the following query which is taking 5.50 mins to execute

     SELECT  LIST_NAME,ORIGINAL_NAME, COUNT(*)
     FROM EMPLOYEE E, LIST L
     WHERE E.LIST_ID=L.LIST_ID
     GROUP BY LIST_NAME,ORIGINAL_NAME

Can someone help me in tuning the above query. Planning to use aggregate join index for this as users expect this query to run in approx 20 secs. To avoid overhead(insert s and update s), planning to create JI after every day s load is processed and then drop it just before next days load as utilities are not supported with JI s. Any inputs are much appreciated.

Explain plan:

     6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way
        of an all-rows scan , grouping by field1 (L.LIST_NAME, E.ORIGINAL_name).
        Aggregate Intermediate Results are computed globally, then placed in
        Spool 5.  The aggregate spool file will not be cached in memory.  The
        size of Spool 5 is estimated with no confidence to be 846,399 rows
        (9,496,596,780 bytes).  The estimated time for this step is 5 minutes
        and 30 seconds.

Thanks



     
  <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