Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 06 Apr 2015 @ 12:17:18 GMT


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


Subj:   Re: Inserting/Updating same target table simultaneously by multiple sessions without locking
 
From:   Dave Wellman

Hi Rishi,

Update with a correct PI is an option BUT ONLY IF you are reading data from an external source (i.e. records in a file) - which is exactly what Tpump/TPT Stream does (the suggestion that I made in my earlier post).

Assume tables T1 and T2 both have a PI of column C1 and another column C2.

The following request will use a row-hash lock:

     UPDATE T1
       SET C2 = new-value
     WHERE C1 = a-value;

The following request will use a row-hash lock:

     UPDATE T1
       SET C2 = :value-from-record#2
     WHERE C1 = :value-from-record#1;

The following request will NOT use a row-hash lock (it will use a table-level lock):

     UPDATE T1
       SET C2 = T2.C2
     WHERE C1 = T2.C1;

This is clearly shown in an Explain plan.


Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)



     
  <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: 23 Jun 2019