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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 05 Oct 2005 10:22:57 +0200
Message-ID: <di02go$q8b$01$1@news.t-online.com>

stephen O'D schrieb:
> callcutj_at_mail.com wrote:
>

>>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.

>
>
> Same behaviour here (9.2.0.1 on linux):-
>
> SQL> create table table_a (mypk integer, data varchar2(10));
>
> Table created.
>
> SQL> alter table table_a add constraint table_a_pk
> 2 primary key (mypk);
>
> Table altered.
>
> SQL> insert into table_a
> 2 select level, 'data'
> 3 from dual
> 4 connect by level < 10;
>
> 9 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> create table table_b (notpk integer, data varchar2(10));
>
> Table created.
>
> SQL> insert into table_b
> 2 select level, 'data'
> 3 from dual
> 4 connect by level < 10;
>
> 9 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> set autot on
>
> SQL> L
> 1 delete from
> 2 ( select a.*
> 3 from
> 4 table_a a, table_b b
> 5 where a.mypk = b.notpk
> 6* )
> SQL> /
>
> 9 rows deleted.
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 DELETE STATEMENT Optimizer=CHOOSE
> 1 0 DELETE OF 'TABLE_B'
> 2 1 NESTED LOOPS
> 3 2 TABLE ACCESS (FULL) OF 'TABLE_B'
> 4 2 INDEX (UNIQUE SCAN) OF 'TABLE_A_PK' (UNIQUE)
>
>
>
> An interesting point is this, if you add a primary key to table_B, it
> deletes the rows from table_a instead:-
>
> SQL> alter table table_b add constraint table_b_pk
> 2 primary key (notpk);
>
> SQL> delete from
> 2 ( select a.*
> 3 from table_a a, table_b b
> 4 where a.mypk = b.notpk);
>
> 9 rows deleted.
>
> SQL> select count(*) from table_a;
>
> COUNT(*)
> ----------
> 0
>
> SQL> select count(*) from table_B;
>
> COUNT(*)
> ----------
> 9
>
> Seems like strange behaviour to me, but hopefully someone else can
> comment on it. I know that if you try to update the results of a join,
> both tables must be joined on a PK or it will refuse to do it.
>

Thereas the original question seems to me doesn't violating the rules of   updatable join views (
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:273215737113 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/views.htm#391 )
however your second example is not so obvious - if two tables are joined on their primary key columns, which table is key preserved apparently depends from the order of appearance in the from clause, docs states it should be exactly one key preserved table - why oracle choose to be key preserved the first in the from list is indeed unclear.

Best regards

Maxim Received on Wed Oct 05 2005 - 03:22:57 CDT