Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 19, 2007 - 1:42 pm UTC

Last updated: June 12, 2023 - 12:43 pm UTC

Version: 9.2.0.7.0

Viewed 50K+ times! This question is

You Asked

When I insert or update, I can find out using SQL%ROWCOUNT how many rows were affected. But how do I find out how many rows a MERGE statement does?

Any help would be greatly appreciated.

Thanks

and Tom said...

same way....


ops$tkyte%ORA10GR2> begin
  2          merge into t using dual on (t.x = dual.dummy)
  3          when matched then update set y = y+1
  4          when not matched then insert (x,y) values ( dual.dummy, 0 );
  5
  6          dbms_output.put_line( sql%rowcount || ' rows affected...' );
  7  end;
  8  /
1 rows affected...

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /
1 rows affected...

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /
1 rows affected...

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t;

X          Y
- ----------
X          2

ops$tkyte%ORA10GR2>


Rating

  (18 ratings)

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

Comments

A reader, February 20, 2007 - 12:32 pm UTC

How to know whether SQL%rowcount corresponds to matched or not matched ?

Will this work for multiple rows as well ?
Tom Kyte
February 20, 2007 - 3:04 pm UTC

there is no telling the difference at all between matched and unmatched. They are MERGED, period.

Yes, sql%rowcount is accurate for more than one row.


ops$tkyte%ORA9IR2> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2          merge into t using all_users on (t.user_id = all_users.user_id)
  3          when matched
  4          then update set username = all_users.username
  5          when not matched then insert(user_id,username,created) values (all_users.user_id,all_users.username,all_users.created);
  6          dbms_output.put_line( sql%rowcount || ' rows merged' );
  7  end;
  8  /
35 rows merged

PL/SQL procedure successfully completed.



Sanjay, February 20, 2007 - 5:35 pm UTC

Merge will always give you the number of records merged regardless of how my records inserted or updated. But there is a workaround . Declare a numeric package variable and increment this variable by calling a function when you are inserting in merge statement ( WHEN NOT MATCHED)

At the end deduct the insert count from sql%rowcount you will get both number of rows inserted and number of rows updated .

Example

Create or replace package showMergeExample AS
tableCount NUMBER;
FUNCTION get_row_inserted;
END ;
/
Create or replace package BODY showMergeExample IS
FUNCTION get_row_inserted
RETURN NUMBER
AS
BEGIN
tableCount:=tableCount+1;
RETURN 0;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/

Now I will call this function showMergeExample. get_row_inserted in WHEN NOT MATCHED clause when inserting values . Since this function always return zero so I can replace ¿ VALUES (all_users.user_, all_users.username, all_users.created); ¿ to ¿ VALUES (all_users.user_id + showMergeExample .get_row_inserted, all_users.username, all_users.created);¿




BEGIN
MERGE INTO t
USING all_users
ON (t.user_id = all_users.user_id)
WHEN MATCHED THEN
UPDATE
SET username = all_users.username
WHEN NOT MATCHED THEN
INSERT (user_id, username, created)
VALUES (all_users.user_id + showMergeExample .get_row_inserted, all_users.username, all_users.created);

/ * now with the help of package variable I can get both insert and Update count*/

DBMS_OUTPUT.put_line (showmergeExample.tablecount || ' rows Inserted ');

DBMS_OUTPUT.put_line ((SQL%ROWCOUNT- showmergeExample.tablecount) || ' rows Updated ');
END;


Tom Kyte
February 21, 2007 - 10:44 am UTC

please do not do that - you are counting on a particular plan happening, in a particular order, under particular circumstances.

Any number of things could cause your function to be called "a lot more" than you anticipate.

This is a really bad idea.

MERGE ROWCOUNT

Adrian Billington, February 21, 2007 - 5:06 am UTC

I wrote about this very topic a few years back on the Quest PL/SQL Pipelines: http://www.quest-pipelines.com/newsletter-v4/0903_D.htm Be aware that if you go down the route of maintaining a merge insert/update counter, you must ensure that parallel dml / sql is disabled.

Regards
Adrian

merge rowcount

kenny roytman, June 20, 2007 - 12:10 pm UTC

hi tom,

in a nutshell, any idea how OWB accomplishes this?

thanks.

- ken
Tom Kyte
June 20, 2007 - 1:25 pm UTC

if it uses MERGE, it gets a single number like everyone else.

there are more than one way to merge - in a single sql statement, you get the number of rows merged.

merge rowcount

kenny roytman, June 21, 2007 - 2:06 pm UTC

tom,

not sure if you can provide any further feedback on this, but checked some of the PL/SQL that the latest OWB generates,

and it indeed generates MERGE statements for the MATCH/MERGE operator.

In the runtime screens, OWB then is able to give a breakdown of the number of rows updated/inserted.

Is it possible that they're using this "iffy" approach described above to generate those results?

thanks.

- kenny r.
Tom Kyte
June 22, 2007 - 10:08 am UTC

they would not put triggers on.

you should be able to look at the generated code with OWB (which I don't have installed anywhere - so I won't be doing that) to see what they are doing.

MERGE may not "know" if it's INSERT or UPDATE

Duke Ganote, June 21, 2007 - 4:22 pm UTC

How to find Number of Rows Inserted and Updated?

shashi wagh, February 15, 2010 - 12:58 am UTC

Hi Tom,

How to I find Number of Rows Inserted and Updated?
Please Help me
Tom Kyte
February 16, 2010 - 4:57 pm UTC

re-read the page, it is said there that the concept doesn't exist.

ORA-38104: Columns referenced in the ON Clause cannot be updated: "XC"."NAME"

shashi wagh, February 18, 2010 - 12:37 am UTC

I have following merge query

merge into xcity xc
using xcity_new xcn
on (xc.code = xcn.code and lower(xc.name) <> lower(xcn.name))
when matched then
update set xc.name = xcn.name
when not matched then
insert (code,name) values(xcn.code,xcn.name);


I want to check the column which needs to update

But it gives me error


ORA-38104: Columns referenced in the ON Clause cannot be updated: "XC"."NAME"
ORA-06512: at "SFA_DEV.PROC_SALES_TRGT_UPLOAD", line 5
ORA-06512: at line 2


Do have any solution for such cases?

Thanks

Tom Kyte
February 18, 2010 - 9:23 am UTC

Tell us your goal here, because this logic currently looks "not sensible" to me.


think about it

what if you have data like:
CODE     NAME
-----    -----
123      A
123      B


and you merge into that

123 a

guess what you would have ended up with?


CODE     NAME
-----    -----
123      a      <<<=== updated
123      B
123      a      <<<=== inserted because of a hit on 123,B



I cannot imagine that is what you intended.

A reader, February 18, 2010 - 11:17 am UTC

<qoute>
CODE NAME
----- -----
123 a <<<=== updated
123 B
123 a <<<=== inserted because of a hit on 123,B
<qoute>

don't you think the output would be as below

CODE NAME
----- -----
123 A
123 a <<<=== updated because of a hit on 123,B
123 a <<<=== inserted because of a hit on 123,A

Tom Kyte
February 18, 2010 - 7:34 pm UTC

yeah, I mixed up the <> in the hypothetical output of a query that won't run in the first place :)

but yes, the output would be "nonsense" if it did what they thought it should do.

Conditional update on merge

Richard, February 23, 2010 - 4:19 pm UTC

Quote from above:

merge into xcity xc
  using xcity_new xcn
  on (xc.code = xcn.code and lower(xc.name) <>  lower(xcn.name))
  when matched then 
     update set xc.name = xcn.name
  when not matched then 
     insert (code,name) values(xcn.code,xcn.name);


To make the update of xc.name conditional so it updates if the case-insensitive value is different in xcn.name, use this instead:

merge into xcity xc
  using xcity_new xcn
  on (xc.code = xcn.code)
  when matched then 
     update set xc.name = xcn.name
      where lower(xc.name) <>  lower(xcn.name)
  when not matched then 
     insert (code,name) values(xcn.code,xcn.name);


Then the merge matches by code and updates name only if different case-insensitive content. If the code doesn't match, the new row is inserted.
Tom Kyte
March 01, 2010 - 8:34 am UTC

still doesn't work. You presume that they have to match by CODE (on clause), they have to match by code and upper(name) as I see it.

eg: if the set to merge into is:

123, 'A'
123, 'B'
123, 'a'

and the set to merge from (new values) is

123, 'a'
123, 'b'


What should happen, what would happen? There isn't a way to do this logically as far as I can see. The request is not sufficient specified out to implement in any logical sense.


If code is not unique in the merged into table, this is ambiguous.

To find rows affected by Merge statement

Jardon Ali, August 10, 2011 - 5:20 am UTC

SQL%ROWCOUNT after a MERGE statement would only give the count of records for an insert. If we do an update with the same values present earlier in all columns of the table the sql%rowcount retrieves no count (count is zero).
Tom Kyte
August 13, 2011 - 4:21 pm UTC

what?

<code
ops$tkyte%ORA11GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte%ORA11GR2> create table t2 ( x int, y int );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t1 values ( 1, 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values ( 2, 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 1, null );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 merge into t2 using t1
3 on (t1.x = t2.x)
4 when matched then update set t2.y = t1.y
5 when not matched then insert (x,y) values ( t1.x, t1.y );
6
7 dbms_output.put_line( sql%rowcount );
8 end;
9 /
2

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t2;

X Y
---------- ----------
1 1
2 2

></code>


it counts both updated and inserted records.

how to find rows affected by Merge

Jardon Ali, August 10, 2011 - 5:53 am UTC

Hi please ignore the previous post from me. The merge I used earlier didnt have the 'WHEN MATCHED' CLAUSE.
Didnt find an option to delete the post.
The err is regretted.

awr rows_processed_delta

Bertran Saragusti, May 17, 2012 - 10:06 am UTC

Hi Tom,

I'm analyzing this MERGE statement :

MERGE INTO PROPERTY_LEAD_PRICE USING DUAL ON (PROVIDER_ID = :1 AND CHECKIN_DATE = :2 AND RATE_TYPE_ID = :3 AND DISTRIBUTION_CHANNEL_ID = :4 AND LENGTH_OF_STAY=:5)
WHEN NOT MATCHED THEN
INSERT (PROVIDER_ID,SABRE_PROPERTY_NBR,RATE_TYPE_ID,CURRENCY_CODE,CHECKIN_DATE,
DISTRIBUTION_CHANNEL_ID,UPDATED,TOTAL_COLLECTED_PRICE,BASE_PRICE,TAX,COLLECTED_FEES,
LENGTH_OF_STAY,NON_COLLECTED_FEES) VALUES (:6, :7, :8, :9, :10, :11, systimestamp , :12, :13, :14, :15, :16, :17)
WHEN MATCHED THEN
UPDATE SET UPDATED = systimestamp, TOTAL_COLLECTED_PRICE = :18, BASE_PRICE = :19, TAX = :20,
COLLECTED_FEES = :21, NON_COLLECTED_FEES = :22, SABRE_PROPERTY_NBR = :23, CURRENCY_CODE = :24
WHERE TOTAL_COLLECTED_PRICE <> :25 OR NON_COLLECTED_FEES <> :26;

I'm puzzled because the table has a unique index based on all the joined columns, the sql plan shows a unique scan :

OPERATION OPTIONS OBJECT_NODE OBJECT# OBJECT_OWNER OBJECT_NAME
MERGE STATEMENT
MERGE GIO_O_1 PROPERTY_LEAD_PRICE
VIEW
NESTED LOOPS OUTER
TABLE ACCESS FULL 258 SYS DUAL
PARTITION RANGE SINGLE
VIEW
TABLE ACCESS BY LOCAL INDEX ROWID 110798 GIO_O_1 PROPERTY_LEAD_PRICE
INDEX UNIQUE SCAN 110815 GIO_O_1 AK_PRPLEADPRC_UPDCHKINDT


But AWR shows it as a top consumer in all their ways.

ROWS_PROCESSED_DELTA/EXECUTIONS_DELTA are in the range from 5 to 18. How come one merge execution on an unique condition can process more than one row ? not to mention 18.

Check this example from dba_hist_sqlstat :

EXECUTIONS_DELTA DISK_READS_DELTA BUFFER_GETS_DELTA ROWS_PROCESSED_DELTA
4879 63923 2939192 64833
4721 62992 2940063 72164
5167 49198 2470358 61474
5238 40226 2286054 64302

The table is partitioned by CHECKIN_DATE, the unique index is LOCAL and we have 16 partitions ( 4 of them have no rows ).

I appreciate any guide you can give to understand what is happenning.

Thanks,
Bertran Saragusti.
Tom Kyte
May 18, 2012 - 2:28 am UTC


... But AWR shows it as a top consumer in all their ways.
.....

why is that mysterious? Why couldn't a sql statement that is executed frequently be at the top??? If you do something fast - over and over and over and over and over and over and over (row by row - slow by slow processing - not too good, worst thing you can do) - it will take a long long long long time and consume tons and tons and tons of resources.


AWR is a snapshot, it is imprecise, it is closer than close enough to get useful information from - but it is not a "video", it is not "perfectly 100% totally accurate".


It is a digital view of analog data.

question again

A reader, June 04, 2012 - 11:57 am UTC

Hi Tom,

I know exactly what AWR is, my main question was not answered :

How come one merge execution on an unique condition can process more than one row ? not to mention 18.

Thanks,
Bertran Saragusti.
Tom Kyte
June 04, 2012 - 12:41 pm UTC

I was objecting to your statement:

But AWR shows it as a top consumer in all their ways.

that shouldn't be surprising, if you execute something small and fast over and over and over - it'll be your top consumer. And this is classic slow by slow processing (using DUAL to merge a single row at a time :( )

at least they used array processing. Not very much - only about 5-18 rows per bind execute, but at least they did that....


 
ops$tkyte%ORA11GR2> create table t
  2  as
  3  select object_id, owner, object_type, object_name
  4    from all_objects
  5   where rownum <= 100;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          type array is table of number index by binary_integer;
  3      l_obj_ids  array;
  4  begin
  5      for x in (select * from t)
  6      loop
  7                  l_obj_ids(1) := x.object_id;
  8                  l_obj_ids(2) := -x.object_id;
  9  
 10          forall i in 1..2
 11              merge into t using dual
 12              on (t.object_id = L_OBJ_IDS(i) )
 13              when not matched then insert(object_id,owner,object_type,object_name)
 14              values (l_obj_ids(i), x.owner, x.object_type, x.object_name)
 15              when matched then update set object_name = 'xxx';
 16      end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select sql_text, executions, rows_processed from v$sql where sql_id = '4z76c6cnmqr3q';

SQL_TEXT
-------------------------------------------------------------------------------
EXECUTIONS ROWS_PROCESSED
---------- --------------
MERGE INTO T USING DUAL ON (T.OBJECT_ID = :B1 ) WHEN NOT MATCHED THEN INSERT(OB
JECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME) VALUES (:B2 , :B5 , :B4 , :B3 ) WHEN MAT
CHED THEN UPDATE SET OBJECT_NAME = 'xxx'
       100            200




I only executed it 100 times.

It did 200 rows.

different rowcount from 10.2.0.3.0 to 11.2.0.2.0

giuseppe d'ambrosio, September 12, 2013 - 1:34 pm UTC

hello Tom,

the following script on version 11 returns an affected row on merge (case WHEN MATCHED THEN UPDATE) though no row can match the condition ("where 0=1")

on version 11 dbms_output writes:
x=1
x=1

the same works instead as I expect on version 10, and dbms_output writes:
x=1
x=0

..am I missing something?

thanks very much
Giuseppe


-------------


create table temp1 (col1 integer, col2 integer);

create table temp2 (col1 integer, col2 integer);

declare x integer;
begin

MERGE INTO temp1 t
USING (select 1 col1 ,3 col2 from dual) x
ON (t.col1 = x.col1)
WHEN MATCHED THEN UPDATE SET t.col2 = x.col2
WHEN NOT MATCHED THEN INSERT
VALUES (x.col1, x.col2);

x:= sql%rowcount;
dbms_output.put_line ('x='||x);

insert into temp2 select * from temp1;
commit;

MERGE INTO temp1 t
USING (select 1 col1 ,3 col2 from temp2 where 0=1) x
ON (t.col1 = x.col1)
WHEN MATCHED THEN UPDATE SET t.col2 = x.col2;

x:= sql%rowcount;
dbms_output.put_line ('x='||x);

end;
/





Tom Kyte
September 23, 2013 - 6:03 pm UTC

ops$tkyte%ORA11GR2> declare x integer;
  2
  3  begin
  4
  5  MERGE INTO temp1 t
  6     USING (select 1 col1 ,3 col2 from dual) x
  7     ON (t.col1 = x.col1)
  8     WHEN MATCHED THEN UPDATE SET t.col2 = x.col2
  9     WHEN NOT MATCHED THEN INSERT
 10       VALUES (x.col1, x.col2);
 11
 12  x:= sql%rowcount;
 13  dbms_output.put_line ('x='||x);
 14
 15  insert into temp2 select * from temp1;
 16  commit;
 17
 18  MERGE INTO temp1 t
 19     USING (select 1 col1 ,3 col2 from temp2 where 0=1) x
 20     ON (t.col1 = x.col1)
 21     WHEN MATCHED THEN UPDATE SET t.col2 = x.col2;
 22
 23  x:= sql%rowcount;
 24  dbms_output.put_line ('x='||x);
 25
 26  end;
 27  /
x=1
x=0

PL/SQL procedure successfully completed.





I cannot reproduce with 11.2.0.3

it does the right thing in 11g.


It depends

Aalbert, October 02, 2013 - 8:14 am UTC

Depending on the situation, you could do the same merge twice, omitting the WHEN MATCHED the first time. That way, the first %ROWCOUNT will be the number of insertions, while the second will be the total number of merges.
This might be slow and not completely accurate, but may be good enough for what you're doing.

What about using count(1)

J. Chase, March 24, 2023 - 2:48 pm UTC

Going to try this here in a minute. This is pseudo-code:

select count(1) into before_count from my.table

merge into my.table using . . .

rows_affected = sql%rowcount

select count(1) into after_count from my.table

insert_count = after_count - before_count
update_count = rows_affected - insert_count

Chris Saxon
March 27, 2023 - 1:14 pm UTC

This is unreliable if there are other transactions adding/removing rows at the same time. If you can make the transaction serializable (so changes in other sessions are hidden from you) this could work. If the table is "large" adding the count may make this process unacceptably slow too.

You can see other solutions for getting the ins/upd split in this tutorial:

https://livesql.oracle.com/apex/livesql/file/content_NSEVYBMX5SSVTFMZS24P57NL4.html

Sql%rowcount merge using plsql package variable

Oj, June 12, 2023 - 10:05 am UTC

Back in Feb 2007, you said it was a bad idea to use the package variable solution, I'm just trying to figure out why because it seems to work fine. It's also one that's very similar in approach to the one outlines here

http://www.oracle-developer.net/display.php?id=220
Chris Saxon
June 12, 2023 - 12:43 pm UTC

Adrian - the author of the article you link to - points (at least) one caveat in the very next comment on this page:

you must ensure that parallel dml / sql is disabled

With package variables there's a risk that how the MERGE is executed changes, meaning the counter is incorrect. It's up to you to assess whether this risk is acceptable for you.

I discussed other ways to see the row counts and insert/update split in a SQL Office Hours session:


More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library