Re: tuning a massive delete

In this example, yes, looks like you need a union.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, September 18, 2003 12:44 PM

> Perhaps this small example can make it clear?
> I have two tables, orders and order_to_delete.
> I want to find orders to KEEP - i.e. the order_id is not in table
order_to_delete, or it's in table order_to_delete with a status 'N'.
>
> If I'm using outer joins, I think I need a a union, nicht wahr?
>
> drop table order_to_delete ;
> drop table orders ;
> create table orders (order_id number not null, order_date date) ;
> create table order_to_delete (order_id number not null, delete_flag
varchar2 (1)) ;
> insert into orders (order_id, order_date)
> values (1, sysdate - 1) ;
> insert into orders (order_id, order_date)
> values (2, sysdate - 2) ;
> insert into orders (order_id, order_date)
> values (3, sysdate - 3) ;
> insert into order_to_delete (order_id, delete_flag)
> values (1, 'Y') ;
> insert into order_to_delete (order_id, delete_flag)
> values (2, 'N') ;
> commit ;
> select a.order_id, a.order_date
> from orders a, order_to_delete b
> where a.order_id = b.order_id
> and b.delete_flag = 'N'
> union
> select a.order_id, a.order_date
> from orders a, order_to_delete b
> where a.order_id = b.order_id (+)
> and b.order_id is null ;
>
> This query will return order ids 2 and 3, the ones I want to KEEP. The
query returns the same value in Oracle 8.1 and Oracle 9.2
>
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> > Binley Lim
> >
> > 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.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jacques Kilchoer
> INET: Jacques.Kilchoer_at_quest.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).