Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!s37g2000prg.googlegroups.com!not-for-mail From: Gokul Newsgroups: comp.databases.oracle.server Subject: Re: Delete behaviour Date: Mon, 3 Mar 2008 15:56:59 -0800 (PST) Organization: http://groups.google.com Lines: 118 Message-ID: <23d47e59-7ccb-43bc-87ef-7e2918df68b1@s37g2000prg.googlegroups.com> References: <1204584523.447815@bubbleator.drizzle.com> NNTP-Posting-Host: 81.242.226.7 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: posting.google.com 1204588714 28293 127.0.0.1 (3 Mar 2008 23:58:34 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 3 Mar 2008 23:58:34 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: s37g2000prg.googlegroups.com; posting-host=81.242.226.7; 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:442088 X-Received-Date: Mon, 03 Mar 2008 18:58:35 EST (text.usenetserver.com) On Mar 3, 11:48 pm, DA Morgan 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...@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.