Oracle-L: Re: tuning a massive delete

From: Binley Lim <Binley.Lim_at_xtra.co.nz>
Date: Wed, 17 Sep 2003 15:54:43 -0800
Message-ID: <F001.005D0425.20030917155443@fatcity.com>

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" <ORACLE-L_at_fatcity.com> Sent: Thursday, September 18, 2003 9:04 AM

> rgaffuri_at_cox.net 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: sfaroult_at_oriole.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

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 - 18:54:43 CDT