All Forums General
TD_lover 8 posts Joined 06/13
03 Oct 2013
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.

ulrich 816 posts Joined 09/09
03 Oct 2013

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

TD_lover 8 posts Joined 06/13
06 Oct 2013

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.

You must sign in to leave a comment.