Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 10343 invoked from network); 21 Jan 2008 00:10:51 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 21 Jan 2008 00:10:51 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 380D17E955C; Mon, 21 Jan 2008 01:10:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07660-09; Mon, 21 Jan 2008 01:10:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9BDFE7E94CE; Mon, 21 Jan 2008 01:10:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 21 Jan 2008 00:22:27 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 318DB7E82A6 for ; Mon, 21 Jan 2008 00:22:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01392-06 for ; Mon, 21 Jan 2008 00:22:27 -0500 (EST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.178]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A0297E82F6 for ; Mon, 21 Jan 2008 00:22:25 -0500 (EST) Received: by wa-out-1112.google.com with SMTP id k22so3708342waf.18 for ; Sun, 20 Jan 2008 21:22:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=r8E1EfqbbD5inKvfaMsoJNghjAbEiigjxM16NELtitg=; b=Fu0pXGnMbl9mthbpkCDvpy0HKKP4kYsYv/30FpWeqd3buHDxSyIU0L+OLzjT1WZRh+lTXQpFITNm9TTYZuuoRcw2baGaUfEsZZxW4VgUsHlUre6yOlYrcwFvbbQDKpAmWZCQq2Cyf6ZSJzlsVJtI1nK7YlrBBIJVrsqM8FHUZOI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=idl94XZsvNfUfexu5hiUGR0xuMl1lq6KacjiNO/p4evVkkjs3AM0dGyrz8sSfuyN6k1LNtPrb0qN7SPLSMTZIY1bAwsHSQAiW8QHTtApIj2L0AMpmaByuj7GAxtjpu1J9xH3vPSQ8C0B1TwKq/SnvOzq1mgzDEo/uwUgFPfVlog= Received: by 10.142.214.5 with SMTP id m5mr2948349wfg.89.1200892945170; Sun, 20 Jan 2008 21:22:25 -0800 (PST) Received: by 10.142.102.16 with HTTP; Sun, 20 Jan 2008 21:22:25 -0800 (PST) Message-ID: <9c9b9dc90801202122w75a2c146t2af083b1aaaadcf4@mail.gmail.com> Date: Mon, 21 Jan 2008 00:22:25 -0500 From: "Rumpi Gravenstein" To: oracle-l Subject: Why ANSI FULL Join Explain Plan includes View MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11849_33419167.1200892945174" X-archive-position: 4797 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: rgravens@gmail.com Precedence: normal Reply-to: rgravens@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_11849_33419167.1200892945174 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline All, I have two statements I believe are equivalent that have slightly different explain plans. I'm at a loss to explain why the FULL syntax includes the view statement. I'm running this test on Oracle 10.1.0.2.0. Any thoughts why the explain plans are different? Statement 1) SELECT e.ename, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno(+) 4 UNION ALL 5 SELECT NULL, d.dname 6 FROM dept d 7 WHERE NOT EXISTS 8 (SELECT 1 9 FROM emp e 10 WHERE e.deptno = d.deptno); 16 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=16 Bytes=324) 1 0 UNION-ALL 2 1 HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=294) 3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126) 4 2 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60) 5 1 HASH JOIN (ANTI) (Cost=7 Card=2 Bytes=30) 6 5 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60) 7 5 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42) And select e.ename, d.dname 2 FROM emp e 3 FULL JOIN dept d 4 ON (e.deptno = d.deptno); 16 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=16 Bytes=256) * 1 0 VIEW (Cost=13 Card=16 Bytes=256)* 2 1 UNION-ALL 3 2 HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=294) 4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126) 5 3 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60) 6 2 HASH JOIN (ANTI) (Cost=7 Card=2 Bytes=30) 7 6 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60) 8 6 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42) -- Rumpi Gravenstein ------=_Part_11849_33419167.1200892945174 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline All,

I have two statements I believe are equivalent that have slightly different explain plans.  I'm at a loss to explain why the FULL syntax includes the view statement.  I'm running this test on Oracle 10.1.0.2.0 .  Any thoughts why the explain plans are different?

Statement 1)
SELECT e.ename, d.dname
  2    FROM emp e, dept d
  3   WHERE e.deptno = d.deptno(+)
  4  UNION ALL
  5  SELECT NULL, d.dname
  6    FROM dept d
  7   WHERE NOT EXISTS
  8   (SELECT 1
  9      FROM emp e
 10     WHERE e.deptno = d.deptno);

16 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=16 Bytes=324)
   1    0   UNION-ALL
   2    1     HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=294)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126)
   4    2       TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60)
   5    1     HASH JOIN (ANTI) (Cost=7 Card=2 Bytes=30)
   6    5       TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60)
   7    5       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)


And

select e.ename, d.dname
  2    FROM emp e
  3              FULL JOIN dept d
  4                 ON ( e.deptno = d.deptno);

16 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=16 Bytes=256)
   1    0   VIEW (Cost=13 Card=16 Bytes=256)
   2    1     UNION-ALL
   3    2       HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=294)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126)
   5    3         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60)
   6    2       HASH JOIN (ANTI) (Cost=7 Card=2 Bytes=30)
   7    6         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=5 Bytes=60)
   8    6         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)

--
Rumpi Gravenstein ------=_Part_11849_33419167.1200892945174-- -- http://www.freelists.org/webpage/oracle-l