On a general note, this older outer join syntax to simulate a not-in requires ALL the "b" columns to include the (+) sign, including the "nvl...", except the "b.cusip is null", which is the not-in itself. If you miss one, the logic is completely changed.
9i's new syntax does outer-joins by doing the outer on the tables, rather than the columns, so you can no longer do this outer "simulation"! In this case, you can achieve the same thing with a not-in, rather than (+), in the SQL syntax (assuming you see a HASH AJ in the plan somewhere). ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 9:04 AM > [EMAIL PROTECTED] wrote: > > > > create table ani_prx_faster parallel (degree 5) nologging > > as > > select b.* > > from bo_owner_master.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.ba_reccode, 'X') != 'V' > > and b.cusip is null > > > > This query I got from here I got only 638k, and I have 27m records in my file and there are 17m deletes. so 12m records left. Also all the records are null???? > > > > what do i need to change? > > > > > Ooops, I have permuted a's and b's in the where clause ... > > Otherwise not to sure about the nvl(). Wait a minute ... we are getting > rows for which there is no matching (cusip, fund_no, add_cymd) in the > staging table. Perhaps that the 'cusip is null' condition should go ... > the nvl() should be enough. > > -- > Regards, > > Stephane Faroult > Oriole Software > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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).
