Archives of the TeradataForum

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

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

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>>