Re: Delete behaviour

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 03 Mar 2008 17:07:00 -0800
Message-ID: <1204592818.811307@bubbleator.drizzle.com>


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.

Please post the DDL to create the two tables and the DML that inserts the rows. I'd like to see if I can duplicate what you are seeing though I can't say I have any unpatched 10gR2 servers.

Which leads me to suggest you shouldn't have any either. <g> Patch to 10.2.0.3.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Mar 03 2008 - 19:07:00 CST

Original text of this message