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