Delete behaviour
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