Re: Fastest way to check for dupes

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