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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
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: