how nice is it
Leon Rzhemovskiy, November 25, 2002 - 8:26 pm UTC
Thanks Tom, I appreciate your fast and as knowledgeable feedback
I am running 9i as well. As you understand my question is related not to a problem but rather to best practice.
Would you be able to say that merge is not only nice but also reasonable to implement with pipelining going forward in 9i.
What should I expect speed, processor usage, redo? Are you aware of any problem with it?
Thanks
Leon
outstanding
Leon Rzhemovskiy, November 26, 2002 - 1:41 pm UTC
Thanks for detail explanations and code examples.
Comment on handwritten code is unfortunately applicable only to performance side. Practical application may require procedural approach due to exception handling, auditing etc.
Good comparison stats
Kevin, February 18, 2003 - 9:56 pm UTC
Hi Tom,
The comparison numbers are very impressive and convincible. But I can't find the table structure for RUN_STATS and table/view STATS - is this a data dictionary table/view?
Thanks.
Kevin
February 19, 2003 - 8:41 am UTC
Merge + variables
Robert, March 13, 2003 - 9:59 am UTC
Hi Tom,
Can you use variables in place of tables in the merge statement. For example if your table to be upserted has 4 fields and these 4 fields are passed in as parameters. How does that work as you can't have:
merge into t1
using ao on ( t1.object_id = ao.object_id )
when there is only one table. I tried this using dual as the dummy table and although it compiled it never seemed to work.
Robert.
March 14, 2003 - 5:17 pm UTC
identifiers cannot be bound in ANY statement -- the plan would totally change.
you would have to use dynamic sql.
Is Upsert possible for single table?
Tony, June 12, 2003 - 7:32 am UTC
MERGE statement needs two tables to implement upsert.
Can't we do upsert in one table?
For example: In emp table, I want to update if record exists, else insert.
June 12, 2003 - 9:16 am UTC
update it WITH WHAT?
insert it FROM WHERE?
you need that other table see -- it has the data to update with, and the data that needs to be inserted.
EMP is the target, so, where is the source of the data?
If you mean "for a single row", use "select :bv1, :bv2, .... from dual" as the source and bind in the single row values. That'll either update or insert a single row.
What's faster delete/insert or upsert?
Peter Tran, June 12, 2003 - 10:45 am UTC
Assuming that all columns can be replaced, is it faster to bulk delete and bulk inserts, or upsert using the merge technique you outlined above?
Thanks,
-Peter
June 12, 2003 - 11:15 am UTC
truncate+insert /*+ append */ into a nologging table (with a backup of that tablespace right afterwards of course) would most likely be faster, especially if it were
truncate
disable all indexes
insert /*+ append */
rebuild in parallel/nologging the indexes
backup
Clarification to my earlier question
Peter Tran, June 12, 2003 - 12:20 pm UTC
Tom,
What if I can't do a complete truncate of the table? What if my table currently has 100 million rows, and now I have say a million records that can either be new records or update of the existing records.
Currently, we would delete all duplicate records in the table and then bulk insert the new 1 million records.
This was faster then to do an update which throws an exception if the record doesn't exist, then do an insert.
Thanks,
-Peter
June 12, 2003 - 1:41 pm UTC
that is because row by row operations are almost always slower (there are cases, but in general)...
a merge would be called for here. I have a 3.8 million row table and want to merge 1% more
big_table@ORA920> merge into big_table bt
2 using merge_data md on ( bt.id = md.id )
3 when matched then
4 update set owner = md.owner,
5 object_name = md.object_name,
6 subobject_name = md.subobject_name,
7 data_object_id = md.data_object_id,
8 object_type = md.object_type,
9 created = md.created,
10 last_ddl_time = md.last_ddl_time,
11 timestamp = md.timestamp,
12 status = md.status, temporary = md.temporary,
13 generated = md.generated,
14 secondary = md.secondary
15 when not matched then
16 insert ( id, OWNER, OBJECT_NAME,
17 SUBOBJECT_NAME, OBJECT_ID,
18 DATA_OBJECT_ID, OBJECT_TYPE,
19 CREATED, LAST_DDL_TIME,
20 TIMESTAMP, STATUS, TEMPORARY,
21 GENERATED, SECONDARY )
22 values ( md.id, md.OWNER, md.OBJECT_NAME,
23 md.SUBOBJECT_NAME, md.OBJECT_ID,
24 md.DATA_OBJECT_ID, md.OBJECT_TYPE,
25 md.CREATED, md.LAST_DDL_TIME,
26 md.TIMESTAMP, md.STATUS, md.TEMPORARY,
27 md.GENERATED, md.SECONDARY);
38172 rows merged.
Elapsed: 00:05:09.51
Statistics
----------------------------------------------------------
414 recursive calls
84182 db block gets
96814 consistent gets
45069 physical reads
19120100 redo size
791 bytes sent via SQL*Net to client
1850 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
38172 rows processed
Or, I delete and insert:
big_table@ORA920> delete from big_table
2 where id in ( select id from merge_data );
19086 rows deleted.
Elapsed: 00:08:33.78
Statistics
----------------------------------------------------------
175 recursive calls
135948 db block gets
3872806 consistent gets
78021 physical reads
15651020 redo size
793 bytes sent via SQL*Net to client
832 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19086 rows processed
big_table@ORA920> insert /*+ APPEND */ into big_table select * from merge_data;
38172 rows created.
Elapsed: 00:01:19.03
Statistics
----------------------------------------------------------
39 recursive calls
98282 db block gets
640 consistent gets
19373 physical reads
9934016 redo size
778 bytes sent via SQL*Net to client
829 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
38172 rows processed
and I even gave the insert the benefit of append, something you might not do in the real world (that 1% of space isn't going to be reused with an append)
Efficient Upsert
Mark, June 12, 2003 - 12:51 pm UTC
Simply wonderful. I really admire your depth of your knowledge.
Awesome example
Peter Tran, June 12, 2003 - 3:30 pm UTC
I concur with Mark. Your example does a wonderful job of proving your point.
One last follow up question, I'm assuming there's going to be overhead in inserting data into the merge_data table. Assuming that the merge_data table is a GTT, then can I assume the following to be true?
runtime(insert into GTT + using merge) < runtime (delete/insert)
A simple yes/no would be sufficient.
Best,
-Peter
June 12, 2003 - 4:02 pm UTC
I assumed merge_data already exists?
but in general, populating merge_data+merging I would imagine would be more efficient then DELETE by key, INSERT..
simple yes/no answers are things I hesitate to give ;)
Merge with TABLE(CAST...
Jan, July 16, 2003 - 9:35 am UTC
Dear Tom,
is the MERGE command not supported in following case or is there some other way how to do this?:
MERGE INTO normal_table t
USING TABLE(CAST(my_collection
AS my_collection_type))c
...
...
It gives me an error:
PL/SQL: ORA-00903: invalid table name
It works if I use normal table instead of collection.
Oracle version 9.2.0.1.0
Regards, Jan
July 16, 2003 - 9:42 am UTC
You have to "select" from the function
ops$tkyte@ORA920LAP> create type myScalarType as object
2 ( x int, y date )
3 /
Type created.
ops$tkyte@ORA920LAP> create type myArrayType as table of myScalarType
2 /
Type created.
ops$tkyte@ORA920LAP> create or replace function myfunction return myArrayType
2 as
3 l_data myArrayType :=
4 myArrayType( myScalarType( 1, sysdate ),
5 myScalarType( 2, sysdate+2 ),
6 myScalarType( 3, sysdate+3 ) );
7 begin
8 return l_data;
9 end;
10 /
Function created.
ops$tkyte@ORA920LAP> create table t
2 as
3 select rownum x, sysdate-rownum y
4 from all_objects
5 where rownum <= 3;
Table created.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> merge into t
2 using ( select * from TABLE( myFunction ) ) c
3 on ( t.x = c.x )
4 when matched then update set y = c.y
5 when not matched then insert ( x,y ) values ( c.x, c.y );
3 rows merged.
merge
vj, July 28, 2003 - 5:41 am UTC
Hello Tom,
I require an help in Merge Command
My Table Script :
CREATE TABLE TRANSSRL (
MY_CODE VARCHAR2 (12) NOT NULL,
PREFIX VARCHAR2 (3) NOT NULL,
YEAR NUMBER (4) NOT NULL,
CURR_NO NUMBER (16) NOT NULL,
CONSTRAINT PK_TRANS_SRL
PRIMARY KEY ( CURR_NO, MY_CODE, PREFIX, YEAR ) )
/
--For your demo i insert a record
insert into transsrl values('PBM','GE',2002,31)
/
--My Merge Statment
merge INTO TRANSSRL a
USING
(
SELECT
my_code,prefix,year,curr_no
FROM
TRANSSRL
WHERE
my_code = 'PBM' AND
prefix = 'GE' AND
year = 2002
) b
ON
(
a.my_code = b.my_code AND
a.prefix = b.prefix AND
a.year = b.year
)
WHEN matched THEN UPDATE SET a.curr_no = NVL(curr_no,0) +1
WHEN NOT matched THEN INSERT (my_code,prefix,year,curr_no) VALUES ('PBM','GE',2002,1)
This statement executes properly, since data is available for this criteria , and it updates the currno column from 31 to 32..
If i try to change the year so that "when not matched" can happen...it fails..
i changed the year from 2002 to 2003
merge INTO TRANSSRL a
USING
(
SELECT
my_code,prefix,year,curr_no
FROM
TRANSSRL
WHERE
my_code = 'PBM' AND
prefix = 'GE' AND
year = 2003
) b
ON
(
a.my_code = b.my_code AND
a.prefix = b.prefix AND
a.year = b.year
)
WHEN matched THEN UPDATE SET a.curr_no = NVL(curr_no,0) +1
WHEN NOT matched THEN INSERT (my_code,prefix,year,curr_no) VALUES ('PBM','GE',2003,1)
Logically here "it is not matched" so i expected another record in the table with
PBM,GE,2003,1
..But it is not happening..can you help me
July 28, 2003 - 7:35 am UTC
umm, the query:
(
SELECT
my_code,prefix,year,curr_no
FROM
TRANSSRL
WHERE
my_code = 'PBM' AND
prefix = 'GE' AND
year = 2003
)
returns no data -- that is why -- there is nothing to "match" or "not match" to at all....
vj, July 29, 2003 - 12:54 am UTC
Tom,
What is the alternate for me to implement the merge in this scenario ??...i got to check whether record exists for the Primary Key..if no record exists then Insert Otherwise Update..Currently we have a block of pl/sql code to do the same..Would be great if i can implement through merge as this would be called very frequently.
July 29, 2003 - 7:03 am UTC
if the set of data you are merging on CONTAINS NO DATA, then there is no data to match to or insert.
that is the problem. your data to merge with is EMPTY. we do not "make up data"
Great stuff
A reader, August 20, 2003 - 11:58 am UTC
If I have a database (9i) and in the MERGE statement the USING clause selects through DB Link (that database is 8i) is it possible to use MERGE?
Something like:
MERGE into T1
(using Select * from T2@DB_LINK_T2 ) T2
on (T1.Id = T2.Id)
WHEN MATCHED then
update set ...
WHEN NOT MATCHED then
insert (id, ... )
value (id, ... ) ;
August 21, 2003 - 8:31 am UTC
ops$tkyte@ORA920> select * from v$version@ora817dev;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
ops$tkyte@ORA920> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
ops$tkyte@ORA920> @a
ops$tkyte@ORA920> merge into emp
2 using emp@ora817dev e2 on ( emp.empno = e2.empno )
3 when matched then update set emp.ename = e2.ename
4 when not matched then insert ( empno ) values ( e2.empno )
5 /
14 rows merged.
Using sql%rowcount to update user activity.
ht, August 26, 2003 - 8:03 pm UTC
Tom,
I'm using your technique to update records, retrieve the sql%rowcount, and insert if sql%rowcount=0.
How can I track a range of times that a user is logged on? For example, if the user appears in v$session, I would like to insert sysdate into useractivity.begin_time. If the user is still logged on when the check runs again, I would update useractivity.end_time. If the user logs off, I would like to do nothing (because the proc won't be called since v$session would return no records for that user). useractivity.end_time would always be updated with the last time the proc found that the user was logged in.
I'm hung up on figuring out a way to have multiple records in the useractivity table that would show 1 record for each duration of time the user was logged in. For example:
user begin end
__________________
user1 5pm 5:15pm
user1 5:35pm 6:55pm
etc ...
I'm trying to avoid:
user1 5pm
user1 5:01pm
user1 5:02pm
user1 5:15pm
I hope I've explained my problem clearly.
Thanks in advance,
ht
August 27, 2003 - 7:55 am UTC
you can just enable auditing and audit connects. that'll get the "begin time and end times" for you.
a simple outer join to v$session -- or better yet a scalar subquery to v$session -- can be used to fill in the missing end times.
Can you provide a sample?
ht, August 27, 2003 - 2:30 pm UTC
Tom,
Thanks for the auditing tip.
Can you provide an example on how I could use an outer join or scalar subquery to update a session's "end_time"? I still am not able to figure out how I could have a record for each duration that my proc captures a user's session.
I understand how to write 1 record each time the user is logged in but I would like to have 1 record for each duration:
user1 5pm-6pm
user1 7pm-8pm
Currently, if my update fails, I insert. The problem is that I end up with this:
user1 5pm-8pm
This doesn't tell me that user1 was not on the system between 6pm and 7pm.
The reason I'm pursuing this with a non-auditing solution is that I would also like to capture other non-auditable events such as the duration that transactions/second are over 50.
Thanks,
ht
August 27, 2003 - 6:17 pm UTC
use auditing, you'll get the right answer, no muss, no fuss.
I cannot fix the 6-7pm problem, only auditing can.
How about tracking periods when a file is offline?
ht, August 27, 2003 - 6:20 pm UTC
Tom,
I can use auditing for the user activity tracking. But what about tracking when a datafile is offline (or other db events)? Is there a way that I can have 1 record for the duration a datafile is offline without having to write 12 records every hour (if I check every 5 minutes)?
TIA
ht
August 27, 2003 - 7:24 pm UTC
you would have to
a) query what you thought the status was -- online or offline
b) compare that to what the status is
c) if different (or a returns nothing) then log B
Of course!
ht, August 27, 2003 - 7:38 pm UTC
Tom,
I was missing a simple flag that needs to updated before a new record is inserted. If an update returns no rows, I update the flag and insert a new record. Thanks for pointing out the obvious.
ht
A reader, November 07, 2003 - 10:19 am UTC
Can you show the same logic (upsert ) with 8i (817)
November 07, 2003 - 10:46 am UTC
takes an insert + update in 8i
ops$tkyte@ORA920PC> create table t1 ( x int primary key, y int );
Table created.
ops$tkyte@ORA920PC> create table t2 ( a int primary key, b int );
Table created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into t1 values ( 1, 1 );
1 row created.
ops$tkyte@ORA920PC> insert into t2 values ( 1, 2 );
1 row created.
ops$tkyte@ORA920PC> insert into t2 values ( 2, 2 );
1 row created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from t1;
X Y
---------- ----------
1 1
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> update ( select *
2 from t1, t2
3 where t1.x = t2.a )
4 set y = b;
1 row updated.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into t1
2 select *
3 from t2
4 where a not in ( select x from t1 )
5 /
1 row created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from t2;
A B
---------- ----------
1 2
2 2
merge with rowtype array, is it possible?
reader, December 16, 2003 - 7:17 pm UTC
Tom,
gtt(insert)+merge is great. However, I was thinking looking at rowtype data from your link ->
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12226527910608, <code>
from your link,
ops$tkyte@ORA920LAP> declare
2 type array is table of emp%rowtype index by binary_integer;
3
4 l_data array;
5 begin
6 select * bulk collect into l_data from emp;
7
8 forall i in 1 .. l_data.count
9 insert into emp values l_data(i);
10 end;
11 /
Somehow, I can use l_data as source for the 'merge' command? That way I can do all this in memory (with some reasonable size of the bulk collect)..
Thanks in advance
December 17, 2003 - 6:38 am UTC
You'd need to use sql collection types for this, it could look like this:
ops$tkyte@ORA9IR2> create type myScalarType as object
2 ( x int, y date, z varchar2(20) )
3 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create type myArrayType as table of myScalarType
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int, y date, z varchar2(20) );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( x int, y date, z varchar2(20) );
Table created.
ops$tkyte@ORA9IR2> insert into t1
2 select rownum, sysdate+rownum, rownum
3 from all_users
4 where rownum <= 5;
5 rows created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2
2 select rownum+3, sysdate+rownum+30, rownum+30
3 from all_users
4 where rownum <= 5;
5 rows created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
X Y Z
---------- --------- --------------------
1 18-DEC-03 1
2 19-DEC-03 2
3 20-DEC-03 3
4 21-DEC-03 4
5 22-DEC-03 5
ops$tkyte@ORA9IR2> declare
2 l_data myArrayType;
3 begin
4 select cast(
5 multiset( select myScalarType(x,y,z) from t2 ) as myArrayType )
6 into l_data
7 from dual;
8
9 merge into t1
10 using ( select t.x, t.y, t.z from TABLE(cast(l_data as myArrayType)) t ) t2
11 on ( t1.x = t2.x )
12 when matched then update set y = t2.y, z = t2.z
13 when not matched then insert (x,y,z) values ( t2.x, t2.y, t2.z );
14 end;
15 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from t1;
X Y Z
---------- --------- --------------------
1 18-DEC-03 1
2 19-DEC-03 2
3 20-DEC-03 3
4 17-JAN-04 31
5 18-JAN-04 32
6 19-JAN-04 33
7 20-JAN-04 34
8 21-JAN-04 35
8 rows selected.
Just great!
reader, December 17, 2003 - 7:16 am UTC
Tom,
Thanks a lot for making it so clear and simple.
Greatly appreciate your help
Have a great day!
how about efficient upsert in 8i ?
ET, December 30, 2003 - 11:01 pm UTC
I'm still using 8i and therefore can't make use of "merge".
Can you kindly advice what's the efficient upsert code (with sample code) in 8i ?
December 31, 2003 - 9:34 am UTC
takes an insert + update in 8i
look upwards on this page for that text and you'll see the example, answered already.
merge with condition
karma, March 08, 2004 - 3:11 pm UTC
Using merge
when matched then update
when not matched then insert. In this case of insert can you only insert based on some condition? like insert only if it meets certain criteria?
March 08, 2004 - 3:23 pm UTC
no, you would have to filter out rows you did not want to insert in the USING clause.
OK
NATHAN, March 18, 2004 - 6:22 am UTC
Dear Tom,
I would like to update the following table with a condition "Update
first row by 1,second row by 2,third row by 3"
SQL> select * from tbl;
I
----------
1
2
3
SQL> desc tbl
Name Null? Type
----------------------------------------- -------- ----------------------------
I NUMBER
Updation results in
sql>select * from tbl;
I
-----------
2
4
6
How to do that?
March 18, 2004 - 7:58 am UTC
what is the "first row"... hmm, do you think things come out in the order they went in?
set i = i+rownum;
but don't be surprised by the results. there is no such thing as "the first row in a table", the "nth row" or the "last row"
upsert on master/detail?
bob, March 18, 2004 - 8:09 am UTC
Hi Tom,
Thanks for this discussion above. If the record from the master table you are merging/upserting has detail records what do you do?
From a singe line of input from a flat file, I create a surrogate pk, some attributes, and populate a detail table with somewhere between 1-8 rows of data associated with the master record. Some of these attributes define a unique key, but potentially may change, so I went with the surrogate key.
each new line of the flat file requires an upsert (no problem there thanks to the above discussions) but the detail records may be potentially different.
I decide if I should attempt to identify if all the associated detail records are the same or not. My initial thoughts say:
1. insert,
2. if unique constraints are violated in the master record, update it, and delete associated detail records and reload them, even if they are potentially the same. This seems wasteful, but easier than trying to check each time whether or not I have exactly the N number of records with that same FK and that each of those records has the values that match my list of expected values. (sounds like a simple question, but I can't convert it from a multi-step procedure to simple sql)
your thoughts please?
March 18, 2004 - 8:19 am UTC
i don't understand why the fact that a table being merged into is a "master" record with "details" has any bearing?
Your second paragraph describes a multi-table insert, not a merge?
so, it sounds like you are getting a single record with the master AND details, it'll be near impossible to use a "MERGE" for that, given the "i create a surrogate pk" statement.
don't see how you would process this efficiently
(better to update generally and upon hitting sql%rowcount=0, insert. the insert actually does all of the work of inserting and upon duplication runs recursive sql to get constraint information and such. can be very expensive)
no merge possibilities.
bob, March 18, 2004 - 9:36 am UTC
Tom,
Thank you for your timely response.
You described the situation correctly. Multi-table insert, that might fail and require an update when the master table pk is violated.
I have a >98% insert, <2% update situation, so the expense of the occasionaly recursive sql on the failed update might be better than the 98% sql%rowcount=0.
Regardless, I can benchmark that to verify where the breakeven point is for my data. I can live without the efficiency of a merge, but where does that leave me with regards to attempting to update multiple detail records from the new line single line of input.
I see the only option for the detail table as delete everything and reinsert.
Since deletes may be required if too many values are currently in the detail table, I might as well just delete everything and reinsert.
If I could devise a query that would tell me whether all detail values I have are present with no extras, I could potentially avoid the delete/insert altogether, but I haven't been able to do that.
It sounds simple though. get the count of records matching the fk and the known list of values, get the total count matching the fk, if they are not the same, than delete and insert.
For table having security policy
Nitin, April 13, 2004 - 10:48 am UTC
Hi Tom,
It seems that with security policies defined on a table, merge does not work.
ORASSO@iasdb >select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
PL/SQL Release 9.0.1.4.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production
Elapsed: 00:00:00.00
ORASSO@iasdb >ed
Wrote file afiedt.buf
1 MERGE INTO WWSSO_APPUSERINFO_T A
2 USING T B
3 ON ( A.SSO_USERNAME = B.SW_ONE_ID AND A.APP_ID = B.APP_ID )
4 WHEN MATCHED THEN
5 UPDATE SET A.SUBSCRIBER_ID = 1,
6 A.APP_USERNAME=B.SAP_ID,
7 A.APP_PWD=B.MGR_PW
8 WHEN NOT MATCHED THEN
9 INSERT (A.SUBSCRIBER_ID,A.APP_ID,A.SSO_USERNAME,A.APP_USERNAME,A.APP_PWD)
10 VALUES( 1, B.APP_ID,
11 B.SW_ONE_ID,
12 B.SAP_ID,
13* B.MGR_PW)
ORASSO@iasdb >/
MERGE INTO WWSSO_APPUSERINFO_T A
*
ERROR at line 1:
ORA-28132: Merge into syntax does not support security policies.
Elapsed: 00:00:00.00
ORASSO@iasdb >desc WWSSO_APPUSERINFO_T
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSCRIBER_ID NOT NULL NUMBER
SSO_USERNAME NOT NULL VARCHAR2(256)
APP_ID NOT NULL VARCHAR2(80)
APP_USERNAME VARCHAR2(512)
APP_PWD VARCHAR2(512)
USER_FIELDS EXTRAFIELDSTYPE
USER_PREFS VARCHAR2(1000)
ORASSO@iasdb >select column_name,data_default from user_tab_columns
2 where TABLE_NAME = 'WWSSO_APPUSERINFO_T';
COLUMN_NAME DATA_DEFAULT
-------------------- --------------------------------------------------
SUBSCRIBER_ID sys_context('WEBDB_VPD_CTX','subscriber_id')
SSO_USERNAME
APP_ID
APP_USERNAME
APP_PWD
USER_FIELDS
USER_PREFS
7 rows selected.
Elapsed: 00:00:00.01
Do you know if there is a workaround for this one?
I am currently doing a slow (row by row) upsert in my procedure.
Thanx
April 13, 2004 - 2:31 pm UTC
instead of
1 MERGE INTO WWSSO_APPUSERINFO_T A
2 USING T B
3 ON ( A.SSO_USERNAME = B.SW_ONE_ID AND A.APP_ID = B.APP_ID )
4 WHEN MATCHED THEN
5 UPDATE SET A.SUBSCRIBER_ID = 1,
6 A.APP_USERNAME=B.SAP_ID,
7 A.APP_PWD=B.MGR_PW
8 WHEN NOT MATCHED THEN
9 INSERT (A.SUBSCRIBER_ID,A.APP_ID,A.SSO_USERNAME,A.APP_USERNAME,A.APP_PWD)
10 VALUES( 1, B.APP_ID,
11 B.SW_ONE_ID,
12 B.SAP_ID,
13* B.MGR_PW)
you'll have to:
update ( select a.substcriber_id, a.app_username, a.app_pwd, b.sap_id, b.mgr_pw
from WWSSO_APPUSERINFO_T A, T B
where A.SSO_USERNAME = B.SW_ONE_ID AND A.APP_ID = B.APP_ID )
SET SUBSCRIBER_ID = 1,
APP_USERNAME=SAP_ID,
APP_PWD=MGR_PW
/
insert into wwsso_appuserinfo
select 1, B.APP_ID, B.SW_ONE_ID, B.SAP_ID, B.MGR_PW
from t b
where (sw_one_id, app_id) not in ( select sso_username, app_id
from wwsso_appuserinfo )
/
to avoid the row by row processing.
Nitin, April 13, 2004 - 3:42 pm UTC
Thanx, Tom.
I am getting ORA-01799 now.
ORASSO@iasdb >ed
Wrote file afiedt.buf
1 update ( select a.subscriber_id, a.app_username, a.app_pwd, b.sap_id, b.mgr
_pw
2 from WWSSO_APPUSERINFO_T A, T B
3 where A.SSO_USERNAME = B.SW_ONE_ID AND A.APP_ID = B.APP_ID )
4 SET SUBSCRIBER_ID = 1,
5 APP_USERNAME=SAP_ID,
6* APP_PWD=MGR_PW
ORASSO@iasdb >/
SET SUBSCRIBER_ID = 1,
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Elapsed: 00:00:00.00
April 13, 2004 - 6:44 pm UTC
you need a primary key on t(sw_one_id,app_id) (or unique constraint)
How do you call this proc correctly?
A reader, July 01, 2004 - 10:38 am UTC
Similar to above, this proc inserts into wsso_appuserinfo$.
In calling this proc, it comes back with:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "ORASSO.WWERR_API_ERROR_UI", line 346
ORA-06512: at "ORASSO.WWSSO_APP_ADMIN", line 3874
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
Here is the proc declaration:
procedure login_fappuser
(
p_app_id in varchar2 default null -- param mismatch fix
,p_app_name in varchar2 default null
,p_app_username in varchar2 default null
,p_app_pwd in varchar2 default null
,p_remember_credentials in varchar2 default null
,p_fname in wwsec_api.array default wwsec_api.empty
,p_fval in wwsec_api.array default wwsec_api.empty
,p_fhidden in wwsec_api.array default wwsec_api.empty
,p_request in varchar2 default null
,p_action in varchar2 default null
,p_done_url in varchar2 default null
,p_url_name in varchar2 default null
,p_url_value in varchar2 default null
);
declare
x wwsec_api.array;
y wwsec_api.array;
z wwsec_api.array;
begin
--x := wwsec_api.empty;
--y := wwsec_api.empty;
--z := wwsec_api.empty;
x(1) := 'External App';
y(1) := 'username';
z(1) := 'userpass';
WWSSO_APP_ADMIN.login_fappuser('6BB3F8D23D94242298CA9401B87229A7', 'External App', 'username', 'userpass', 'Y', x, y, z, 'Login', null, null, null, null);
end;
/
Can you help me figure out what I'm doing wrong.
July 01, 2004 - 11:33 am UTC
are you calling this from the web or sqlplus? if not via the web, the owa_util package cannot work -- nothing in it. the initialization routines haven't been invoked.
you might be able to work around this by calling:
declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'WEB_AUTHENT_PREFIX';
vl(1) := 'WEB$';
owa.init_cgi_env( nm.count, nm, vl );
end;
/
and setting up a dummy cgi environment before running your procedure in sqlplus.
IT Worked!
A reader, July 01, 2004 - 12:05 pm UTC
You are a total genius.
Indeed I am calling it from sqlplus, and not from the web.
sql> declare
2 nm owa.vc_arr;
3 vl owa.vc_arr;
4 begin
5 nm(1) := 'WEB_AUTHENT_PREFIX';
6 vl(1) := 'WEB$';
7 owa.init_cgi_env( nm.count, nm, vl );
8
9 WWSSO_APP_ADMIN.login_fappuser( p_app_id => '6BB3F8D23D94242298CA9401B87229A7'
10 , p_app_name => 'External App'
11 , p_app_username => 'username'
12 , p_app_pwd => 'password'
13 , p_remember_credentials => 'Y');
14 end;
15 /
PL/SQL procedure successfully completed.
The only problem is it "worked", but it didn't do what I expected it to do.
I am trying to call WWSSO_APP_ADMIN.login_fappuser manually to insert into this table:
sql> desc WWSSO_APPUSERINFO_T;
Name Null? Type
--------------------------------------- -------- -------
SUBSCRIBER_ID NOT NULL NUMBER
SSO_USERNAME NOT NULL VARCHAR2(256)
APP_ID NOT NULL VARCHAR2(80)
APP_USERNAME VARCHAR2(512)
APP_PWD VARCHAR2(512)
USER_FIELDS EXTRAFIELDSTYPE
USER_PREFS VARCHAR2(1000)
so that our External Application (defined in SSO) doesn't prompt us with a second username and password screen when the user logs in for the first time. But even though the procedure completed successfully, it didn't insert the row.
as I understand, wwsso_app_admin.login_fappuser is going to pump out encrypted values for the APP_USERNAME and APP_PWD fields. That is what I ultimately needs.
orasso.wwsso_app_admin.login_fappuser is all wrapped code, so I can't tell what it does, or how it inserts etc.
Any help on this one would be *so* appreciated!
July 01, 2004 - 7:44 pm UTC
sorry, haven't touched this stuff myself.
Ok..
A reader, July 08, 2004 - 10:17 am UTC
Is there a way that I can manually generate the encrypted values that that procedure inserts into WWSSO_APPUSERINFO_T?
sql> desc WWSSO_APPUSERINFO_T;
Name Null? Type
--------------------------------------- -------- -------
SUBSCRIBER_ID NOT NULL NUMBER
SSO_USERNAME NOT NULL VARCHAR2(256)
APP_ID NOT NULL VARCHAR2(80)
APP_USERNAME VARCHAR2(512)
APP_PWD VARCHAR2(512)
USER_FIELDS EXTRAFIELDSTYPE
USER_PREFS VARCHAR2(1000)
The procedure I mention above inserts encrypted values into this table. You enter a username and a password at a login screen, and it encrypts those values. Here are examples of those values:
SSO_USERNAME APP_USERNAME APP_PWD
=============== ================================= ================================
BYRON 924F7ABD2C1875DB9DF7B5A772FFB487 DC579E03787FE031CE9AC4FA96A61C26
GLENN 8AE78C70561A789E3B232A0193F3AB0D 162BB1D35EF06DB554654CE8EF341B26
MATT 16910049EE017A896992E52A2D8541A5 2A5F53F507DEB8F5FD1CFD329B8E0910
MAX B8D31002805CA187B8FC08E91D9C982D 51877EA59D24C665DD3687C4F274A7BB
STEVE 0B8AC3BBA4B39A196C85E0358AAEEB37 9AACC15E83B2433CAB9B805AA7695B13
Can you tell me what Oracle program is used to generate these encrypted values, and maybe show me how to use it?
I would think it wouldn't be too hard, but I don't know.
July 08, 2004 - 11:43 am UTC
I don't know if they are encrypted or hashed. but no, i do not know of a way to generate them.
well.. I found it
A reader, July 09, 2004 - 9:45 am UTC
sql> select orasso.wwsso_utl.encrypt_ps_username('username') from dual;
ORASSO.WWSSO_UTL.ENCRYPT_PS_USERNAME('USERNAME')
=======================================================================
328834B26DC287F3C9D76FD2412A8BDD10461832CB416B95
sql> select orasso.wwsso_utl.encrypt_ps_password('password') from dual;
ORASSO.WWSSO_UTL.ENCRYPT_PS_PASSWORD('PASSWORD')
=======================================================================
72D0991EA19C0D5C4A0F17BDB6E9F1D7377B4B735B1FA521
sql> select orasso.wwsso_utl.decrypt_ps_username('328834B26DC287F3C9D76FD2412A8BDD10461832CB416B95') from dual;
ORASSO.WWSSO_UTL.DECRYPT_PS_USERNAME('328834B26DC287F3C9D76FD2412A8BDD10461832CB416B95')
===============================================================================================
username
sql> select orasso.wwsso_utl.decrypt_ps_password('72D0991EA19C0D5C4A0F17BDB6E9F1D7377B4B735B1FA521') from dual;
ORASSO.WWSSO_UTL.DECRYPT_PS_PASSWORD('72D0991EA19C0D5C4A0F17BDB6E9F1D7377B4B735B1FA521')
===============================================================================================================
password
Good stuff!
Merge within FORALL in 9i r2
sudip, July 11, 2004 - 2:15 am UTC
Sir,
Are there any plans of supporting MERGE within FORALL calls.
I tried using that , the procedure compiled, but while execution, it throws internal error.
On searching I found that this feature is not supported.
</code>
http://www.oreillynet.com/pub/a/network/2003/04/22/feuerstein.html?page=last <code>
"(Ideally we'd use MERGE with a RECORD bind, but this isn't supported yet.)"
Is this feature going to come/or has already come in 10g release.
The work around that I have is to create a GTT with on commit delete and use merge . Is there any better approach.
Thanks!!
July 11, 2004 - 9:45 am UTC
show me a logical example of where it would make sense to forall i a merge -- a statement that takes set A and merges with set B.
you have two sets, you merge.
so where does the array come into play.
If I had a collection of objects -- instead of an array of records, we would just:
merge into t1
using ( select * from table( your_collection_variable ) ) t2
on (....)
the capability already exists and does not necessitate or desire a forall construct
Performance
Dave, July 14, 2004 - 12:13 pm UTC
I know this is a bit vague but ill ask anyway.
How does merge scale upwards when you are updating for example a 10 million rows with a million rows? where the merge is being done on the primary key and is a 5 column table.
Does merge scale to high volumes is my basic question, both on the base table and the table being merged from
I would test it out but dont have the data right by the environment right now
July 14, 2004 - 12:18 pm UTC
merge scales better than nested loops in that context, not nearly as well as a hash join though.
merge PK violatiion
jianhui, July 19, 2004 - 10:05 am UTC
Tom,
The following merge statement runs into error saying "ORA-00001: unique constraint (table_name.constraint_name) violated. The constraint reported here is the PK on the target table. I checked both source and target tables and both PKs are enabled, also did a "select count(distinct)", how could this happen? The merge is based on 4 PK columns on both tables, besides PK is the only unique constraint both tables have.
MERGE /*+ NOLOGGING PARALLEL */ INTO target a
USING source b
ON
(
a.pk_col1 = b.pk_col1
AND a.pk_col2 = b.pk_col2
AND a.pk_col3 = b.pk_col3
AND a.pk_col4 = b.pk_col4
)
WHEN
MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
July 19, 2004 - 10:29 am UTC
NOLOGGING is not a hint -- it is meaningless in this context.
do you have the "real" example -- all of it. a cut and paste from sqlplus including the error message would be best (i want to see the *real* merge statement, no edits)
merge unique constraint violation
jianhui, July 19, 2004 - 4:11 pm UTC
Tom,
For the latest MERGE question, we ran the same statement in DEV environment and it's ok, but failed in QA. It seems to me it's the data related error, but since the merge condition is based on PK, theoratically i can not figure out how it could happen. Sorry for not able to post the exact SQL because of privacy concern. Could you just guess some possibilities that make this happen based on the scenario i described, so i can do a further check of our system. It's a data warehouse load process, we enable and disable constraints during the load process, could this be a direction to investigate?
July 19, 2004 - 4:57 pm UTC
the only conclusion I can come to is "there really is duplicate data". if i cannot see the statement I cannot say anything else.
If you believe this to be an error on our part, please file a tar with support -- hopefully they'll be permitted to see stuff.
possible issue is bug: 2719742, you can reference that with support.
are the datatypes of the tables primary keys *the same*?
nice guesses
Jianhui, July 20, 2004 - 10:03 am UTC
Tom,
Thank you for the guesses. We did a
SELECT count(*) FROM
(SELECT DISTINCT pk_col1, pk_col2, pk_col3, pk_col4)
FROM source_table)
no duplicated keys. datatype and name of both source and target tables are identical. We'll file a tar.
Best,
July 20, 2004 - 8:12 pm UTC
can you tell me if there is by any chance active dml on that table? (from another session?)
bug 2719742
Jianhui, July 22, 2004 - 6:16 pm UTC
Tom,
I reviewed bug 2719742 and it's possible there are some active sessions updating the same target table. But our version is 9.2.0.5 RAC on LINUX, we 'll do further investigation with the support personals. Thanks a lot!
merge using gtt as source table
Peter Tran, August 06, 2004 - 12:47 pm UTC
Hi Tom,
I have a GTT (delete on commit) as a source table. I bulk insert into the gtt and invoke merge to get the data over to the target table.
Here's the explain plan:
MERGE STATEMENT
MERGE
VIEW
NESTED LOOPS [OUTER]
ODDV11.ODFRC_GTT TABLE ACCESS [FULL]
PARTITION RANGE [ITERATOR]
ODDV11.ODFRC_PK INDEX [FAST FULL SCAN]
Now if I have a lot of parallel sessions doing the same thing, is the FTS going to be a overhead because each session will have to scan up to the HWM or will Oracle be smart enough to only scan the section of the data that is allocated to that session?
Thanks,
-Peter
August 06, 2004 - 1:26 pm UTC
each user gets their OWN unique segment -- in fact, since each user can have a different temp -- their temp segments may well be in different tablespaces.
Merging into multiple tables
A reader, August 12, 2004 - 1:42 pm UTC
I have a large query returning many rows that I would like to MERGE into multiple tables (based on 1 PK column)
Is this possible?
Something like
WITH (my_query)
MERGE INTO t1 using my_query on ....
MERGE INTO t2 using my_query on ....
Any other way to do this?
Thanks
August 12, 2004 - 1:44 pm UTC
nope, if my_query is hugely expensive to compute -- use a global temporary table to stash/cache the result.
Alternative to UPDATE a join
Basil, November 23, 2004 - 12:28 pm UTC
Regarding Nitin's question above, another solution is to use a temporary table.
What I did was to create a temporary table with the same structure as the table with a policy. I copy the data to the temporary table, then apply the MERGE to the temporary table followed by any other processing, then delete the original data, then insert the data back from the temporary table.
Testing locally revealed it to be fairly quick (as it's an all-SQL solution). Additionally, it was less-disruptive to our code base, as we could use the same (dynamically generated) MERGE we had been using.
I'm currently trying to convince the Metalink representative to open a documentation bug on this. It's a shame that this basic incompatibility between MERGE and FGAC security policies is documented in exactly one place: the text of ORA-28132. It really should be mentioned in both MERGE and FGAC documentation. This cost us several days' worth of work that were not accounted for in the schedule.
Also, and a bit incidentally, updating a join will not work if a security policy is in place on the key-preserved table and the join is an OUTER JOIN. It works fine if I drop the policy.)
November 23, 2004 - 1:50 pm UTC
woah -- think about that for a minute.....
q) what data can you see? all of it? or some of it?
a) with FGAC, some of it
so when you "copy the data", "merge into copy", "delete original", "copy back"
you have implicitly "deleted everything you were NOT allowed to see"
This is for your protection here -- think about the semnatics of what is going on. FGAC hides rows -- you merge -- you merge with data you cannot see, it inserts. It is just a basic incompatibility between what you've done and what you want to do.
Perhaps you wanted to (for the duration of the merge, obviously this must be "batch" since you delete and reinsert everything) remove access from table, DROP the policy, merge to refresh, add the policy.
otherwise, my answer stands -- update, insert. your approach will tend to "lose" data automagically depending on who runs it.
Alternative to UPDATE a join 2
Basil, December 13, 2004 - 2:44 pm UTC
With all due respect, I thought the point of FGAC was to implement a virtual private database. I don't care about any data other than that currently visible to me. When I SELECT, I should only get data visible to me, when I MERGE, I should only be UPDATEing or INSERTing data that is visible to me.
What I've done is taken a snapshot of the rows I can see, work with them, delete those rows I can see from the source, and put the modifications back. This shouldn't disrupt anyone else, since all deletions, updates, inserts, and merges are all done with policies in force.
Yes, this is all batch processing in a data warehouse environment. We had hoped (based on preliminary tests and the documentation) to use FGAC to avoid rewriting major chunks of the application. This is not working out as well as intended.
Today I find out that multitable insert is also incompatible with FGAC. What I'm finding out more day by day is that FGAC isn't very good for data warehouse type applications, even when they're shared by users who shouldn't see each other's information.
December 13, 2004 - 2:52 pm UTC
perhaps you want to
a) use base tables for your ETL processing
b) expose simple "select * from" views
c) FGAC the views
d) query the views, not the base tables.
in that way, you have full FGAC for your end user queries and no impediments for your data loads.
MERGE is not using any indexes
Praveen, January 07, 2005 - 8:35 am UTC
Hi Tom,
I tried an example as below to see the difference in
performance while using a merge statement on tables with
and without indexes on them.
create table t1 (id integer, val integer);
create index idx_t1 on t1 (id);
create table t2 (id integer, val integer);
create index idx_t2 on t2 (id);
create table t3 (id integer, val integer);
create index idx_t2 on t2 (id);
insert into t1 values(1, 10);
insert into t1 values(2, 20);
insert into t1 values(3, 30);
insert into t1 values(4, 40);
insert into t2 values(1, 50);
insert into t2 values(2, 60);
insert into t2 values(3, 70);
insert into t3 values (3, 80);
insert into t3 values (4, 90);
commit;
MERGE INTO t3
USING (SELECT t1.id, t1.val
FROM t1, t2 WHERE t1.id = t2.id) t
ON (a.id = t.id)
WHEN MATCHED THEN
UPDATE SET val = t.val
WHEN NOT MATCHED THEN
INSERT (id, val)
VALUES (t.id, t.val);
Below is the execution plan:
MERGE STATEMENT Hing=CHOOSE
MERGE T3
VIEW
HASH JOIN OUTER
HASH JOIN
TABLE ACCESS FULL T1
TABLE ACCESS FULL T2
TABLE ACCESS FULL T3
The query is not using the indexes at all on the tables!
This is the same execution plan generated when the query is
executed on the same tables, but without indexes.
In one of the reviews above I saw a similar query using the
indexes properly. Do I need to setup anything (like, any
session parameters before issuing a MERGE statement)?
Also as per the execution plan, the query is using a HASH
OUTER JOIN. I could not see why exactly an OUTER JOIN is
required, in a query like above.
How can I rewrite the query so that it uses the right
indexes?
The assumption is there are more updates than inserts.
In this case, I assume, use of proper indexes on table t3
might fast up the process.
Could you please give me some more insight into this
problem?
Thanks and regards.
Praveen.
January 07, 2005 - 9:35 am UTC
thank goodness it did not!!! you wouldn't want it to go slow now would you?
repeat this over and over and over until you actually talk yourself into believing it:
loop
full scans are NOT evil
indexes are not goodness incarnate
end loop
Look at your query:
MERGE INTO t3
USING (SELECT t1.id, t1.val
FROM t1, t2 WHERE t1.id = t2.id) t
ON (a.id = t.id)
WHEN MATCHED THEN
UPDATE SET val = t.val
WHEN NOT MATCHED THEN
INSERT (id, val)
VALUES (t.id, t.val);
Ok, looking at just this:
USING (SELECT t1.id, t1.val
FROM t1, t2 WHERE t1.id = t2.id) t
why on earth would you want to use an index?
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>
You are going to pretty much look at every row in T1 (or T2) and join it to some rows in T2 (or T1). Using an index would be *painful* here (you need to get the LAST ROW out of that query before we can start doing stuff). Big juicy, brutally efficient HASH JOINS to the rescue!
and then we take this and join it to the table to be merged into. Again *every row* is going to be hit. HASH joins rule!
Upsert Upset!
Andy, June 07, 2006 - 12:23 pm UTC
Tom
I have a package with 3 routines, for building INSERT, UPDATE and UPSERT statements.
The insert and update routine is based on your DBMS_SQL bind variable parsing routine in a previous post.
The upsert merely calls the same insert routine, and on the DUP_VAL_ON_INDEX exception it calls the update routine.
My tests for the insert and update works as expected.
SQL_TEXT EXECUTIONS PARSE_CALLS ExecsPerParseCall
INSERT INTO table1 ( col1, col2, col3)
VALUES ( :bv1, :bv2, :bv3) 99 1 0.0101
UPDATE table1 SET col2 = :bv2, col3 = :bv3
WHERE col1 = :bv1 99 1 0.0101
However, my upsert doesn't produce the same results. After the UPSERT, the results are now:
SQL_TEXT EXECUTIONS PARSE_CALLS ExecsPerParseCall
INSERT INTO table1 ( col1, col2, col3)
VALUES ( :bv1, :bv2, :bv3) 198 100 0.50505
UPDATE table1 SET col2 = :bv2, col3 = :bv3
WHERE col1 = :bv1 198 100 0.50505
What is wrong here? Surely calling the same insert and update routines from the upsert routine should be work? Or is this caused by the DUP_VAL_ON_INDEX exception?
June 07, 2006 - 3:23 pm UTC
without seeing your logic, we cannot ascertain why YOU are parsing 100 times (we parse when you tell us to - look to your logic!)
Upsert Upset
Andy, June 08, 2006 - 5:11 am UTC
Tom
OK, my package is defined as:
CREATE OR REPLACE PACKAGE Test_Pkg
AS
PROCEDURE process_data (
p_pkt_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_key_fld_count IN NUMBER,
p_cols IN VARCHAR2,
p_vals IN VARCHAR2
);
END Test_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Test_Pkg
AS
-- DECLARATIONS
TYPE ARRAY IS TABLE OF VARCHAR2 (4000);
c_delim CONSTANT CHAR (1) := CHR (1);
m_last_stmt LONG;
m_cursor NUMBER := DBMS_SQL.OPEN_CURSOR;
-- LOCAL PROCS
PROCEDURE P (p_str IN VARCHAR2)
IS
l_str LONG := p_str;
BEGIN
LOOP
EXIT WHEN l_str IS NULL;
DBMS_OUTPUT.PUT_LINE (SUBSTR (l_str, 1, 250));
l_str := SUBSTR (l_str, 251);
END LOOP;
END;
FUNCTION str2tbl (p_str IN VARCHAR2)
RETURN ARRAY
AS
l_str LONG DEFAULT p_str || c_delim;
l_n NUMBER;
l_data ARRAY := ARRAY ();
BEGIN
LOOP
l_n := INSTR (l_str, c_delim);
EXIT WHEN (NVL (l_n, 0) = 0);
l_data.EXTEND;
l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_str, 1, l_n - 1)));
l_str := SUBSTR (l_str, l_n + 1);
END LOOP;
RETURN l_data;
END;
-- MAIN
PROCEDURE ins (
p_table_name IN VARCHAR2,
p_key_fld_count IN NUMBER,
p_cols IN VARCHAR2,
p_vals IN VARCHAR2
)
IS
/**************************************
INSERT routine
**************************************/
l_stmt LONG;
l_rc NUMBER;
l_cols ARRAY := str2tbl (p_cols);
l_vals ARRAY := str2tbl (p_vals);
BEGIN
l_stmt := 'insert into ' || p_table_name || ' ( ' || l_cols (1);
FOR i IN 2 .. l_cols.COUNT
LOOP
l_stmt := l_stmt || ', ' || l_cols (i);
END LOOP;
l_stmt := l_stmt || ') values ( :bv1';
FOR i IN 2 .. l_cols.COUNT
LOOP
l_stmt := l_stmt || ', :bv' || i;
END LOOP;
l_stmt := l_stmt || ')';
IF (l_stmt <> m_last_stmt OR m_last_stmt IS NULL) THEN
P ( l_stmt);
m_last_stmt := l_stmt;
DBMS_SQL.PARSE (m_cursor, m_last_stmt, DBMS_SQL.native);
END IF;
FOR i IN 1 .. l_vals.COUNT
LOOP
DBMS_SQL.BIND_VARIABLE (m_cursor, ':bv' || i, l_vals (i));
END LOOP;
l_rc := DBMS_SQL.EXECUTE (m_cursor);
END ins;
PROCEDURE upd (
p_table_name IN VARCHAR2,
p_key_fld_count IN NUMBER,
p_cols IN VARCHAR2,
p_vals IN VARCHAR2
)
IS
/**************************************
UPDATE routine
**************************************/
l_stmt LONG;
l_rc NUMBER;
l_cols ARRAY := str2tbl (p_cols);
l_vals ARRAY := str2tbl (p_vals);
BEGIN
l_stmt :=
'update '
|| p_table_name
|| ' set '
|| l_cols (p_key_fld_count + 1)
|| ' = :bv'
|| (p_key_fld_count + 1);
FOR i IN (p_key_fld_count + 2) .. l_cols.COUNT
LOOP
l_stmt := l_stmt || ', ' || l_cols (i) || ' = :bv' || i;
END LOOP;
l_stmt := l_stmt || ' where ' || l_cols (1) || ' = :bv1';
FOR i IN 2 .. (p_key_fld_count)
LOOP
l_stmt := l_stmt || ' and ' || l_cols (i) || ' = :bv' || i;
END LOOP;
IF (l_stmt <> m_last_stmt OR m_last_stmt IS NULL) THEN
P ( l_stmt);
m_last_stmt := l_stmt;
DBMS_SQL.PARSE (m_cursor, m_last_stmt, DBMS_SQL.native);
END IF;
FOR i IN (p_key_fld_count + 1) .. l_vals.COUNT
LOOP
DBMS_SQL.BIND_VARIABLE (m_cursor, ':bv' || i, l_vals (i));
END LOOP;
FOR i IN 1 .. (p_key_fld_count)
LOOP
DBMS_SQL.BIND_VARIABLE (m_cursor, ':bv' || i, l_vals (i));
END LOOP;
l_rc := DBMS_SQL.EXECUTE (m_cursor);
END upd;
PROCEDURE ups (
p_table_name IN VARCHAR2,
p_key_fld_count IN NUMBER,
p_cols IN VARCHAR2,
p_vals IN VARCHAR2
)
IS
/**************************************
UPSERT routine (Update or Insert)
**************************************/
BEGIN
<<UPSERT>>
BEGIN
ins (p_table_name, p_key_fld_count, p_cols, p_vals);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
--duplicate, so update
upd (p_table_name, p_key_fld_count, p_cols, p_vals);
WHEN OTHERS THEN
RAISE;
END UPSERT;
END ups;
PROCEDURE process_data (
p_pkt_type IN VARCHAR2,
p_table_name IN VARCHAR2,
p_key_fld_count IN NUMBER,
p_cols IN VARCHAR2,
p_vals IN VARCHAR2
)
IS
BEGIN
CASE UPPER (p_pkt_type)
WHEN 'INS' THEN
ins (p_table_name, p_key_fld_count, p_cols, p_vals);
WHEN 'UPD' THEN
upd (p_table_name, p_key_fld_count, p_cols, p_vals);
WHEN 'UPS' THEN
ups (p_table_name, p_key_fld_count, p_cols, p_vals);
ELSE
RAISE_APPLICATION_ERROR (-20001, 'INVALID PACKET TYPE');
END CASE;
END process_data;
END Test_Pkg;
/
Here is my test:
SQL> DROP TABLE TABLE1
Table dropped.
SQL> CREATE TABLE TABLE1(
col1 NUMBER,
col2 VARCHAR2(10),
col3 VARCHAR2(10),
CONSTRAINT pk_table1 PRIMARY KEY (col1))
Table created.
SQL> ALTER SYSTEM FLUSH SHARED_POOL
System switch log altered.
SQL> SELECT DISTINCT
vs.sql_text, vs.executions, vs.parse_calls
FROM v$sqlarea vs
WHERE (UPPER(vs.sql_text) LIKE 'INSERT%TABLE1%'
OR UPPER(vs.sql_text) LIKE 'UPDATE%TABLE1%')
AND vs.parsing_user_id = 27
no rows selected
SQL> -- INSERT and UPDATE tests
SQL> DECLARE
X NUMBER;
c_delim CONSTANT CHAR(1) := CHR(1); --my delimiter
BEGIN
FOR X IN 1..99
LOOP
Test_Pkg.process_data(
'INS','table1',1, 'col1' || c_delim || 'col2' || c_delim || 'col3',
X || c_delim || 'Test rec' || X || c_delim || 'A');
END LOOP;
FOR X IN 1..99
LOOP
Test_Pkg.process_data(
'UPD','table1',1, 'col1' || c_delim || 'col2' || c_delim || 'col3',
X || c_delim || 'Test rec' || X || c_delim || 'B');
END LOOP;
END;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT
vs.sql_text, vs.executions, vs.parse_calls
FROM v$sqlarea vs
WHERE (UPPER(vs.sql_text) LIKE 'INSERT%TABLE1%'
OR UPPER(vs.sql_text) LIKE 'UPDATE%TABLE1%')
AND vs.parsing_user_id = 27
SQL_TEXT EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
insert into table1 ( col1, col 99 1
2, col3) values ( :bv1, :bv2,
:bv3)
update table1 set col2 = :bv2, 99 1
col3 = :bv3 where col1 = :bv1
2 rows selected.
SQL> -- UPSERT test
SQL> DECLARE
X NUMBER;
c_delim CONSTANT CHAR(1) := CHR(1); --my delimiter
BEGIN
FOR X IN 1..99
LOOP
Test_Pkg.process_data(
'UPS','table1',1, 'col1' || c_delim || 'col2' || c_delim || 'col3',
X || c_delim || 'Test rec' || X || c_delim || 'C');
END LOOP;
END;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT
vs.sql_text, vs.executions, vs.parse_calls
FROM v$sqlarea vs
WHERE (UPPER(vs.sql_text) LIKE 'INSERT%TABLE1%'
OR UPPER(vs.sql_text) LIKE 'UPDATE%TABLE1%')
AND vs.parsing_user_id = 27
SQL_TEXT EXECUTIONS PARSE_CALLS
------------------------------ ---------- -----------
insert into table1 ( col1, col 198 100
2, col3) values ( :bv1, :bv2,
:bv3)
update table1 set col2 = :bv2, 198 100
col3 = :bv3 where col1 = :bv1
2 rows selected.
I would expect executions = 198 and parse_calls = 1. I'm reusing the same code!
June 08, 2006 - 9:15 am UTC
do you see your logic....
You have ONE cursor.
You need to do TWO different statements on your "upsert"
anytime you have to do the update, you "unparse" the insert and parse the update.
Then, you try to insert the next time.
tell you want, put a tiny bit of debug in there, when you parse, dbms_output and put out the last statement and current statement and you'll see your program flow.
(I hate this dynamic stuff, still think you should just use SQL and static SQL at that)
Upsert Upset
Andy, June 09, 2006 - 4:40 am UTC
Thanks.
In this post: </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6711305251199#6775345875208 <code>
you created a package called dyn_insert.
That solution appeared to fit our needs, which is to build an updater app which builds all DML for the system, based (basically) on table names, columns and values. We wanted to build all the DML in a package, something like my listing above.
Unfortunately, the DBMS_SQL.PARSE introduces another level to the parsing mechanism in Oracle, the results of which we can see above - pretty much a parse per execution, which is not good.
We thought about building the DML in the app (VB), but I suspect we'll get exactly the same problem: parsing occuring for each SQL statement.
I have to say, I thought all the parsing was controlled within Oracle's own admin of the SGA, not merely from the fact that it is being executed. I HAD thought that SQL is parsed in the SGA, executed, and wouldn't need to be parsed again if it was already loaded. What is happening in the above test is each time I call DBMS_SQL.PARSE, it explicitly parses the SQL. Every time (as long as m_last_stmt changes, that is).
From your response above, you advised only hard-coding the SQL in a stored proc - indeed, I have tested this, and it does indeed work (high execs, low parses).
Is this method really the only way we can get optimum performance in Oracle? It looks like there is a downside to the flexible method you suggested in using dyn_insert - 1 parse per execution. Is that the penalty for flexibility?
June 09, 2006 - 6:46 am UTC
In computer life - you will find the following to be an almost universal truth:
the more generic you get - the less performant you are, the more resources you consume..
the more specific you are - the more performant you are, and you'll find you use less resources over time.
You could write your DML package to cache perfectly - it would become more sophisticated. You would have an array of cursors - perhaps indexed by your sql statements - look up to see if the statement is cached, reusing that or parsing new...
You might want to peek at:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2588723819082 <code>
Upsert Upset
Andy, June 09, 2006 - 5:19 am UTC
Upsert Upset
Andy, June 09, 2006 - 6:30 am UTC
Actually, may not have found the answer!
That post is for many inserts in a loop. Our app is sending inserts, and 1,2,..n number of updated columns.
That would result in n!(factorial) combinations of static SQL! Don't fancy that, personally!
Therefore, I need:
* Dynamic SQL
* Parsed once, executed x times
Can I achieve this? Or, is my TEST_PKG above as good as I'm going to get?
June 09, 2006 - 6:47 am UTC
see discussion above... You'd have to make it more sophisticated, implement an array of cursors, cache them "open"
Merge into with variables
RD, July 31, 2007 - 11:02 am UTC
Tom,
As per your suggestion i used dynamic sql as below.
for rec in ( select c1 , c2 from table1 )
loop
execute immediate '
merge into table2
using ( select '''||rec.c1||''' c1
, '''||rec.c2||''' c2
from dual
) temp
on ( table2.col1= temp.c1 and table2.col2= temp.c2 )
when matched then
update set col3= ''xyz''
when not matched then
insert ( col1, col2, col3)
values ( temp.c1,temp.c2, ''xyz'' )
';
end loop;
Request you to suggest any better ways to accomplish the same.
TIA
RD
August 02, 2007 - 10:03 am UTC
as per what suggestion??????
you need NO dynamic sql for that.
In fact, you need no procedural code for that.
merge into table2
using (select c1, c2 from table1 ) table1
on (table2.col1 = table1.col1 and table2.col2 = table1.col2)
when matched then set col3 = 'xyz'
when not matched then insert (col1, col2, col3) values ( table1.col1, table1.col2, 'xyz' );
static sql, no code
Merge into with variables
RD, August 01, 2007 - 10:49 am UTC
Hi TOM,
I did not mention before.. but i will have to use variables .. i represnt my code differently as below.
Please help me in finding the best way.
1) Can i use static SQL
2) If dynamic sql is execute immediate OK.
From other posts i realize that using dbms_sql avoid multiple parsings.
declare
v_c10 pls_integer;
v_c20 pls_integer;
begin
for rec in ( select c1 , c2, c3 from table1 )
loop
select c10
into v_c10
from table3
where table3.c11 =rec.c1;
select c20
into v_c20
from table4
where v_c10 between table4.c21 and table4.c22;
execute immediate '
merge into table2
using ( select '''||rec.c1||''' c1
, '''||rec.c2||''' c2
from dual
) temp
on ( table2.col1= temp.c1 and table2.col2= temp.c2 )
when matched then
update set col3= :v_c20
when not matched then
insert ( col1, col2, col3)
values ( temp.c1,temp.c2, :v_c20 )
' using v_c20, v_c20
;
end loop;
end;
Thanks in advance,
RD
August 05, 2007 - 10:05 am UTC
stop it, stop NOT USING BIND VARIABLES
stop using string concatenation.
You do not have to use dbms_sql
You do not have to use dynamic sql at all
You do not have to do this slow by slow, please - just join, use sql - do it right.
give me creates for table1..4 (MINIMAL CREATES WE DO NOT NEED 20 columns for example)
and explain the meaning of the query against table4 - which appears random in your example - and I'll do this in a single merge, no problem whatsoever.
RD from sweden - Use a Table Join?
Tyler, August 03, 2007 - 8:21 am UTC
This should do what your loop / dynamic code is doing now, since you didn't provide create table scripts, it's not been tested, but should be logically equivalent...
merge into table2
using (
select T1.c1 , T1.c2, T4.c20
from table1 T1, table3 T3, table4 T4
where T3.c11 = T1.c1
and T3.c10 between T4.c21 and T4.c22
) temp
on ( table2.col1= temp.c1 and table2.col2= temp.c2 )
when matched then
update set col3 = temp.c20
when not matched then
insert ( col1, col2, col3)
values ( temp.c1, temp.c2, temp.c20 );
Merge into with variables
rd, August 17, 2007 - 4:51 am UTC
Hello TOM,
I think i should give the full picture of my case.
Please find below the code as it is.
Request you to make suggestions.
Please let me know if i should come up with creates for the tables involved.
Note that transactions is a huge table.
Thanks in advance,
RD
############ Proceedure starts here ######
procedure ACCOUNT_PROGRAM_PERIOD
is
v_max_app_id pls_integer := pkg_manage_loads.func_get_max_seq_num('ACCOUNT_PROGRAM_PERIOD');
v_period_spend number;
v_currrent_target number;
v_app_id_counter pls_integer:=1;
begin
for rec in (
select rownum rno
, ap.account_number
, ap.program_id
, per.period_id
, per.start_date
, per.end_date
, per.insert_date period_insert_date
from ACCOUNT_PROGRAM ap
, PERIOD per
, PERIOD_RULE pr
, START_EVENT_TYPE et
where ap.program_id=per.program_id
and ( start_event_operator='New assignment'
and ap.assign_date = per.start_date
or start_event_operator!='New assignment'
and ap.assign_date <= per.end_date
and nvl(per.close_date,trunc(sysdate)) >= trunc(sysdate-1)
)
and ap.program_id=pr.program_id
and pr.start_event_type_id=et.start_event_type_id
)
loop
select nvl( sum(trans_amt), 0)
into v_period_spend
from transactions mit
where mit.acct_num= rec.account_number
and mit.trans_date between rec.START_DATE and rec.END_DATE
and mit.trans_tp in ( '623', '626', '625', '627') ;
if v_period_spend >=0 then
begin
select decode( BUCKET_END_VALUE, null, null, BUCKET_END_VALUE+1)
into v_currrent_target
from PROGRAM_REWARD_BUCKET
where program_id = rec.program_id
and v_period_spend between bucket_start_value and nvl(bucket_end_value, 1000000);
execute immediate '
merge into ACCOUNT_PROGRAM_PERIOD app
using ( select '''||rec.account_number||''' account_number
, '''||rec.program_id||''' program_id
, '''||rec.period_id ||''' period_id
from dual
) temp
on ( app.account_number= temp.account_number
and app.program_id= temp. program_id
and app.period_id= temp.period_id )
when matched then
update
set accumulated_in_period= :v_period_spend
, closing_balance= :v_period_spend+ nvl(app.OPENING_BALANCE, 0)
, current_target= :v_currrent_target
, update_date = sysdate
when not matched then
insert
( account_program_period_id
, account_number
, program_id
, period_id
, accumulated_in_period
, closing_balance
, current_target
)
values (
:v_max_app_id + :v_id_counter
, temp.account_number
, temp.program_id
, temp.period_id
, :v_period_spend
, :closing_balance
, :v_currrent_target
)
'
using v_period_spend, v_period_spend, v_currrent_target
, v_max_app_id, v_app_id_counter, v_period_spend
, v_period_spend, v_currrent_target;
v_app_id_counter:= v_app_id_counter+1;
end;
end if;
end loop;
commit;
end ACCOUNT_PROGRAM_PERIOD;
############ Proceedure ends here ######
Merge into - with variables
rd, September 24, 2007 - 10:48 am UTC
Hello TOM,
Can you please respond to my issue as in previous post. I have provided whole code hoping that it gives full picture.
Please let me know if more information is needed for you to be able to look at my problem.
Thanks in advance,
RD
September 26, 2007 - 1:43 pm UTC
you have no issue, you asked for a 'comment'. I had nothing to say.
I do not reverse engineer big blocks of code and re-engineer them. if you have a concrete short question (preferable that it relates to the original thing on the page), I can look at that.
advanced upsert
Ove, September 26, 2007 - 5:51 pm UTC
Hello Tom
I have a special case of upsert where I need to actually perform an Update+Insert in the Update case.
(I "close" the old row and "open" a new one, since we have From_date and To_date on each record)
I have not been able to do this with neither Merge nor Forall, due to their syntax restrictions.
When I perform this using conventional nested loops, well it's just veery very slow and with one million records per night, we might be in for a problem.
I'm currently developing on Oracle 9.2, but we'll be moving to 10g later.
Do you have any suggestions?
September 26, 2007 - 10:13 pm UTC
not due to their syntax restrictions, but rather that "update existing, create new" is not "what is expected"
have you looked at DBMS_WM?
search for that here, the workspace manager might be what you are looking for.
A reader, September 26, 2007 - 6:15 pm UTC
Merge into- with variables
RD, October 02, 2007 - 5:00 am UTC
Hi Tom,
This refers to my query (
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6618304976523#407507700346318262 )
I could not find how i can avoid " string concatenation" in the code below.
The variables "rec." come from driving loop.
´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´
execute immediate '
merge into ACCOUNT_PROGRAM_PERIOD app
using ( select '''||rec.account_number||''' account_number
, '''||rec.program_id||''' program_id
, '''||rec.period_id ||''' period_id
from dual
............
...
...
´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´´
Basically how i can use merge with variables from the driving loop.
Thanks in advance,
RD
October 03, 2007 - 4:42 pm UTC
well, i hate running sql inside of a loop - it usually means "programmer is thinking slow by slow and has forgotten SQL is based on sets"
but...
a) why are you using dynamic sql in the first place???!?!?!??!
b) execute immediate 'merge into ... using (select :a, :b, :c from dual ) ...'
using rec.account_number, rec.program_id, rec.period_id
JUST BIND.
Merge into- with variables
RD, October 04, 2007 - 8:49 am UTC
Thanks much Tom.
Your remarks are very useful for my learning... make me rethink my approach....
a)
i) to commit after 10000 records .. but that pieice is not included in the code i posted .
ii) Table "transactions" is a huge table ...i thought it would take log time to execute ... but now i think time to join the tables can be less than this looping and i try that.
My understanding is, if without loop all updated are committed in one go.
1) No looping means all updates are committed in the end ?
2) Should i be concerned if large updates are happening and commiting is in the end ?
Thanks in advance,
RD
to RD from Sweden
A reader, October 05, 2007 - 8:14 am UTC
Which version of Oracle are you using?
Merge into- with variables
rd, October 05, 2007 - 11:25 am UTC
Oracle9i
Merge statement
Jdam, November 16, 2007 - 10:22 am UTC
Hi Tom,
If the rows on both table exists and the values are the same can the merge statement skip these rows and do nothings
November 21, 2007 - 11:00 am UTC
well, it would update the row (but indexes are not touched, not modified) and it HAS to update the row - in order to LOCK the row - else the entire SEMANTICS of the merge statement are fundamentally changed.
So, no, the row will not be skipped - you need to have it locked in order to have the transactional semantics of the merge be consistent.
Too much!
Stew Ashton, November 20, 2007 - 4:17 pm UTC
Now I don't even have to run the test, since you just did.
But I will anyway :)
Thanks again!
Re: followup to "Merge statement"
Stew Ashton, November 21, 2007 - 11:26 am UTC
Concerning "but indexes are not touched, not modified" : is there a way I can run a test and show that the index is not being modified?
Thanks in advance.
November 20, 2007 - 1:50 pm UTC
ops$tkyte%ORA10GR2> create table t ( x int, y int ) tablespace example;
Table created.
ops$tkyte%ORA10GR2> create index t_id on t(x) tablespace users;
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> alter tablespace users offline;
Tablespace altered.
ops$tkyte%ORA10GR2> update t set x = 1 where y = 1;
1 row updated.
ops$tkyte%ORA10GR2> update t set x = 2 where y = 1;
update t set x = 2 where y = 1
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4:
'/home/ora10gr2/oracle/product/10.2.0/oradata/ora10gr2/users01.dbf'
ops$tkyte%ORA10GR2> alter tablespace users online;
Tablespace altered.
and a tkprof would show increased IO when you actually change the value as well:
ops$tkyte%ORA10GR2> alter session set sql_trace=true;
Session altered.
ops$tkyte%ORA10GR2> update t set x = 1 where y = 1;
1 row updated.
ops$tkyte%ORA10GR2> update t set x = 2 where y = 1;
1 row updated.
update t set x = 1 where y = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.05 0 7 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.06 0 8 3 1
********************************************************************************
update t set x = 2 where y = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.02 0 7 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.03 0 8 5 1
increased current mode gets to modify/maintain index.
Ok, just realized that technically, that doesn't show it for a merge :)
ops$tkyte%ORA10GR2> alter tablespace users offline;
Tablespace altered.
ops$tkyte%ORA10GR2> merge into t using (select 1 new_x, 1 new_y from dual ) d on ( t.y = d.new_y ) when matched then update set x = new_x;
1 row merged.
ops$tkyte%ORA10GR2> merge into t using (select 2 new_x, 1 new_y from dual ) d on ( t.y = d.new_y ) when matched then update set x = new_x;
merge into t using (select 2 new_x, 1 new_y from dual ) d on ( t.y = d.new_y ) when matched then update set x = new_x
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4:
'/home/ora10gr2/oracle/product/10.2.0/oradata/ora10gr2/users01.dbf'
ops$tkyte%ORA10GR2> alter tablespace users online;
Tablespace altered.
merge and blob
Roen, September 26, 2008 - 5:45 pm UTC
Oracle version: 10G
Are there any limitations or performance issues using merge
statement that included blob columns. This will be through PL/SQL (loop) as I load blobs (from OS) into a database table.
Thanks.
September 27, 2008 - 11:18 am UTC
what do merge and loop have to do with each other?
I would be worried about the loop if performance is an issue - you would want to look for non-code ways to achieve your goal (eg: a single sql statement, not a sql statement in a loop executed over and over and over and over...)
merge in a loop
Roen, September 27, 2008 - 5:43 pm UTC
"you would want to look for non-code ways to achieve your goal"
Since this is a batch processing scenerio that requires me to keep lot of tabs like how many files were successfully uploaded..or not to load certain kind of files..I am not sure how I can do this with one sql rather than processing them file by file. As far as Merge and loop..My options were
1. If DUP_VAL_INDEX then Update
2. Use merge with select :x, :y from dual as a join condition table..
And here is what came from 10g Docs:
This statement is primarily useful in data warehousing situations where large amounts of data are commonly inserted and updated. If you only need to insert or update a single row, it is more efficient to do that with the regular PL/SQL techniques: try to update the row, and do an insert instead if the update affects zero rows; or try to insert the row, and do an update instead if the insert raises an exception because the table already contains that primary key.
Thanks.
September 28, 2008 - 10:19 am UTC
... Since this is a batch processing scenerio that requires me to keep lot of tabs
like how many files were successfully uploaded..or not to load certain kind of
files..I am not sure how I can do this with one sql rather than processing them
file by file. ...
I've always failed to see the connection between "I am batch", "I cannot do sets". Look at the problem, look for set based solutions.
Do not fall into slow by slow processing - I'll go back to that. do not process a row at a time (that you have a file to process indicates to me that you have more than a record to process - do things IN SETS)
MERGE is an atomic statement - insert+possible update or update+possible insert is not - not in a multi-user environment.
You'll find the performance of
insert
when dup val then update
to be horrible if the row almost always exists.
update
when %rowcount = 0 then insert
to be horrible if the row almost never exists.
You'll find
update
when 0 insert
to be best when the row usually exists
insert
when dup val then update
to be best when the row almost never exists
you'll find
merge
to be good in all of the above.
And you'll find benchmarks to be useful to see which is "best", for example:
create table T
as
select object_id, owner, object_type, object_name
from all_objects
where rownum <= 10000;
alter table T add constraint T_pk primary key(object_id);
create table t1
as
select object_id, owner, object_type, object_name
from all_objects where 1=0;
alter table t1 add constraint t1_pk primary key(object_id);
create table t2
as
select object_id, owner, object_type, object_name
from all_objects where 1=0;
alter table t2 add constraint t2_pk primary key(object_id);
create table t3
as
select object_id, owner, object_type, object_name
from all_objects where 1=0;
alter table t3 add constraint t3_pk primary key(object_id);
create or replace procedure try_update_then_insert
as
begin
for x in ( select * from T )
loop
update t1
set object_name = x.object_name,
object_type = x.object_type,
owner = x.owner
where object_id = x.object_id;
if ( sql%rowcount = 0 )
then
insert into t1
(object_id,owner,object_type,object_name)
values
(x.object_id,x.owner,x.object_type,x.object_name);
end if;
end loop;
end;
/
create or replace procedure try_insert_then_update
as
begin
for x in ( select * from T )
loop
begin
insert into t2
(object_id,owner,object_type,object_name)
values
(x.object_id,x.owner,x.object_type,x.object_name);
exception
when dup_val_on_index
then
update t2
set object_name = x.object_name,
object_type = x.object_type,
owner = x.owner
where object_id = x.object_id;
end;
end loop;
end;
/
create or replace procedure just_merge
as
begin
for x in ( select * from T )
loop
merge into t3
using (select x.object_id object_id,
x.owner owner,
x.object_type object_type,
x.object_name object_name
from dual) D
on (t3.object_id = d.object_id)
when matched
then update
set owner = d.owner,
object_type = d.object_type,
object_name = d.object_name
when not matched
then insert (object_id,owner,object_type,object_name)
values (d.object_id,d.owner,d.object_type,d.object_name);
end loop;
end;
/
and then run six sessions with tracing on. first two run try_update_then_insert, second two run try_insert_then_update, last two run just_merge (testing almost none exist followed by almost all exist cases...)
tkprof's would show something like:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
update then insert
total 20271 6.54 6.45 0 20063 31540 20049
total 10104 3.31 3.30 0 20167 10294 20000
insert then update
total 10274 3.91 4.82 8 883 35668 20053
total 50194 20.51 20.43 0 150426 84868 30021
just merge
total 10229 5.00 4.93 0 19937 31522 20039
total 10104 4.68 4.55 0 30172 10294 20000
I'd be very tempted to go with merge across the board here - avoid insert/update route.
update/insert is just more code that you have to write, test - and by the time you update and check sql%rowcount - there is no reason some other session might not have inserted the row and committed - so you still have to deal with dup val on index in general (or consider it a fatal error)
error_logging_clause of merge statement
Roen, September 28, 2008 - 6:38 pm UTC
Thanks for the comparison Tom..
I noticed in Oracle docs there is an "error_logging_clause" clause of merge statement that can log errors into a log table...
Should there be a separate "log error" clause for insert and update statement..or one clause at the end will log both operations of the merge statement for the target table. Just a side note..I am really liking this "log error" clause thing..seems pretty cool...
Also..is there a way to keep some kinda tab on how many were inserted/vs updated during a merge statement?
Thanks
September 28, 2008 - 8:37 pm UTC
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081218 the train track is pretty easy to follow on this one.
There is a single optional error logging clause at the end, after the "when matched" and "when not matched" clauses.
The merge statement is a single statement - the operation is a merge, not really an update, not really and insert - a merge. and as such, the error logging clause is for the statement.
Reported back are the number of rows merged only.
returning clause for a lob
Roen, September 28, 2008 - 11:33 pm UTC
Version 10G
Tom,
Where does the returning clause go in a merge statement
(inserting/updating a blob):
MERGE INTO target a
USING source b
ON
(
a.pk_col1 = b.pk_col1
AND a.pk_col2 = b.pk_col2
AND a.pk_col3 = b.pk_col3
AND a.pk_col4 = b.pk_col4
)
WHEN
MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
I tried following oracle docs i.e
insert statement --> after values clause and
update statement --> after where clause
I keep getting errors..
Why Merge?
Vidyesh, November 03, 2008 - 5:36 am UTC
Dear Tom,
The "merge" needs unique or primary key on joining column.
Why not just do update (select...) and insert instead of row-by-row merge statement ?
Is there any specific case wherein "merge" is of specific use?
Thanks & best Regards
November 10, 2008 - 2:56 pm UTC
the merge does not need a unique or primary key.
ops$tkyte%ORA10GR2> create table t1 ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> create table t2 ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> insert into t1 values ( 1, null );
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values ( 1, 100 );
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values ( 2, 200 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> merge into t1 using t2 on (t1.x = t2.x)
2 when matched then update set y = t2.y
3 when not matched then insert(x,y) values (t2.x,t2.y);
2 rows merged.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t1;
X Y
---------- ----------
1 100
2 200
merge is a set operation that works in bulk - working in bulk is better than slow by slow (whoops - guess I meant "row by row", but it rhymes with slow by slow and is really slow, by slow...)
Dan, November 10, 2008 - 4:48 pm UTC
You also cannot update a join unless you have update privileges on all tables in the join, even if you are only updating one.
Merge PK dependency
sanjay, March 17, 2009 - 4:14 pm UTC
As Tom said,without using PK works,
but for duplicate set of data we will encounter ORA-30926 Unagle to get a stable set of rows in the source tables
March 18, 2009 - 8:13 am UTC
and that is a very very very good thing...
What about atomicity?
Paul Moore, April 24, 2012 - 10:53 am UTC
One aspect of this question which I didn't see in the discussion is atomicity. Consider a simple "counter" table
create table counts (id number primary key, n number)
If we update, then insert on rowcount of 0, there is a (small) window when another session could add a record between the update and the insert, resulting in a primary key violation.
If this matters (and it probably should!) then you pretty much have to do the insert first, and if it fails do the update. That way you always hold a lock on the record you are working with.
By the way, I don't know if MERGE is atomic in this sense. I'd like to think that it is, but I don't see an easy way to test it (as with any race condition, provoking it can be hard).
April 24, 2012 - 11:27 am UTC
If this matters (and it probably should!) then you pretty much have to do the insert first, and if it fails do the update. That way you always hold a lock on the record you are working with.
that'll have precisely the same issue - resulting in a primary key violation.
I try to insert at the same time you do, about the same time, doesn't have to be precisely the same time.
One of us will block one the other - during the insert.
One of us will get a primary key violation when the other commits.
The code must be prepared to deal with a unique constraint violation regardless.
Paul Moore, April 25, 2012 - 8:44 am UTC
Sorry, I should have been clearer. The code I had in mind was
becin
insert into counts (id, n) values (p_id, 1);
exception
when dup_value_on_index then
update counts set n = n + 1 where id = p_id;
end;
As you say, this code is prepared for a unique constraint violation and will work. The alternative, doing the update first, is
update counts set n = n + 1 where id = p_id;
if sql%rowcount = 0 then
insert into counts (id, n) values (p_id, 1);
end if;
That doesn't handle constraint violations, and to make it do so you have to duplicate the update. If you fail to trap the possible exception, the code will "mostly" work but there is a small time window where your code can try the update and find the row doesn't exist, but then before the insert is executed another session could add the row, triggering the constraint violation. This is a classic race condition.
I have just seen this precise code in a production system which is why I thought it was worth mentioning it as a downside of the "try the update first" approach.</code>
April 25, 2012 - 10:13 am UTC
got it, thanks!
Upsert with race condition -> MERGE INTO .. LOG ERRORS
Chris R. Donnelly, July 22, 2013 - 2:24 pm UTC
So from what I understand, the best way to handle an upsert into a single table from multiple processes (which can result in a DUP_VAL_ON_INDEX race condition) would be a MERGE INTO ... LOG ERRORS statement?
Additionally, I also have found it is possible to use either the TAG feature with a SYS_GUID() (or a DBMS_ERRLOG-shaped global temporary table) to then MERGE INTO the target table from the error table (UPDATE only) for DUP_VAL_ON_INDEX (once you check for other errors first!).
DISCLAIMER: While the GTT-ERRLOG solution feels cleaner to me, I have not yet verified it under any significant load, only in basic testing, and only on 11.2.0.3.
# Chris