Re: Fastest way to check for dupes

Just asking, I don't know if it can or not, but what about

insert into a ( select from b minus select from a );

"Ryan Gaffuri" <rkg100_at_erols.com> wrote in message news:a68r70$lgm$1_at_bob.news.rcn.net...
> They feel that adding another table will make the datamodel too complex.
Ive
> done this for 18 months. Dont have the leverage that alot of you have. :(
>
>
> "Thomas Kyte" <tkyte_at_oracle.com> wrote in message
> news:a68aul02k0f_at_drn.newsguy.com...
> > In article <6757365f.0203070829.508dbc76_at_posting.google.com>,
> rkg100_at_erols.com
> > says...
> > >
> > >I have two tables with identical structure. A and B
> > >
> > >I am using SQLLDR to load data into table B. I need to delete all the
> > >rows in table B that EXACTLY match a row in table A. If a single
> > >column is different I need to keep it.
> > >
> > >I can do a delete, run a cursor... what runs the fastest? Or is there
> > >a better way. Management does not want me to use a seperate C table to
> > >load the data.
> > >
> > >Ryan Gaffuri
> >
> > I would be tempted to create a new table C
> >
> > create table c
> > as
> > select * from b
> > minus
> > select * from a;
> >
> > it'll handle nulls and everything properly, you could possibly use
> unrecoverable
> > and such as well so as to make the CTAS go as fast as possible.
> >
> > Why does mgmt care?
> >
> >
> > The delete statement could be:
> >
> > delete from b
> > where exists ( select null
> > from a
> > where (a.c1 = b.c1 OR (a.c1 is null and b.c1 is null))
> > and (a.c2 = b.c2 OR (a.c2 is null and b.c2 is null))
> > and .......
> > )
> >
> > skip the "is null" check on non-nullable columns to allow for the use of
> an
> > index on that column
> >
> > --
> > Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> > Expert one on one Oracle, programming techniques and solutions for
Oracle.
> > http://www.amazon.com/exec/obidos/ASIN/1861004826/
> > Opinions are mine and do not necessarily reflect those of Oracle Corp
> >
>
>
Received on Thu Mar 07 2002 - 18:26:20 CST