Specifying 'Locking row for Access' in a View of two views

General
Enthusiast

Specifying 'Locking row for Access' in a View of two views

Hi

Would like your thoughts on what will actually happen if I specify the clause 'Locking Row for Access' in a VIEW which joins two more VIEWs (which have 'locking row for access' specified in their DDL) and these later two VIEWs are one-to-one copy of underlying tables.Something like this:

View AV is on two more views - EV1 and Ev2 and 

AV --> create view AV (ev1.col1 , ev2.col2)  locking row for access select ev1.col1, ev2.col2 from EV1, EV2 where <joining condition>

EV1 --> create view EV1 (col1, col2, col3) locking row for access select col1, col2, col3 .... from T1) 

EV2 --> same definition like EV1 on table T2

An answer is well appreciated.

Thanks.

2 REPLIES
Senior Supporter

Re: Specifying 'Locking row for Access' in a View of two views

did you try an explain for the selects?

It is stating which actual locks are set for the select.

Is the join a PI join?

Ulrich

Enthusiast

Re: Specifying 'Locking row for Access' in a View of two views

Yes Ulrich. i have comapared two explain plans. And they state the same access path. I have used the following DDLs to simulate the problem. Now please let me know is there any other concerns that you can think of ?

create table test_tab1(id decimal(18,0), name varchar(10))

 

create table test_tab2(id decimal(18,0), name varchar(10))

 

REPLACE VIEW test_ev1 (id, name) as  LOCKING ROW  ACCESS select id , name from test_tab1

 

REPLACE VIEW test_ev2 (id, name) as  LOCKING ROW  ACCESS select id , name from test_tab2

 

REPLACE VIEW test_av_withaccess (name1, name2) as  LOCKING ROW  ACCESS select  ev1.nameev2.name from   test_ev1 ev1, test_ev2 ev2 where ev1.id = ev2.id

 

REPLACE VIEW test_av_noaccess (name1, name2) as  select  ev1.nameev2.name from   test_ev1 ev1, test_ev2 ev2 where ev1.id = ev2.id

 

Thanks.