RE: Why ANSI FULL Join Explain Plan includes View

It just means that Oracle creates intermediate view to handle query transformation.

Oracle transforms full outer join to union all view as you showed already.  

No additional I/O and no additional cost thus no change on execution plan.  

Your two execution plans are effectively same.  

PS) As of Oracle 11g, we have a real full outer join with no clever but meaningless transformation.    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rumpi Gravenstein
Sent: Monday, January 21, 2008 2:22 PM
To: oracle-l
Subject: Why ANSI FULL Join Explain Plan includes View  

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

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

--

Rumpi Gravenstein

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jan 21 2008 - 01:24:23 CST