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 - 15:55:51 CST
