Puzzled with SQL performance

I'm puzzled. I have a table, ach_details, containing 366,534 rows. It's defined with tran_rec_id as the first column in the primary key. The second table, today_transactions, has 0 rows. It has today_tran_rec_id as the first column in its primary key.

This query takes 80 seconds to execute:

SQL> DELETE FROM ach_details

  2      WHERE tran_rec_id IN
  3          (SELECT today_tran_rec_id FROM today_transactions
  4           WHERE org_id = 150);

0 rows deleted.

This query takes 1.5 seconds to execute:

SQL> DELETE FROM ach_details

  2      WHERE tran_rec_id IN
  3          (SELECT NVL(today_tran_rec_id, 0) FROM today_transactions
  4           WHERE org_id = 150);

0 rows deleted.

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? Received on Thu Mar 19 1998 - 00:00:00 CST