Re: Delete Performance Issue

  • From: "Don Doo" <doodon@xxxxxxxxx>
  • To: "Kerber, Andrew" <Andrew.Kerber@xxxxxxx>
  • Date: Thu, 21 Dec 2006 13:25:26 -0500

Andrew,

There is no change in the execution plan.  Same plan as before

0        DELETE STATEMENT
1  0     DELETE
2  1    HASH JOIN                              4132
3  2    VIEW
4  3    SORT UNIQUE
5  4    HASH JOIN                                13
6  5    TABLE ACCESS FULL C_RUN                   6
7  5    NESTED LOOPS                              6
8  7    TABLE ACCESS BY INDEX ROWID C_STAGE      1
9  8     INDEX UNIQUE SCAN C_STAGE_AK            1
10 7   TABLE ACCESS FULL C_SUMMARY               5
11 2   TABLE ACCESS FULL C_TRACE                4113


On 12/21/06, Kerber, Andrew <Andrew.Kerber@xxxxxxx> wrote:


 Could you rewrite the subquery using exists and accomplish the same
purpose? Something like this (may not be quite right), the idea is to only
hit the first matching record in the subselect, instead of getting all of
them:



delete from cs_trace cs
 where cs.targetperiod= 200612
 and exists (select
 RunSeq from   C_Run pr,
               C_Summary ss,
               C_Stage st
      where  cs.RunSeq = ss.RunSeq
                and    ss.stageType = st.stageType
                and    st.name = 'load'
              and    pr.period = 361
       and    ss.Active   = 'yes')



Andrew W. Kerber
Oracle DBA
UMB
816-860-3921
andrew.kerber@xxxxxxx



"If at first you dont succeed, dont take up skydiving"

-----Original Message-----
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Rajeev Prabhakar
*Sent:* Thursday, December 21, 2006 11:55 AM
*To:* doodon@xxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Delete Performance Issue



Hi Don



One of the things you could try is parallel DML if

your database server is not too cpu bound.



Plus, verify that relevant indices are present.



HTH

-Rajeev



On 12/21/06, *Don Doo* <doodon@xxxxxxxxx> wrote:

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 12-2006