Re: Delete behaviour

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 04 Mar 2008 23:19:14 +0100
Message-ID: <636072F26488tU1@mid.individual.net>

On 04.03.2008 00:56, Gokul wrote:

> On Mar 3, 11:48 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> Gokul wrote:

>>> Trying to understand the delete behaviour. Doesn't present consistent
>>> results. Just checking if this is expected behaviour or a bug. Is
>>> there a trace or something somewhere to check the optimizer rewriting
>>> the query. I understand 10.2.0.1.0 is a bit old. But then, not sure if
>>> that is the issue.
>>> BANNER
>>> ----------------------------------------------------------------
>>> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
>>> PL/SQL Release 10.2.0.1.0 - Production
>>> CORE 10.2.0.1.0 Production
>>> TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
>>> NLSRTL Version 10.2.0.1.0 - Production
>>> SQL> desc t1;
>>> Name Null? Type
>>> ----------------------------------------- --------
>>> ---------------------------
>>> I NOT NULL NUMBER
>>> SQL> desc t2;
>>> Name Null? Type
>>> ----------------------------------------- --------
>>> ---------------------------
>>> I NOT NULL NUMBER
>>> SQL> select * from t1;
>>> I
>>> ----------
>>> 1
>>> SQL> select * from t2;
>>> I
>>> ----------
>>> 1
>>> SQL> set autotrace traceonly explain
>>> SQL>
>>> SQL> delete from (select 1 from t1,t2 where t1.i = t2.i);
>>> 1 row deleted.
>>> Execution Plan
>>> ----------------------------------------------------------
>>> Plan hash value: 1067833891
>>> -----------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>>> Time |
>>> -----------------------------------------------------------------------------
>>> | 0 | DELETE STATEMENT | | 1 | 4 | 1 (0)|
>>> 00:00:01 |
>>> | 1 | DELETE | T1 | | |
>>> | |
>>> | 2 | NESTED LOOPS | | 1 | 4 | 1 (0)|
>>> 00:00:01 |
>>> | 3 | INDEX FULL SCAN | T2_PK | 1 | 2 | 1 (0)|
>>> 00:00:01 |
>>> |* 4 | INDEX UNIQUE SCAN| T1_PK | 1 | 2 | 0 (0)|
>>> 00:00:01 |
>>> -----------------------------------------------------------------------------
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>> 4 - access("T1"."I"="T2"."I")
>>> SQL> rollback;
>>> Rollback complete.
>>> SQL> delete from (select 1 from t2,t1 where t1.i = t2.i);
>>> 1 row deleted.
>>> Execution Plan
>>> ----------------------------------------------------------
>>> Plan hash value: 1389963799
>>> -----------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>>> Time |
>>> -----------------------------------------------------------------------------
>>> | 0 | DELETE STATEMENT | | 1 | 4 | 1 (0)|
>>> 00:00:01 |
>>> | 1 | DELETE | T2 | | |
>>> | |
>>> | 2 | NESTED LOOPS | | 1 | 4 | 1 (0)|
>>> 00:00:01 |
>>> | 3 | INDEX FULL SCAN | T1_PK | 1 | 2 | 1 (0)|
>>> 00:00:01 |
>>> |* 4 | INDEX UNIQUE SCAN| T2_PK | 1 | 2 | 0 (0)|
>>> 00:00:01 |
>>> -----------------------------------------------------------------------------
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>> 4 - access("T1"."I"="T2"."I")
>>> SQL>

>> I don't see any inconsistency in what you posted. Can you explain
>> what it is that is causing you concern?
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
> 
> Sorry, just realised wasn't clear enough. The first delete removes
> entries from T1 and the second from T2. Swapping the table order makes
> the difference. Primary key on columns of both the tables.

Just out of curiosity: why would anybody do this? I can see that deleting from views makes sense but why delete from an inline view and not directly delete from the table? Is there any kind of delete selection that cannot be done with "delete from t1..."?

Cheers

        robert Received on Tue Mar 04 2008 - 16:19:14 CST