Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Zeljko.

Asked: April 17, 2006 - 10:32 am UTC

Last updated: October 18, 2017 - 3:38 am UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

consider this:

create table test (
id number(10),
username varchar2(100),
reason varchar2(100),
timestamp_ number(10),
cnt number(10),
CONSTRAINT test_pk primary key (id)

);

insert into test(id,username,reason,timestamp_,cnt) values (1,'aca','bad pass.',500,0);
insert into test(id,username,reason,timestamp_,cnt) values (2,'beca','bad pass.',1000,0);
insert into test(id,username,reason,timestamp_,cnt) values (3,'aca','bad pass.',1010,1);
insert into test(id,username,reason,timestamp_,cnt) values (4,'aca','unknown',1020,0);


Can I do this pseudo code in one sql statement:

select max(id) into l_id from test where username=:username and timestamp>:timestamp-:fixed_delay;

--if found update else insert
if (l_id is not null) then update test set cnt=cnt+1 where id=l_id
else
insert into test (id,username,reason,timestamp_,cnt) values (:id,:username,:reason,:timestamp_,0)
end if

if input data is :
:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =60

it should update record id=3,

:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =600

it should update record id=3,

:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =10 it should insert new record.

so, if there is a record with same username and reason in last <fixed_delay> seconds then update last record else insert new_one. We can not relay on fact that there will be just one record for update in given period. So we have to be exact and update only last record that match criteria. Timestamps in real examples are unix timestamp, in this example I used smaller numbers (500-1020) for clarity.

It is easy to write stored procedure for this but is there a way to use merge statement.



and we said...

well, it should NEVER be three sql statements. It should have just been:

update
if (sql%rowcount = 0)
then
insert
end if;

I hate to see people "look to see if there is data to update, if so, update it - else insert"

It should always be "let the update see if there is data to update, if not, insert it" at most.

that and the logic just doesn't work in a multi-user environment (that max(id) junk, ugh - man oh man). Also, :id in the insert would be NULL - no logic for that (assume you must have meant to use sequence.nextval)


Your record id=3, I think you meant id=4 given the above data:

p_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);

1 row merged.

ops$tkyte@ORA10GR2> select * from test;

ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 1

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :username := 'aca';:reason :='bad pass.';:timestamp_:=1030;:fixed_delay :=600 ;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> merge into test
2 using ( select max(id) id
3 from test
4 where username = :username
5 and timestamp_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);

1 row merged.

ops$tkyte@ORA10GR2> select * from test;

ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 2

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :username := 'aca';:reason :='bad pass.';:timestamp_:=1030;:fixed_delay :=10 ;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> merge into test
2 using ( select max(id) id
3 from test
4 where username = :username
5 and timestamp_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);

1 row merged.

ops$tkyte@ORA10GR2> select * from test;

ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 2
12 aca bad pass. 1030 0


Rating

  (17 ratings)

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

Comments

merge

raaj, April 18, 2006 - 2:02 am UTC

Hi,Tom
thanks a lot for the reply it's really great and helpful to ALL.


A reader, April 18, 2006 - 3:11 am UTC

Thanks Tom,

I said that it is just pseudo code to explain what we want with that statement, in the meanwhile I wrote stored procedure with update and insert if no data updated. :id is populate via sequence, it is not relevant to this example so I left it out.
id should be 3 (in original example) because we match username and reason, but there is no difference in sql logic. So our statement is

merge into test
using ( select max(id) id
from test
where username = :username and reason=:reason
and timestamp_ > :timestamp_-:fixed_delay ) t2
on (t2.id = test.id)
when matched then update set cnt = cnt+1
when not matched then insert(id,username,reason,timestamp_,cnt)
values(seq.nextval,:username,:reason,:timestamp_,0);

In our case it is not crucial to avoid some multiuser abnormalities, but it is interesting to elaborate what in case that two sessions go to the insert part of merge in the same moment with same data, there will be 2 row inserted instead one inserted and one updated.
If we want to have 1 record not two in this case we have to use some lock to serialize access? Would you recommend dbms_lock for that case?

And another question :
how merge statement works with write consistency, what is happening if the rows are changed and do not match conditions in "using part" any more?


Tom Kyte
April 18, 2006 - 9:38 am UTC

psuedo code is supposed to accurately and clearly depict the true requirements. That psuedo code apparently does not do that....

it was ENTIRELY relevant to the example, what if using the sequence in that place didn't work? what then??? (time wasted is the answer).

"is not crucial to avoid some multiuser abnormalities" - very very shortsighted of you. you never know what'll happen in the future, it is always our job to think about this stuff.

Your "it is interesting part" is interesting - so what'll happen to your application in that case - hmmm, maybe it is crucial to think about these things?

the update component of the merge follows all of the rules of write consistency.

I hate when I forget to enter my name and email

Zeljko Vracaric, April 18, 2006 - 3:14 am UTC

:(

interesting part

Zeljko Vracaric, April 18, 2006 - 10:15 am UTC

<quote>
Your "it is interesting part" is interesting - so what'll happen to your
application in that case - hmmm, maybe it is crucial to think about these
things?
</quote>
it is not crucial to us, because we plan to use merge instead of just inserts, to log login failures from dsl users. Some dsl equipment do automatic retries and fill database with not useful data. So by aggregating some of bad logins (if it is same user and same reason in some interval) we keep out tables smaller. Are there two inserts or insert and update is not worth introducing more serializations in this case. I asked for opinion how to do similar thing where it is important to have insert and update instead of two inserts.


Tom Kyte
April 18, 2006 - 10:20 am UTC

you would have to serialize at the user level - since you want to either

a) update existing
b) insert new if no existing


by username. If two records came in at the same time - neither would see an "existing record", they would both insert - leading to a situation you did not anticipate, nor do you desire.

So, you would likely goto the PARENT table (missing from this example) and select for update that users record - THEN do the modification of this child table.

almost agreed :)

Zeljko, April 18, 2006 - 10:30 am UTC

it is possible if username is correct but if user enter wrong username there is no parent record.

Tom Kyte
April 18, 2006 - 2:04 pm UTC

sounds like you are still missing the parent table...

but you could figure out other ways to serialize on the username I am sure (I can think of at least one more - dbms_lock..)

agreed

Zeljko, April 19, 2006 - 2:15 am UTC

<quote>
If we want to have 1 record not two in this case we have to use some lock to
serialize access? Would you recommend dbms_lock for that case?
</quote>

thanks tom for your time and chance to ask question.

merge and write consistency

Andrey N. Edemsky, May 04, 2006 - 3:51 pm UTC

Hi Tom! You wrote:
"the update component of the merge follows all of the rules of write consistency. "
But look at this:

-- session1
SQL> create table ane_test as (select rownum m, 10+rownum n from dual connect by level < 10)
/

Table created

SQL> update ane_test set m=m+n where m in(2,7);

2 rows updated

SQL> select * from ane_test;

         M          N
---------- ----------
         1         11
        14         12
         3         13
         4         14
         5         15
         6         16
        24         17
         8         18
         9         19

9 rows selected

SQL> 

 
-- session2
SQL> merge into ane_test
using (select 2 n from dual union all select 7 n from dual union all select 8 n from dual) t2
on (ane_test.m=t2.n)
when matched then update set ane_test.n=(-1)*ane_test.n
when not matched then insert values(0,0);
-- session hungs 

--session3
SQL> update ane_test set m=m+N where m=8;
1 row updated

SQL> commit;
Commit complete

--session1
SQL> commit;

Commit complete


--session2, continues:
SQL> merge into ane_test
using (select 2 n from dual union all select 7 n from dual union all select 8 n from dual) t2
on (ane_test.m=t2.n)
when matched then update set ane_test.n=(-1)*ane_test.n
when not matched then insert values(0,0);

Done

SQL> select * from ane_test;

         M          N
---------- ----------
         1         11
        14        -12
         3         13
         4         14
         5         15
         6         16
        24        -17
==>     26        -18  -- WOW! It can be "26, 18" or "-10, -18" not "26, -18"...
         9         19

9 rows selected
 
I've try at 9.2 and 10.1

It happens because of no statement restart in case of attributes listed in ON() clause changes...
With best regards, Andrey.
P.S.: Sorry for my english. 

Tom Kyte
May 09, 2006 - 10:36 am UTC

I concurr, will file a bug.

Multiple instance of merge is slow

Tony, March 11, 2008 - 11:15 pm UTC

I have a procedure having merge statment (10g). If I run multiple instance, the process takes more time than without merge. Is there any locking issue in merge?

Tom Kyte
March 12, 2008 - 5:20 pm UTC

if i run multiple instance - no idea what you mean there?

how would you do it without merge?

(any unindexed foreign keys by any chance - are you merging into a table with child tables that point to it and they are without an index on their foreign key?)

Filed bug

Igor, April 21, 2009 - 8:21 am UTC

Hi Tom,

What was result/conclusion of filed bug ?

I got same result in 10.2.0.2 and 11.1.0.7...

Thank you and regards


Tom Kyte
April 21, 2009 - 3:24 pm UTC

there is an in progress bug

Hdr: 5214824
Abstract: READ CONSISTENCY NOT WORKING CORRECTLY ON MERGE STATEMENT

that I filed on this

Thank you

Igor, April 22, 2009 - 4:05 am UTC


Filed Bug Update

An Oracle Developer, August 02, 2011 - 10:55 am UTC

Hi Tom,

Any update on the filed bug?

Hdr: 5214824
Abstract: READ CONSISTENCY NOT WORKING CORRECTLY ON MERGE STATEMENT
Tom Kyte
August 02, 2011 - 11:49 am UTC

no, I'll track this one myself now.

but thanks for the followup... Here is a little back story that goes with this one.

About two weeks ago, before I left on travel, I made a mental note to myself to followup on a different bug when I got back. So, on my calendar, I put the bug number in as the subject - for yesterday.

So, I get back from travel and sit down and look at the calendar yesterday. And there is that number. But - it is just a number, I didn't put any context around it.

And I had totally forgotten what that number was - why it was there.

Until just now - by you giving me this bug number - it made me ask "hmmm, I wonder if that mystery number on my calendar is a bug #"

And it was, as soon as I saw the subject of the bug - I remembered what it was all about.


So, note to self, never just put a number on my calendar - bug "bug #xxxxx" ;)


still a bug in 11.2.0.2

Chris, August 09, 2011 - 9:23 am UTC

The same behaviour occurs in 11.2.0.2.
But I guess this is slightly unusual in that the "key" column used by the merge in session 2 (column "M") isn't immutable and is changed by the update in session 3.

Tom Kyte
August 13, 2011 - 4:06 pm UTC

I've gotten this promoted to the top of the stack, it is being worked on now

Is this known bug?

GPU, August 20, 2013 - 6:40 pm UTC

Hello Tom,

Merge is behaving strangely and it is not reliable to use.

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


Scenario 1: Not expecting to see a record in t1 (1=2 condition in subquery), but got different results

drop table t1;

Create table t1 (col1 varchar2(1) not null);


MERGE INTO t1
USING (SELECT dummy
FROM DUAL
WHERE 1 = 2) s
ON (1 = 2)
WHEN NOT MATCHED
THEN
INSERT (col1)
VALUES (s.dummy);

=> 1 row merged

select * from t1;

col1
----
X


commit;

Scenario 2: I am not expecting Oracle error but Oracle thrown an exception(In this case not null constraint violation)

drop table t2;


Create table t2 (col1 varchar2(1));

insert into t2 values ('X');
insert into t2 values ('Y');
insert into t2 values ('Z');

commit;

With distinct keyword, it works fine.

MERGE INTO t1
USING (SELECT distinct col1
FROM t2
WHERE 1 = 2) s
ON (t1.col1 = s.col1)
WHEN NOT MATCHED
THEN
INSERT (col1)
VALUES (s.col1);

=> 0 rows merged

with max() function, it is throwing ORA-01400: cannot insert NULL into ("USER"."T1"."COL1")

MERGE INTO t1
USING (SELECT max(col1) as col1
FROM t2
WHERE 1 = 2) s
ON (t1.col1 = s.col1)
WHEN NOT MATCHED
THEN
INSERT (col1)
VALUES (s.col1);

=> ORA-01400: cannot insert NULL into ("USER"."T1"."COL1")


Is this known bug? if yes, got fixed in 12c?

Please advise

Thanks,
GPU
Tom Kyte
August 28, 2013 - 5:35 pm UTC

this is not a bug.


an aggregate query with no group by BY DEFINITION returns

a) at least one row
b) at most one row


select distinct col1 from t2 where 1=2 - returns zero rows

select max(col1) form t2 where 1=2 - returns one row


this is the way sql works.


ops$tkyte%ORA11GR2> select count(*), max(dummy), min('hello') from dual where 1=0;

  COUNT(*) M MIN('
---------- - -----
         0

ops$tkyte%ORA11GR2>



without a group by - min and max will return NULL if there are no records to be found.


Same guy - Correction

GPU, August 26, 2013 - 5:11 pm UTC

Please ignore Scenario 2 it is my mistake group function will return exactly one row even if there is no record in the table, so error is valid.

It seems scenario 1 is bug in 11.2.0.2.0 and got fixed in 11.2.0.3.0

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


Scenario 1: Not expecting to see a record in t1 (1=2 condition in subquery), but got different
results

drop table t1;

Create table t1 (col1 varchar2(1) not null);


MERGE INTO t1
USING (SELECT dummy
FROM DUAL
WHERE 1 = 2) s
ON (1 = 2)
WHEN NOT MATCHED
THEN
INSERT (col1)
VALUES (s.dummy);

=> 1 row merged

select * from t1;

col1
----
X


commit;


merge with db-link

mila, October 18, 2013 - 4:21 am UTC

Hi, Tom.

I use table and view

-on source and destination base:
create table TEST_PHONE
(
id_phone INTEGER not null,
num_p CHAR(11)
);

-on source base
create view test_phone_2 as
select id_phone, num_p, ora_rowscn dbscn
from TEST_PHONE a;

When I run command

merge into TEST_PHONE@DST a
using (
select t.id_phone, t.num_p from test_phone_2 t where t.dbscn>0) b
on (a.id_phone = b.id_phone)
when matched then update set
a.num_p = b.num_p
when not matched then
insert (id_phone, num_p)
values (b.id_phone, b.num_p)

ORA-00904: : invalid identifier
ORA-02063: preceding line from DST

If I delete "where t.dbscn >0" or change on "where t.id_phone >0" - it work.
If I run command on destination base

merge into TEST_PHONE a
using (
select t.id_phone, t.num_p from test_phone@src t where t.dbscn >0) b
on (a.id_phone = b.id_phone)
when matched then update set
a.num_p = b.num_p
when not matched then
insert (id_phone, num_p)
values (b.id_phone, b.num_p);
It work too.

Why don't it work with "ora_rowscn dbscn"?
What can I do thah first command work?

'delete' inside merge

A reader, November 20, 2013 - 7:37 am UTC

Hi Tom,

I have a question about the 'delete' function of merge statement.

By a trigger, my test showed that both 'update' and 'delete' were invokded.

My question is as eventually that role will be deleted, why will do the update firstly?

I guess 'delete' is almost happened after 'update', there should not be any case to rollback to the time between 'update' and 'delete', why do both of them with generating more redo and undo?

Old Merge "write consistency" bug

Stew Ashton, September 27, 2017 - 5:20 pm UTC

"Hdr: 5214824
Abstract: READ CONSISTENCY NOT WORKING CORRECTLY ON MERGE STATEMENT"

My turn to confirm that the bug appears to survive in 12.2.0.1.

Would appreciate an update from someone with access to MOS - or Oracle colleagues?

Best regards, Stew Ashton
Connor McDonald
October 18, 2017 - 3:38 am UTC

Bug is still there.

I'll give it a "nudge"

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.