cedric -- Thanks for the question regarding "Update base on join view fails with ora 1779", version 9.2.0.8

Submitted on 7-Apr-2008 12:52 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 6-Sep-2011 8:51

You Asked

Hi,

Here is the problem

update (select t1.* from T1,T2 where t1.col=T2.col) t
set col4='toto'

ora-1779

even if i try /*+bypass_ujvc*/ in my update

update /*+bypass_ujvc*/ (select t1.* from T1,T2 where t1.col=T2.col) t
set col4='toto'

I try exactly the same exemple i found on asktom, so is ti a bug from 9.2.0.8 ???

is there another soutionto bypass key preserved ?

Thks a lot,

Regards,

Cédric

Tom, here is an exemple that issue on my environnement (AIX 5.3 Oracle 9.2.0.8)

create table T1 (col1 int, col2 int);
insert into T1 values (1,1);
insert into T1 values (2,1);
insert into T1 values (3,1);

create table T2 (col1 int, col2 int);
insert into T1 values (1,2);
insert into T1 values (2,2);
insert into T1 values (3,2);

update /*+ bypass_ujvc */ (select t1.col2 t1col2,t2.col2 T2col2 from T1,T2 where t1.col1=T2.col1) t
set t1col2=t2col2

issue with ORA-01779

Thanks a lot

regards

and we said...

ops$tkyte%ORA10GR2> select * from t1;

      COL1       COL2
---------- ----------
         1          1
         2          1
         3          1

ops$tkyte%ORA10GR2> merge into t1
  2  using (select t1.rowid rid, t2.col2 t2_col2
  3           from t1, t2
  4          where t1.col1 = t2.col1) x
  5  on (t1.rowid = x.rid)
  6  when matched then update set col2 = t2_col2
  7  when not matched /* never happens */ then insert (col1) values (null);

3 rows merged.

ops$tkyte%ORA10GR2> select * from t1;

      COL1       COL2
---------- ----------
         1          2
         2          2
         3          2

Note: simply having the correct primary key in place (on that must be true - has to be true for the update to work, for the merge to work) you can do this simply:

ops$tkyte%ORA10GR2> alter table t2 add constraint t2_pk primary key(col1);

Table altered.

ops$tkyte%ORA10GR2> update (select t1.col2 t1_col2, t2.col2 t2_col2 from t1, t2 where 
t1.col1=t2.col1)
  2  set t1_col2 = t2_col2;

3 rows updated.

that would be the best approach of course.

Reviews

Merge vs. views updte April 8, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: cedric rollo from FRANCE

Tom,

Thanks a lot for this other manner to do the job.

I use to do that but the response time is 6 time less efficient in our environnement.

I found a solution to bypass this issue : 

declare
  sqlstr varchar2(4000);
begin
  sqlstr:='update /*+ bypass_ujvc */ ...'
  execute immediate sqlstr;
end;
/

So in that case the HINT is understood by oracle core.

But i think using this hint is not a good idea due to bad-know of fonctionnal environnement.

Thanks a lot,

Regard

Followup April 9, 2008 - 2pm Central time zone:

using this hint is way more than "not a good idea"

it is what I would call irresponsible.

The fix: create the UNIQUE CONSTRAINT THAT MUST DE-FACTO EXIST.

that, in addition to merge, is the only responsible thing to do

Key preserved table April 9, 2008 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Vikas from London

One innocent question : if there are workarounds available for the limitation imposed by key 
preserved table when why Oracle itself has imposed this limitaion. It is only matter of reading 
this page( May be some of the gurus were already using this trick).

Followup April 9, 2008 - 2pm Central time zone:

we need to know the table is key preserved in order to ascertain that the update is deterministic - that is, it returns the same answer given the same inputs.

if the join column in the lookup table IS NOT UNIQUE - it is not deterministic.

so the responsible answer: assert the constraint to the database, and then it just works.

Key preserve April 10, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: cedric rollo

Another issue is when §you have the PK un the join but when the column name is not the same. So in 
that case the hint is very usefull :

create table t1 (col1 int primary key,col2 int);
create table t2 (id int primary key,col2 int);

update (select t1.col2, t2.col2 from t1,T2 where t1.col1=T2.id)
set t1.col2=t2.col2;

in that case : ora-1779 but you are using the PK, so the update is deterministic.

You case use the /*+ bypass_ujvc */ hint without any danger

regards,

Cédric

Followup April 10, 2008 - 11am Central time zone:

no clue what you mean here, the column names are not relevant, never been relevant, never will be relevent.

ops$tkyte%ORA10GR2> create table t1 (col1 int primary key,col2 int);

Table created.

ops$tkyte%ORA10GR2> create table t2 (id int primary key,col2 int);

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update (select t1.col2 t1_c2, t2.col2 t2_c2 from t1,T2 where t1.col1=T2.id)
  2  set t1_c2 = t2_c2
  3  /

0 rows updated.

you do NOT need any hints, you just need to be joining on a primary key or a unique constraint set of columns - no one cares what their names are.

You case use the /*+ bypass_ujvc */ hint without any danger

sorry, but unless and until you actually write the code behind that hint, you cannot say that.

the hint doesn't work in 9.2.0.8 April 10, 2008 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: Milo Tan

I agree with Tom that using the bypass_ujvc hint is not a good idea.  I found that it may or may 
not work depending on the  database release version.

The hint fails in 9.2.0.8 is because of a bug.  I actually used the hint in 9.2.0.5 and 9.2.0.7 
with no problem.  
The hint also works in 10.2.0.3.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


Session altered.

SQL> create table T1 (col1 int, col2 int);

Table created.

SQL> 
SQL> insert into T1 values (1,1);

1 row created.

SQL> insert into T1 values (2,1);

1 row created.

SQL> insert into T1 values (3,1);

1 row created.

SQL> 
SQL> 
SQL> create table T2 (col1 int, col2 int);

Table created.

SQL> 
SQL> insert into T2 values (1,2);

1 row created.

SQL> insert into T2 values (2,2);

1 row created.

SQL> insert into T2 values (3,2);

1 row created.

SQL> 
SQL> update /*+ bypass_ujvc */ (select t1.col2 t1col2,t2.col2 T2col2 from T1,T2 where 
t1.col1=T2.col
1) t
  2  set t1col2=t2col2;

3 rows updated.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          2
         2          2
         3          2

The best approach as suggested by Tom is to add a primary/unique constraint.

Followup April 10, 2008 - 8pm Central time zone:

funny, how can there be a bug against something that technically doesn't exist...

You cannot never count on

a) the behavior (because it just isn't documented)
b) the side effects (because you don't know what it does or how it does it or how it might do it in the future)

it is not responsible to use this.

Don't mean to quibble April 10, 2008 - 11pm Central time zone

Bookmark | Bottom | Top

Reviewer: Milo Tan

But it was reported as a bug in Metalink, and reported there as being fixed for bug: 5407528.  :-)

And when I said that I used it successfully in 9.2.0.5 and 9.2.0.7, I really meant to say I tested 
the hint sucessfully in those Releases.

Tom, I agree with you completely that it is not a good idea to use the hint in a production system.

Followup April 11, 2008 - 7am Central time zone:

but you see, in metalink "everything is a bug"

I was speaking conceptually here - you cannot really expect to file or find a bug fix in general for something that does not exist. If you ever run into a problem with this "feature that is not a feature", you cannot expect any help really.

So the fact that there is a 'bug' (an enhancement request is..... a bug - or is it? well, it is logged as "a bug", but it isn't a bug - it is an enhancement request...) doesn't mean you or I could come along and expect to file a bug against it in the future.

Still the /*+ bypass_ujvc */ hint is very useful to me. February 1, 2010 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jo Desmet from Pleasanton, CA

I can understand all the opposition against the use of the /*+ bypass_ujvc */ hint.
However, too many times I come to the scenario where I have two near-identical tables Where all keys except one are the same. The reason being is that Table B is an aggregate of Table A (and thus has typically one key less as an aggregate).
There are also a lot of cases where we know the extra key to be nothing but a constant. By defining this in the Where condition, Oracle does not recognize this. Example:

CREATE TABLE table1 (
  col int,
  pk_col int,
  CONSTRAINT table1_pk PRIMARY KEY (pk_col)
);
CREATE TABLE table1_snapshot (
  col int,
  pk_col int,
  snapshot_date DATE,
  CONSTRAINT table1_snapshot_pk PRIMARY KEY (pk_col,snapshot_date)
);
UPDATE (
  SELECT
    o.col AS old_col,
    n.col AS new_col
  FROM
    table1 o,
    table1_snapshot n
  WHERE
    o.pk_col = n.pk_col
    AND n.snapshot_date = TRUNC(SYSDATE)-2
) SET
  old_col = new_col;

In above example, table1 has one Column for the Primary Key (pk_col), snapshot_table1 has two Columns for the Primary Key (pk_col, and snapshot_date).
To a human it is clear that the the conditions for the update to work are fulfilled, but somehow Oracle does not recognize this.

Followup February 2, 2010 - 11am Central time zone:

but you *do not need it*

MERGE will do it without the undocumented - unknown to you how it works - hint.

and merge will do it safely - if you do have duplicate keys, the merge statement will TELL YOU. The update would not.

DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA
DO NOT USE THIS UNDOCUMENTED HINT, YOU WILL BE ABLE TO CORRUPT YOUR DATA

I don't care if your single example won't, I care that someone coming along later that reads your code will assume "this is a good idea" and use it as well and corrupt data. JUST DON'T DO IT, it isn't even necessary, you have a documented, real, correct way to do it.

Hopefully, I've been very very clear.

ops$tkyte%ORA10GR2> UPDATE (
  2    SELECT
  3      o.col AS old_col,
  4      n.col AS new_col
  5    FROM
  6      table1 o,
  7      table1_snapshot n
  8    WHERE
  9      o.pk_col = n.pk_col
 10      AND n.snapshot_date = TRUNC(SYSDATE)-2
 11  ) SET
 12    old_col = new_col;
  old_col = new_col
  *
ERROR at line 12:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into table1 t1
  2  using (select * from table1_snapshot where snapshot_date = trunc(sysdate)-2) t2
  3  on( t1.pk_col = t2.pk_col)
  4  when matched then update set t1.col = t2.col;

0 rows merged.

why the bypass hint did not work September 5, 2011 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Ved from India

In the original post.. Cedric tried using the /*+bypass_ujvc*/ in the update..

My question is why the hint didn't work..

Followup September 6, 2011 - 8am Central time zone:

I don't care why it did not work - it is not documented, it is not supported, we (you and i) do not know all of the rules for using it - it is nothing for us to use.

there is never a reason to use it anymore, not with MERGE being available.

Write a Review