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:
) PRIMARY_INDEX(Column1 , Column2);
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.
How can rows be on every amp ? I have the primary index in where clause. Here is the explain plan:
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.
- 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?
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.
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.
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.
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?
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.