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 ?
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;
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
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.
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
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
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
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.
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).
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.
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.
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;
/
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
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
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: