Possible Bug with: DELETE FROM (SELECT * FROM TABLE)

I am attempting to use the following DELETE FROM format with the intent of deleting the records in TABLE_A that appear within TABLE_B:

DELETE FROM (SELECT a.* FROM TABLE_A a JOIN TABLE_B b ON a.FIELD = b.FIELD)

where a.FIELD is defined as a PK; there is no PK, index, etc. defined on b.FIELD

When I execute this statement, I am finding that the records in TABLE_B are being deleted instead of TABLE_A! This is re-enforced when I look at the explain plan:

Operation                      Object
------------------------------ ------------------------------
DELETE STATEMENT ()
 DELETE ()                     TABLE_B   <--- I'd expect TABLE_A
  NESTED LOOPS ()
   TABLE ACCESS (FULL)         TABLE_B
   INDEX (UNIQUE SCAN)         TABLE_A

I am using:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 4 13:10:44 2005 Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

Note: I cannot use the "IN" clause (which does properly delete from TABLE_A), e.g:

SELECT a.* FROM TABLE_A WHERE a.FIELD IN (SELECT b.FIELD FROM TABLE_B b)

because I may need to join on several fields, eg.:

DELETE FROM (SELECT a.* FROM TABLE_A a JOIN TABLE_B b ON a.FIELD1 = b.FIELD1 AND a.FIELD2 = b.FIELD2)

Any help or insight into this would be greatly appreciated. Is it possible I have found a bug???

Thanks in advance. Received on Tue Oct 04 2005 - 14:05:15 CDT