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