VIEW CREATION QUERY

Database

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

4 REPLIES

Re: VIEW CREATION QUERY

Hi ,

Any suggestions??

Regards

John

Re: VIEW CREATION QUERY

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.

Re: VIEW CREATION QUERY

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?

Teradata Employee

Re: VIEW CREATION QUERY

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