Subject delete query optimization question
Author Emile Snyder
Hello all,

So, after having 'set plan on' pointed out (and finding 'set stats on'),
I'm trying to figure out the following situation.

I have two tables, RESULTS and MEMO, where you can have many memo's per
result. There's a before delete and after delete trigger on RESULTS,
the before delete trigger removes any memos that reference the result
that's about to be deleted.

The results table has ~500,000 records, ~280,000 of which are due to a
runaway script dumping junk into it. All the junk records have a 'ver'
column of

select count(result_id) from results where ver = '' gives
Elapsed time= 9.15 sec

I haven't run "delete from results where ver = ''" long enough
to finish, but its went over 8 hours before I had to stop it for
unrelated reasons.

The following query
'delete from results where result_id in (select first 10 result_id from
results where ver = '');'



and has been running for ~10-20 minutes so far. Which plan refers to
which part? Any tips about how to improve this? Should I drop the
triggers, delete all the memos with a by hand query (delete from memo
where result_id in (select result_id from results where ver =
'') ?), delete the results, then add the triggers again?

Thanks for any help,

How do I set my LaserPrinter to "Stun"?!