Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: RE: tuning a massive delete

RE: Re: RE: tuning a massive delete

From: Stephane Faroult <>
Date: Wed, 17 Sep 2003 09:19:39 -0800
Message-ID: <>


   NOT EXISTS is to be avoided when it is the only criterion - the subquery is executed for each row in the outer query. The way the CBO behaves varies wildly between 8.1.7 and 9.x. Richard's suggestion is quite correct and a fairly safe way to get a 'right' behaviour with most versions is to use an external join and test for NULL, as in

 select b.*
 from ani_prx b,

      bo_owner_stage.ani_prx a

 where a.cusip = b.cusip (+)
   and a.fund_no = b.fund_no (+)
   and a.add_cymd = b.add_cymd (+)

   and nvl(b.reccode, 'X') != 'V'
   and b.cusip is null

or something similar. Worth trying an explain. Even if the cost is not necessarily to be believed. MINUS would also be something I'd contemplate.


>----- ------- Original Message ------- -----
>From: <>
>To: Multiple recipients of list ORACLE-L
>Sent: Wed, 17 Sep 2003 08:24:44
>i explain planned it and it was much worse than not
>my understanding is hash_aj is faster when the
>table in the sub-query returns results that are
>significantly less than the one in the outer table.
>ill try it, but i think exists is faster. we dont
>want to do an index scan here and my hash_area_size
>isnt real big.
>> From: "Richard Ji" <>
>> Date: 2003/09/17 Wed PM 12:04:56 EDT
>> To: Multiple recipients of list ORACLE-L
>> Subject: RE: tuning a massive delete
>> Try anti hash join.
>> -----Original Message-----
>> Sent: Wednesday, September 17, 2003 11:45 AM
>> To: Multiple recipients of list ORACLE-L
>> i have a table with 27 million records that is
>about 1.2 GB in size. I have a 'staging table' with
>18 million records. 16 million records have a
>'delete' flag. I have indexed the column in staging
>with a delete flag. both tables have indexed
>primary keys. Is the following my fastest option or
>would an 'IN' be faster? Im concerned because this
>has been running for a while and have alot of
>consistent gets but no 'writes' yet which tells me
>its still building the join. Our sort_area_size is
>rather small and Im not allowed to change it which
>tells me we are swapping to the temp tablespace.
>> anyway to speed this up? or is this the fastest
>we got?
>> create table ani_prx_new parallel (degree 5)
>> as select *
>> from ani_prx b
>> where not exists (select 1 from
>bo_owner_stage.ani_prx a where ba_reccode = 'V' and
>a.cusip = b.cusip
>> and a.fund_no = b.fund_no and a.add_cymd =

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Sep 17 2003 - 12:19:39 CDT

Original text of this message