I ended up by using an external programme.
NOT EXISTS is just a set difference.
Doing set compares is really quick if both sets are sorted.
I use CRC64s for the data and just compare the resulting sorted sets of
(large CRC 64-bit) numbers.
Because everything hashes to a number, the memory requirements are not
that bad either (8 bytes per item ~256000 tuples per Mb).
The programme is in C++ but is as fast in something like Perl.
I.e. comparing millions of rows of data takes 10s of seconds rather than
10s of minutes.
Leo
>
> 1) get items which are orphaned in a.
> CREATE TEMP TABLE orphans as
> SELECT a.join1, a.join2
> FROM a LEFT OUTER JOIN b ON a.join1 = b.join1 AND
> a.join2 = b.join2
> WHERE b.join1 IS NULL AND b.join2 IS NULL
>
> D A
>
>
> "Llew" <leo.goodstadt@anat.ox.ac.uk> wrote in message
> news:a65qm1$2k6g$1@jupiter.hub.org...
> > Dear everyone,
> > What is the best way of removing rows which are not in
> another table?