Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 07 Feb 2002 @ 01:29:03 GMT

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

Subj:   Re: UPDATE Statement from another table
From:   Thomas F. Stanek


A couple of comments on your response to Matt.

1) The Insert/Select approach you mentioned will only be "extremely fast",if the underlying join in the INSERT is fast. Although you are eliminating the transient journal using this technique, the TJ overhead can be minimal compared to the impact of a bad join strategy.

2) I would hesitate to COLLECT STATISTICS on the join columns without first considering the use of the table overall. I agree that stats can often improve the join strategy by giving the optimizer more accurate info. However, those same stats could cause other queries that are currently working efficiently to become less so. Just a caution more than anything.

3) As you pointed out, the EXPORT/MLOAD approach will also avoid the TJ and is restartable as well. But again, if the join strategy on the EXPORT is bad, this approach will also perform poorly.

I think the EXPLAIN has to be the first thing to check. If Col. 6 is not the primary key (or at least the primary index) for both tables, it seems that there is at least the potential for the DBMS to do a lot of work to determine what rows to update.

Something to consider.

Thomas F. Stanek
TFS Consulting

  <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