RE: Why ANSI FULL Join Explain Plan includes View

  • From: "Ukja.dion" <ukja.dion@xxxxxxxxx>
  • To: <rgravens@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 21 Jan 2008 16:24:23 +0900

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: