Oracle-L: RE: Re: RE: tuning a massive delete

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 17 Sep 2003 09:19:39 -0800
Message-ID: <F001.005D03C6.20030917091939@fatcity.com>

Roy,

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.

HTH, SF

>----- ------- Original Message ------- -----
>From: <rgaffuri_at_cox.net>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 17 Sep 2003 08:24:44
>
>i explain planned it and it was much worse than not
>exists.
>
>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" <Richard.Ji_at_ztango.com>
>> Date: 2003/09/17 Wed PM 12:04:56 EDT
>> To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>> 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)
>nologging
>> 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 =
>b.add_cymd)
>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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