Why ANSI FULL Join Explain Plan includes View

  • From: "Rumpi Gravenstein" <rgravens@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 21 Jan 2008 00:22:25 -0500

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

Other related posts: