Equivalent delete statements
Date: Sun, 28 Nov 2010 03:53:53 -0800
Message-ID: <AANLkTi=a1KL96GV_qeTe-Ms8AokVQ+prCTG7huetmnWn_at_mail.gmail.com>
We have oracle applications 12.1.2 and as part of a concurrent this delete is run numerous times
First the version details:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production
This is the delete statement that is bad. This takes 2 sec to run. The problem is this executes around 10000 times which is part of a bigger program and the overall completion time of the program is increased.
DELETE FROM PSP_ENC_CHANGED_ASSIGNMENTS
WHERE PAYROLL_ACTION_ID = 1234
AND PAYROLL_ID = 81
AND REQUEST_ID = 99102784
AND ASSIGNMENT_ID IN
(SELECT ASSIGNMENT_ID
FROM PSP_ENC_PAYROLL_ASSIGNMENT_V
WHERE PAYROLL_ID = 81
AND EXCLUDE = 'Y'
MINUS
SELECT ASSIGNMENT_ID
FROM PSP_ENC_CHANGED_ASG_HISTORY
WHERE PAYROLL_ID = 81 );
So I modified it to use the WITH clause like this and this completes in 18
ms.
delete from PSP_ENC_CHANGED_ASSIGNMENTS
WHERE PAYROLL_ACTION_ID = 1234
AND PAYROLL_ID = 81
AND REQUEST_ID = 99102784
and assignment_id in (WITH T
as (SELECT ASSIGNMENT_ID
FROM PSP_ENC_PAYROLL_ASSIGNMENT_V
WHERE PAYROLL_ID = 81
AND EXCLUDE = 'Y'
MINUS
SELECT ASSIGNMENT_ID
FROM PSP_ENC_CHANGED_ASG_HISTORY
WHERE PAYROLL_ID = 81)
SELECT ASSIGNMENT_ID FROM T);
I just want to confirm that both the sqls are equivalent. When I execute in
the several environments, I have it says 0 rows deleted for both the
versions. So how to confirm that this sql is equivalent.
Since this sql comes from Oracle Applications, I am not sure how else to
test it.
Thank you
Kumar
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Nov 28 2010 - 05:53:53 CST