MERGE using DECODE logic to control UPDATE

352420 pts.
Tags:
Oracle 11g
SQL
MERGE using DECODE logic to control UPDATE The SQL below has been running extremely well for over a years in our Production application on Oracle release 10.1. We have recently upgraded to Oracle release 11.1 and this same SQL routine now takes a minimum of 4 times longer to run on 11.1 Has anyone else encountered such performance degradation when moving from 10.1 to 11.1. It appears to be limited to this particular routine as 99.9% of the rest of our application seems to run faster on Oracle 11.1. Any ideas or similar experiences out there ? SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 79 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET TERM OFF SPOOL /mbshist/mbsload/cmo_dir/logs/updt_endrange.sqlrpt MERGE INTO mbs_daterange md USING ( SELECT * FROM cmo_keyvals ) ck ON (md.seqno = ck.seqno AND md.item = ck.item) WHEN MATCHED THEN UPDATE SET md.end_range = DECODE(SIGN(ck.end_range-md.end_range),1,ck.end_range,md.end_range), md.begin_range = DECODE(SIGN(ck.end_range-md.begin_range),-1,ck.end_range,md.begin_range) WHERE ck.end_range > md.end_range or ck.end_range < md.begin_range WHEN NOT MATCHED THEN INSERT (md.seqno, md.item, md.begin_range, md.end_range, md.location) VALUES (ck.seqno, ck.item, ck.end_range, ck.end_range, NULL); commit; SPOOL OFF quit; /

Software/Hardware used:
SQL

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following