Re: Deleting rows using key from a 2nd table

In article <412p06$7fs_at_ixnews7.ix.netcom.com> chuckh_at_ix.netcom.com (Chuck Hamilton) writes:

> I have a job where there's a very large table, and I want to delete a
> small number rows out of it based on data obtained from a much smaller
> table. I would normally using something like this...
>
> DELETE FROM big_table a WHERE EXISTS
> (SELECT 'x' FROM small_table b
> WHERE a.pkcol_1 = b.pkcol_1 AND
> a.pkcol_2 = b.pkcol_2
> )
>
> By looking at the statement though, it looks like it's going to try
> and go through the entire big_table which may contain millions of
> rows. Since the small table will only contain about 50,000 rows, of
> which only maybe 100 will exist in big_table and actually get deleted,
> isn't there a better way? Would using "IN" instead of "EXISTS" work
> better in this case? I've never seen a case where it did. How would
> you even use "IN" when there's more than one column involved?

Performance issues aside, there are two ways to user "IN" instead of "EXISTS" in your delete statement:

Method 1:

DELETE FROM big_table
WHERE rowid IN
(SELECT a.rowid
 FROM big_table a, small_table b
 WHERE a.pkcol_1 = b.pkcol_1
 AND a.pkcol_2 = b.pkcol_2);

Method 2:

DELETE FROM big_table
WHERE (pkcol_1, pkcol_2) IN
(SELECT pkcol_1, pkcol_2
 FROM small_table);

--
 Alvin W. Law ........................................... Oracle Corporation
 Project Leader ............................. 300 Oracle Parkway, Box 659305
 Applications Design & Architecture ............... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ...... Voice: +1.415.506.8317 . Fax: +1.415.506.7294

******************************** W a r n i n g ******************************
Microsoft Network is prohibited from reproducing this work in any form, in
whole or in part, without the express written consent of the original author.