All Forums Database
johnsunnydew 43 posts Joined 09/14
22 May 2015
VIEW CREATION QUERY

Hi there,
 
I have a question regarding view. I have created 2 views as follows.
 

 

REPLACE VIEW VWD.PRODUCT
AS LOCKING ROW FOR ACCESS
SELECT *FROM DDD.PRODUCT;
------------------------------------------------------ ------------------------------------------------------ --
REPLACE VIEW VDDVD.PRODUCT
AS
LOCKING ROW FOR ACCESS
SELECT *FROM VWD.PRODUCT
 
 

Question is, do i require LOCKING ROW FOR ACCESS in the second view created?
First view is VWD.PRODUCT referring to the table DDD.PRODUCT;
Second view VDDVD.PRODUCT referring to the view created above VWD.PRODUCT.
 
Please suggest.
 
Thanks
John
 

johnsunnydew 43 posts Joined 09/14
23 May 2015

Hi ,
Any suggestions??
 
Regards
John

Anilnandala 16 posts Joined 03/14
23 May 2015

LOCKING ROW FOR ACCESS is not required for the second table.
if you can check the explain for SEL * FROM VDDVD.PRODUCT  you can observe that access lock is placed on the table hence another locking statement is not required.

 1) First, we lock DDD.PRODUCT in view VDDVD.PRODUCT for
     access. 

 

 

johnsunnydew 43 posts Joined 09/14
24 May 2015

Anil Thanks.
So u say the below is enough to create the view, without Locking row for acess?
REPLACE VIEW VDDVD.PRODUCT
AS
SELECT *FROM VWD.PRODUCT
Any performnace or other issues if i place this locking row for access here?
 
 

SmarakDas 51 posts Joined 02/12
26 May 2015

Hello John,

 

The Resolver of the Parsing Engine resolves all the statements to their underlying tables. If you create a view from a view, the Resolver will resolves the same to its underlying tables & apply locks likewise. There will be no performance impact with providing the "Lock Row For Access", unless you provide "Locking Row for Read" @  VDDVD.PRODUCT DDL, in which case a Read Lock will be applied @ table "DDD.PRODUCT". As long as you specify nothing or "Lock Row For Access", there shouldn't be any impact.

 

Thanks,

Smarak

You must sign in to leave a comment.