Re: Puzzled with SQL performance

Alton Ayers wrote in message <3511CB39.4E72C9BA_at_ditw.com>... <snipped>
>This third query takes long enough (hours) that I haven't let it finish:
>
>SQL> DELETE FROM ach_details
> 2 WHERE tran_rec_id IN
> 3 (SELECT tran_rec_id FROM today_transactions
> 4 WHERE org_id = 150);
>
>Notice that there is no "tran_rec_id" defined in today_transactions.
>
>Does anyone have an explanation for this behavior?

You're basically doing the following:
DELETE FROM ach_details
WHERE tran_rec_id IN

   (SELECT ach_details.tran_rec_id FROM dual);

Only in this case, "dual" contains 1000's (100,000's?) of rows.

Let's say that ACH_DETAILS table contains 100 rows and TODAY_TRANSACTIONS 10 rows. Each of the rec id's in ACH_DETAILS will thus be repeated 10 times in the subselect, resulting in a set of 1,000 values for tran_rec_id. The final result will be that -all- the rows from ACH_DETAILS will be deleted.

And then you wondering why this DELETE takes that long.. :-))

regards,
Billy Received on Thu Mar 19 1998 - 00:00:00 CST