Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!s12g2000prg.googlegroups.com!not-for-mail From: Gokul Newsgroups: comp.databases.oracle.server Subject: Delete behaviour Date: Mon, 3 Mar 2008 13:40:54 -0800 (PST) Organization: http://groups.google.com Lines: 105 Message-ID: NNTP-Posting-Host: 81.242.225.228 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: posting.google.com 1204580455 10759 127.0.0.1 (3 Mar 2008 21:40:55 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 3 Mar 2008 21:40:55 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: s12g2000prg.googlegroups.com; posting-host=81.242.225.228; posting-account=bDjECgoAAACcrK9JRCgKqh4l2ClSnzv7 User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12,gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:442080 X-Received-Date: Mon, 03 Mar 2008 16:40:55 EST (text.usenetserver.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>