Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 27 Nov 2007 @ 14:57:09 GMT


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


Subj:   Re: Delete Statement taking a long time to run.
 
From:   Bourgoin, Greg

  I have a bteq script in which the DELETE statement is taking around 14 minutes to run....The number of records deleted with this statement is around 4325698.  


Instead of doing a DELETE of what you don't want, do a SELECT of what you do want. Its much quicker to work with empty tables to eliminate the trans logging. It'll take seconds to run. No promises, but I think this is what you want...

     CREATE TABLE edw1_temp AS edw1 WITH NO DATA
     ;

     INSERT INTO edw1_temp
     SELECT edw1.*
        FROM edw1

        LEFT OUTER JOIN edw2
          ON EDW1.employee_id = EDW2.employee_id
         AND EDW1.deptt_code = EDW2.deptt_code
         AND EDW2.deptt <> 'D'
     ;

     -- You didn't indicate your DBMS version so I included
     the ALL qualifier;

     DELETE FROM edw1 ALL;

     INSERT INTO edw1
     SELECT *
        FROM edw1_temp
     ;

     DROP TABLE edw1_temp;

     COLLECT STATISTICS ON edw1;


     
  <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: 15 Jun 2023