How do I speed this up?

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.

In most cases the number of rows in tablea and tableb is roughly equal.

I've used:-

delete from tablea where tablea.uniquekey not in (select tableb.uniquekey from tableb)

This works OK for small numbers of rows, but the time to execute the query is a function of the product of the numbers of rows in the two tables. In practice if tablea and b copntain a couple of thousand rows the execution time is 5 seconds. However this rises to 240 seconds where the rowcount is around 17,000. For larger rowcounts the execution time quickly becomes unacceptable.

What's the quickest alternative way to achieve this? I'm using Oracle 8.1.7

Thanks,

Mike Received on Fri Dec 06 2002 - 09:43:12 CST