Dear everyone,
What is the best way of removing rows which are not in another table?
I have these two tables each with millions of rows/tuples.
They are joined on two fields:
CREATE TABLE a
( join1 OID, join2 OID, --a fair number of other fields .....
)
CREATE TABLE b
( join1 OID, join2 OID, --a fair number of other fields .....
)
There are indices on both of them (on "join1, join2").
At the moment, I am doing
1) get items which are orphaned in a.
CREATE TEMP TABLE orphans as SELECT join1, join2 FROM a WHERE NOT EXISTS ( SELECT *
FROM b WHERE a.join1 = b.join1 AND a.join2 = b.join2 )
2) DELETE FROM a where orphans.join1 = a.join1 and orphans.join2=a.join2
3) DROP TABLE orphans
This is very slow. Is there a better way?
Should I first copy all join1. join2 from a and b into temporary tables
first?
Do I need to index the temporary tables?
Surely this is a general enough a problem that optimal sets of solutions
exists in people's experience.
Thanks a lot.
Llew
