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: <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>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" <[EMAIL PROTECTED]>
>> Date: 2003/09/17 Wed PM 12:04:56 EDT
>> To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
