Delete behaviour

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Mon, 3 Mar 2008 13:40:54 -0800 (PST)
Message-ID: <cba57829-ca66-4413-94a3-52637082f36d@s12g2000prg.googlegroups.com>


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> Received on Mon Mar 03 2008 - 15:40:54 CST

Original text of this message