Re: Equivalent delete statements
- From: Toon Koppelaars <toon@xxxxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Sun, 28 Nov 2010 13:15:17 +0100
They are equivalent: the only thing you did was use subquery factoring to
make more explicit to the CBO that that whole subquery only needs to be
computed once.
Prior to your rewrite the CBO probably merged the subquery into the parent
query, which appears to have been a bad choice in this case.
On Sun, Nov 28, 2010 at 12:53 PM, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
> 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
>
>
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13
Other related posts:
