Archives of the TeradataForum
Message Posted: Tue, 29 Jan 2008 @ 16:43:59 GMT
In response to this question....
First, there is no measurable performance hit on the all-AMP queries by setting a row level access lock and having it converted to an all AMP access lock.
And even if access locking has been requested at the row level, if the plan requires an all-AMP operation, the requested row-hash lock will be converted automatically by the optimizer to a table-level lock. Always spot check the explains to verify that row or, when appropriate, table- level access locks are being issued for those queries.
Below is the plan for a query requiring all AMPs. The plan shows the table-level access lock being applied, even though the SQL requests a row-hash-level access lock. The optimizer escalates the access lock to the table level, even though table-level access locking was not requested.
EXPLAIN LOCKING ROW FOR ACCESS SELECT c_name, c_acctbal FROM customer WHERE c_nationkey = 15;
As of V2R6 the optimizer will escalate the row hash level access lock appropriately. Prior to that release, there were some exceptions.
There is only one exception still remaining that I am aware of. That is the case where you have a macro or a multi-statement-request with mixed row-level modifiers (some access, some read). If the row level access lock statement is not last in the MSR or macro and the final statement takes the default read lock modifier, then the row level access lock will be converted to a row level read lock.
So if you use row level access locking in your views (which is recommended when you have a mix of single and all AMP queries using the view), make sure they are consistently applied across all views, rather than just a subset of views.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|