Re: How do I speed this up?

Hi Mike

The problem with what you're doing is that the 'NOT IN ( ...)' builds a comma-delimited list of uniquekeys that match. Once this gets over a few hundred it slows down.

Try
delete from tablea
where not exists( select * from tableb where tablea.uniquekey = tableb.uniquekey)

Or something similar to that.

HTH Tim.
==
 tableb.uniquekey from tableb)
"Mike Heden" <mheden_at_bigfoot.com> wrote in message news:53b3de4.0212060743.56c83fc4_at_posting.google.com

> 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

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG