Re: Deleting rows using key from a 2nd table

From: Saad Ahmad <sahmad_at_mfa.com>
Date: 1995/08/18
Message-ID: <41320j$9b9_at_homer.alpha.net>#1/1

Chuck Hamilton (chuckh_at_ix.netcom.com) wrote:
> 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
> )

That is a full table scan, i got something like this from explain

1-0-FILTER
  2-1-TABLE ACCESS FULL ITMMST -- big table
  2-2-TABLE ACCESS FULL RCPSUM -- small table

you could use PL/SQL and declare a cursor for the small_table and then delete the rows from the big table.

OR
DELETE FROM big_table a WHERE

   ( a.pkcol_1, a.pk_col2 ) IN ( SELECT b.pk_col1, b.pk_col2

                                 FROM   small_table b
                               );

i got this from explain ...
1-0-NESTED LOOPS
  2-1-VIEW
    3-1-SORT UNIQUE
      4-1-TABLE ACCESS FULL RCPSUM -- a small table   2-2-INDEX UNIQUE SCAN ITMMST_PK -- pk of big table

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************

Received on Fri Aug 18 1995 - 00:00:00 CEST