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.