Skip to Main Content
  • Questions
  • Update base on join view fails with ora 1779

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, cedric.

Asked: April 07, 2008 - 12:52 pm UTC

Last updated: July 31, 2012 - 12:40 pm UTC

Version: 9.2.0.8

Viewed 10K+ times! This question is

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 Tom 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.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Merge vs. views updte

cedric rollo, April 08, 2008 - 11:29 am UTC

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
Tom Kyte
April 09, 2008 - 2:37 pm UTC

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

Vikas, April 09, 2008 - 11:06 am UTC

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).
Tom Kyte
April 09, 2008 - 2:58 pm UTC

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

cedric rollo, April 10, 2008 - 10:26 am UTC

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
Tom Kyte
April 10, 2008 - 11:32 am UTC

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

Milo Tan, April 10, 2008 - 4:59 pm UTC

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.

Tom Kyte
April 10, 2008 - 8:33 pm UTC

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

Milo Tan, April 10, 2008 - 11:44 pm UTC

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.
Tom Kyte
April 11, 2008 - 7:45 am UTC

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.

Jo Desmet, February 01, 2010 - 5:24 pm UTC

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.
Tom Kyte
February 02, 2010 - 11:04 am UTC

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
<b>

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.
</b>

why the bypass hint did not work

Ved, September 05, 2011 - 10:09 am UTC

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

My question is why the hint didn't work..
Tom Kyte
September 06, 2011 - 8:51 am UTC

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.

Two years wiser

Jo Desmet, July 31, 2012 - 4:42 am UTC

Well, just wanted to say that I took your warnings to my hart. The MERGE works of course splendid - no undocumented hints - no surprises. After being two years wiser, I am using in code reviews the MERGE to replace a lot of inefficient BULK COLLECT/UPDATE, or worse, regular cursor loops with updates.

I think it is worth to mention that most cases where Bulk Updates are being used, it most likely deserves the consideration of a MERGE - at least from those cases I have seen. Not only does your code look simpler and more readable, it usually ends up running much faster too ... by a lot of difference.

It is however surprising how persistently people stick to their old Loops and Bulk Collects to manage their Updates. Even things that can be done with a simple INSERT INTO/SELECT.
Tom Kyte
July 31, 2012 - 12:40 pm UTC

thanks for the followup!

No merge with INSTEAD OF trigger

davidp, June 03, 2013 - 9:28 am UTC

MERGE is wonderful for updates like this. Sadly I recently found that MERGE does not work with views with INSTEAD OF triggers, and Oracle Workspace Manager is built on views with INSTEAD OF triggers. It's very sad.

merge and duplicates

Attanasio, January 30, 2014 - 9:04 am UTC

Hello Tom,

you have mentioned 'if you do have duplicate keys, the merge statement will TELL YOU'.

I've had a case in 11.2 version where depending on the order data comes out from the USING clause, the execution was not giving the expected error but was permitting the duplicates chosing the second value.

If I have understood well from the documentation it is an expected behaviour as soon as the update of a row happens only once.

Is that correct ?

Kind regards,

Atta

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions