Re: Delete behaviour

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Mon, 3 Mar 2008 15:56:59 -0800 (PST)
Message-ID: <23d47e59-7ccb-43bc-87ef-7e2918df68b1@s37g2000prg.googlegroups.com>


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. Received on Mon Mar 03 2008 - 17:56:59 CST

Original text of this message