[SQL학원,오라클학원☞탑크리에듀][Full outer join과 outer join, anti-join과의 비교]

[Full outer join과 outer join, anti-join과의 비교]

set autotrace traceonly

select e.empno

from myemp1 e , mydept1 d

where e.deptno(+) = d.deptno

UNION ALL

select e.empno

from myemp1 e , mydept1 d

where e.deptno = d.deptno(+)

 and d.deptno is null;

경   과: 00:00:56.35

-----------------------------------------------------------------------------------

| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |            |    12M|   119M| 31371  (51)| 00:06:17

|   1 |  UNION-ALL            |            |       |       |            |

|*  2 |   HASH JOIN OUTER     |            |    10M|    95M| 15686   (2)| 00:03:09

|   3 |    INDEX FULL SCAN    | PK_MYDEPT1 |     7 |    14 |     1   (0)| 00:00:01

|   4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|    76M| 15614   (2)| 00:03:08

|*  5 |   HASH JOIN RIGHT ANTI|            |  2500K|    23M| 15686   (2)| 00:03:09

|   6 |    INDEX FULL SCAN    | PK_MYDEPT1 |     7 |    14 |     1   (0)| 00:00:01

|   7 |    TABLE ACCESS FULL  | MYEMP1     |    10M|    76M| 15614   (2)| 00:03:08

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("E"."DEPTNO"(+)="D"."DEPTNO")

   5 - access("E"."DEPTNO"="D"."DEPTNO")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

     841949  consistent gets

     183394  physical reads

          0  redo size

  192889570  bytes sent via SQL*Net to client

    7333856  bytes received via SQL*Net from client

     666669  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

   10000006  rows processed

select e.empno

from myemp1 e full outer join mydept1 d

on (e.deptno = d.deptno);

경   과: 00:00:47.89

Execution Plan

-------------------------------------------------------------------------------------

| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |            |    10M|   123M| 15687   (2)| 00:03:09 |

|   1 |  VIEW                  | VW_FOJ_0   |    10M|   123M| 15687   (2)| 00:03:09 |

|*  2 |   HASH JOIN FULL OUTER |            |    10M|    95M| 15687   (2)| 00:03:09 |

|   3 |    INDEX FAST FULL SCAN| PK_MYDEPT1 |     7 |    14 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | MYEMP1     |    10M|    76M| 15614   (2)| 00:03:08 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("E"."DEPTNO"="D"."DEPTNO")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

     750333  consistent gets

      91697  physical reads

          0  redo size

  192889570  bytes sent via SQL*Net to client

    7333856  bytes received via SQL*Net from client

     666669  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

   10000006  rows processed