Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 14:33:59 GMT


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


Subj:   Optimizing SQL
 
From:   Ferry, Craig

I am trying to find a way to speed up this SQL. Currently this runs for an hour. There are 130081 rows in the TMP_dpc_status_daily table and 224457 in the TMP_dpc_status_daily2 table.

I have the table ddl below (both tables are identical).

Before I run the update, I have collected statistics on every column and the index.

Any feedback would be greatly appreciated.


Thanks

Craig


     UPDATE a
     FROM dssbatch.TMP_dpc_status_daily a, dssbatch.TMP_dpc_status_daily2
      b
     SET last_sale_date = b.last_sale_date
     WHERE a.branch_id = b.branch_id
     AND a.customer_no = b.customer_no
     AND b.last_sale_date > a.last_sale_date
     OR a.last_sale_date is null;


     CREATE MULTISET TABLE dssbatch.TMP_dpc_status_daily_daily2 ,NO FALLBACK
     ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
     (
           branch_id SMALLINT,
           customer_no CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
           first_sale_date DATE FORMAT 'YYYY/MM/DD',
           last_sale_date DATE FORMAT 'YYYY/MM/DD'
     )
     PRIMARY INDEX ( branch_id ,customer_no );


     
  <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