Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 18 Feb 2003 @ 19:32:39 GMT

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

Subj:   Re: INSERT/SELECT out of one table into two
From:   Terry Stover

When you are inserting into an empty table with the same PI the optimizer is smart enough to realize it doesn't need to do a dupe row check. Same thing is supposed to happen when your target table has a subset of the source table PI columns (we used to make 2 copies of an end of month reporting table, the PI's were optimized for different sets of reports). We found a bug on that feature during 4.1.3 regression testing. The optimization may not be very robust, maybe the multistatement request screws it up. Does it work on each statement independently?

For performance I'd recommend changing the where criteria. The CAST(SUBSTR(EXTRACT_TS, 1, 10) AS DATE) < '2003-01-10' will have to do a calculation on every row to find the qualifying records. Try converting the '2003-01-10' to a timestamp constant instead (I'm assuming EXTRACT_TS is a timestamp), no point in wasting cpu. I've had 20x performance improvement for similar situations (where the constraint was = / IN not inequality), but those also had other where clause contraints.

  <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: 28 Jun 2020