All AMP active for Updates based on primary index

Database
Enthusiast

All AMP active for Updates based on primary index

Hi All,

I am updating my tables based on primary index, however from the DBQL I see all the amps are active. Could someone explain, if this is expected ?

Cannot post the table structre for confidentialy reasons.

But it's like:

MyTable

(Column1 CHAR(10)

,Column2 CHAR(5)

,Column3 INTEGER

....

) PRIMARY_INDEX(Column1 , Column2);

Query:

UPDATE MyTable

Set Column3 = ? , Column = ? ....

Where Column1 = ? AND Column2 = ?

The table has less than 100 rows and stats are collected over primary index.

I was expecting this to be 1 AMP operation.

28 REPLIES
Enthusiast

Re: All AMP active for Updates based on primary index

There may be rows on every amp that are getting updated. 

Enthusiast

Re: All AMP active for Updates based on primary index

How can rows be on every amp ? I have the primary index in where clause. Here is the explain plan:

The explain plan shows Single Amp Update.

Enthusiast

Re: All AMP active for Updates based on primary index

Ahemmad,

The registers will be storaged at the AMP = hash(Column1 , Column2)

Your query will update one or more registers (if you define the table as Multiset),

but allways in the same AMP.

Regards.

Teradata Employee

Re: All AMP active for Updates based on primary index

- how are you determining that all amps are active for that single update?

- are you running multiple updates concurrently - against the same or multiple tables?

Enthusiast

Re: All AMP active for Updates based on primary index

Thanks Dixxie and Todd.

I am running only single update at a time.

My table is defined as MultiSet - I don't see why the behaviour would be different.

I found out the number of Amps active from DBQL field  NumOfActiveAMPs.

When I run explain on the update statement, it shows only 1 AMP operation. But not sure why the DBQL log shows otherwise.

Since the update is going to be against huge table in live environment, I am worried for the performance issue that may arise for this.

Junior Contributor

Re: All AMP active for Updates based on primary index

The only case I can imagine if it's actually a single update using the PI is a LOCKING TABLE.

Do you run the update against a view with?

Then switching to LOCKING ROW shoul help.

Enthusiast

Re: All AMP active for Updates based on primary index

hi dnoeth,

I run update against view and the view is defined as locking row for access. Surprisingly, the same query in production results 1 Active Amp (as it should be), whereas in UAT its all AMP. 

Enthusiast

Re: All AMP active for Updates based on primary index

I know this thread is really old but thought I would hop on the end and see if anyone would respond.  I too captured a table lock in viewpoint whos SQL was trying to do an update using an in list of NUPIs  (the field being updated was not the NUPI).  This table lock caused all kinds of blocking issues in our OLTP system.  When I pulled the SQL and ran an explain, sure enough it showed a series of single amp updates as one would expect... very similar to what this gentlemen experienced.  Has anyone ever experienced a table lock when doing an update using a list of NUPIs?  If so, could you share the circumstances?

 

Joe

Teradata Employee

Re: All AMP active for Updates based on primary index

What did the explain say about the lock (at the beginning of the explain)?

 

When multiple rows on multiiple amps are being updated, then a table lock will be issued. It does not matter that individual steps will be issued for each update, there will still be a table lock at the beginning. At this time, Teradata does not have a locking mode that locks a list of PI hashes.