>> Another idea is to try an outer join: >> SELECT child_table.parentid INTO tmp_table >> FROM child_table LEFT JOIN parent_table >> ON (child_table.parentid = parent_table.parentid) >> WHERE parent_table.parentid IS NULL; > >There's also >DELETE >FROM child_table >WHERE NOT EXISTS (select 1 >from parent_table >where parent_id = child_table.parent_id >) > > Which won't use anything as efficient as a hash join or merge join but will beat > least capable of using index lookups for something basically equivalent toa > nested loop. Sorry for the delay, I only get to work on this system every now and then. I tried Greg's suggestion and it worked out great. The estimates from EXPLAIN were much larger than the actuals (e.g. 41 hours vs. 2 hours), so I probably have some tuning to do with this dataset. I decided not to try Tom's temp table method because I was afraid the generated table would be very large, so the subsequent DELETE .. WHERE NOT IN (...) would cause swapping again. Thanks all for your help, your insights saved me a lot of headache. Roman _____________________________________ Consolidate your email! http://www.fusemail.com _____________________________________ Check All Email Accounts Anywhere! Check your POP3 and webmail account from any PC. With no ads http://www.fusemail.com ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
