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 Bytes=42)

--

Rumpi Gravenstein

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jan 20 2008 - 23:22:25 CST