Re: Delete behaviour

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 03 Mar 2008 14:48:44 -0800
Message-ID: <1204584523.447815@bubbleator.drizzle.com>


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
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 - 16:48:44 CST

Original text of this message