If you can attach the explain, it might be helpful. Also, check if you have any Soft Referential integrity constraints on the objects invovled.
this is the first time i am posting my question in Teradata Forum, Please for give my ignorance if it is incorrect site.
I have to create a view , I would like to know which one is better
AS LOCKING ROW IN ACCESS or
LOCKING TABLE tablename FOR ACCESS, I read documentation but not giving fair idea.
I have the same interrogation: LOCKING ROW FOR ACCESS is said to be the best ... but why ?
Doc is not so much clear about that:
"Locking an entire base table across all AMPS is undesirable, and the use of LOCKING ROW here prevents the need to lock an entire base table across all AMPs."
So LOCKING TABLE is costly and inappropriate ?
But you can find this :
"The LOCKING ROW modifier cannot be used to lock multiple row hashes. If LOCKING ROW FOR ACCESS is specified with multiple row hashes, the declaration implicitly converts to LOCKING TABLE FOR ACCESS."
The conversion from "Row" to "table" should be costly too, when needed.
So it depends ...
Thanks for comments !
lock row for access has the benefit that the DB detects which need to be done.
If you access only one PI value it will place a row lock for access.
If you access the whole table it will place a table lock for access.
Where if you specify a lock table for access it will be always a table lock even if you select only one PI.
So you have the possibility of an upside if you use lock row for access without an penalty.