Re: How do I speed this up?

From: John Russell <netnews4_at_johnrussell.mailshell.com>
Date: Sat, 07 Dec 2002 21:53:07 GMT
Message-ID: <svq4vu82d8ie0k8063g4mclshif1n8tki4@4ax.com>

On 6 Dec 2002 07:43:12 -0800, mheden_at_bigfoot.com (Mike Heden) wrote:

>I have two tables which use a common unique key.
>
>I want to delete a number of rows from table a where the unique key
>tablea.uniquekey is not present in table b as tableb.uniquekey.

If you're using PL/SQL, one way to do it is to find out what keys should be deleted using BULK COLLECT to store the key values in a collection, then use FORALL to issue all the delete statements based on the elements in the collection. Both operations get batched so that you don't get the overhead of looping through the result set, then looping through the DELETEs.

This may not be quite as fast as other techniques like NOT EXISTS. It does have the nice side-effect that you have a record of which keys were used for the DELETEs, in case you want to record or print them.

John Received on Sat Dec 07 2002 - 15:53:07 CST