Equivalent delete statements
- From: Kumar Madduri <ksmadduri@xxxxxxxxx>
- To: oracle Freelists <Oracle-L@xxxxxxxxxxxxx>
- Date: Sun, 28 Nov 2010 03:53:53 -0800
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
Other related posts:
