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.

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