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 1.0.0.126

select count(result_id) from results where ver = '1.0.0.126' gives
PLAN (RESULTS INDEX (RESULTS_VER))
Elapsed time= 9.15 sec

I haven't run "delete from results where ver = '1.0.0.126'" 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 = '1.0.0.126');'

gives

PLAN (RESULTS INDEX (RDB$PRIMARY5,RESULTS_VER))
PLAN (RESULTS NATURAL)

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 =
'1.0.0.126') ?), delete the results, then add the triggers again?

Thanks for any help,
-emile


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