꿈꾸는 개발자, DBA 커뮤니티 구루비

변환 목적

조인 순서를 자유롭게 결정할 수 있어야 쿼리 최적화 가능
ANSI Outer join문 : 조건절 위치 선택에 주의
1. where절에 기술한 inner 쪽 필터 조건이 의미 있게 사용되는 경우 : is null 조건을 체크 혹은 조인에 실패하는 레코드를 찾고자 할 때 흔히 사용
2. outer 쪽 필터조건은 on절에 기술하든 where절에 기술하든 결과집합이나 성능에 차이 없음
Lateral View
1. 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰라고 생각하면 된다.
  다시 말하면, ANSI left outer join에서 해당 값이 없는 테이블(+기호가 붙은 테이블)을 스칼라 인라인뷰로 만든 결과 집합이다.
2. Oracle 에서는, ANSI left outer join은 내부적으로 left outer joined lateral views로 표현된다.
3. 오라클만이 내부적으로 사용할수 있으며, 사용자가 사용할 경우 에러가 발생한다.
4. 참고 사이트 : http://scidb.tistory.com/entry/Outer-Join-의-재조명

테스트

Outer 기호(+)


Current SQL statement for this session:
select *
from   emp e, dept d
where  d.deptno(+) = e.deptno
and    d.loc = 'DALLAS'
and    e.sal >= 1000

Transformation SQL statement : 
SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
       "D"."DNAME" "DNAME", "D"."LOC" "LOC"
  FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
 WHERE "D"."DEPTNO"(+) = "E"."DEPTNO" AND "D"."LOC" = 'DALLAS'
       AND "E"."SAL" >= 1000
       
SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
       "D"."DNAME" "DNAME", "D"."LOC" "LOC"
  FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
 WHERE "D"."DEPTNO" = "E"."DEPTNO"
   AND "D"."LOC" = 'DALLAS'
   AND "E"."SAL" >= 1000
   
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name          | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |               |       |       |     3 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID   | EMP           |     5 |   185 |     1 |  00:00:01 |
| 2   |   NESTED LOOPS                 |               |     5 |   285 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | DEPT          |     1 |    20 |     2 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | DEPT_LOC_IDX  |     1 |       |     1 |  00:00:01 |
| 5   |    INDEX RANGE SCAN            | EMP_DEPTNO_IDX|     5 |       |     0 |           |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("E"."SAL">=1000)
4 - access("D"."LOC"='DALLAS')
5 - access("D"."DEPTNO"="E"."DEPTNO")
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 319292506
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$6E71C6F6")
      ELIMINATE_OUTER_JOIN(@"SEL$1")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$6E71C6F6" "D"@"SEL$1" ("DEPT"."LOC"))
      INDEX(@"SEL$6E71C6F6" "E"@"SEL$1" ("EMP"."DEPTNO"))
      LEADING(@"SEL$6E71C6F6" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$6E71C6F6" "E"@"SEL$1")
    END_OUTLINE_DATA
  */

ANSI OUTER JOIN inner table의 필터 조건을 where 절에 기술 : outer join을 inner join으로 변환


Current SQL statement for this session:
select *
from  dept d left outer join emp e on d.deptno = e.deptno
where e.sal > 1000 

Transformation SQL statement : 
SELECT "D"."DEPTNO" "QCSJ_C000000000300000", "D"."DNAME" "DNAME",
       "D"."LOC" "LOC", "from$_subquery$_004"."EMPNO_0" "EMPNO",
       "from$_subquery$_004"."ENAME_1" "ENAME",
       "from$_subquery$_004"."JOB_2" "JOB",
       "from$_subquery$_004"."MGR_3" "MGR",
       "from$_subquery$_004"."HIREDATE_4" "HIREDATE",
       "from$_subquery$_004"."SAL_5" "SAL",
       "from$_subquery$_004"."COMM_6" "COMM",
       "from$_subquery$_004"."DEPTNO_7" "QCSJ_C000000000300001"
  FROM "HEAEUN"."DEPT" "D", 
  LATERAL
  ( (SELECT "E"."EMPNO" "EMPNO_0", "E"."ENAME" "ENAME_1", "E"."JOB" "JOB_2",
       "E"."MGR" "MGR_3", "E"."HIREDATE" "HIREDATE_4", "E"."SAL" "SAL_5",
       "E"."COMM" "COMM_6", "E"."DEPTNO" "DEPTNO_7"
  FROM "HEAEUN"."EMP" "E"
 WHERE "D"."DEPTNO" = "E"."DEPTNO"))(+) "from$_subquery$_004"

SELECT "D"."DEPTNO" "DEPTNO", "D"."DNAME" "DNAME", "D"."LOC" "LOC",
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO"
  FROM "HEAEUN"."DEPT" "D", "HEAEUN"."EMP" "E"
 WHERE "E"."SAL" > 1000 AND "D"."DEPTNO" = "E"."DEPTNO"
 
============
Plan Table
============
----------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name       | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |            |       |       |     5 |           |
| 1   |  MERGE JOIN                    |            |    13 |   741 |     5 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID  | DEPT       |     4 |    80 |     2 |  00:00:01 |
| 3   |    INDEX FULL SCAN             | DEPT_PK    |     4 |       |     1 |  00:00:01 |
| 4   |   SORT JOIN                    |            |    13 |   481 |     3 |  00:00:01 |
| 5   |    TABLE ACCESS BY INDEX ROWID | EMP        |    13 |   481 |     2 |  00:00:01 |
| 6   |     INDEX RANGE SCAN           | EMP_SAL_IDX|    13 |       |     1 |  00:00:01 |
----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
4 - filter("D"."DEPTNO"="E"."DEPTNO")
6 - access("E"."SAL">1000)
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 4011727948
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3BAA97A7")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$23D58506")
      ELIMINATE_OUTER_JOIN(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$3BAA97A7" "D"@"SEL$2" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$3BAA97A7" "E"@"SEL$1" ("EMP"."SAL"))
      LEADING(@"SEL$3BAA97A7" "D"@"SEL$2" "E"@"SEL$1")
      USE_MERGE(@"SEL$3BAA97A7" "E"@"SEL$1")
    END_OUTLINE_DATA
  */

ANSI OUTER JOIN inner table의 필터 조건을 on 절에 기술 : outer join을 inner join으로 변환하지 않음


Current SQL statement for this session:
select *
from  dept d left outer join emp e on d.deptno = e.deptno and e.sal > 1000

Transformation SQL statement : 
※ e.sal > 1000  조건은 결과건수에 영향을 못미치고 DEPT 와의 조인건수에만 영향을 미친다.
  다시말해서 e.sal > 1000 에 만족하는건만 DEPT 와 조인한다.

SELECT "D"."DEPTNO" "QCSJ_C000000000300000", "D"."DNAME" "DNAME",
       "D"."LOC" "LOC", "from$_subquery$_004"."EMPNO_0" "EMPNO",
       "from$_subquery$_004"."ENAME_1" "ENAME",
       "from$_subquery$_004"."JOB_2" "JOB",
       "from$_subquery$_004"."MGR_3" "MGR",
       "from$_subquery$_004"."HIREDATE_4" "HIREDATE",
       "from$_subquery$_004"."SAL_5" "SAL",
       "from$_subquery$_004"."COMM_6" "COMM",
       "from$_subquery$_004"."DEPTNO_7" "QCSJ_C000000000300001"
  FROM "HEAEUN"."DEPT" "D", 
LATERAL((SELECT "E"."EMPNO" "EMPNO_0", "E"."ENAME" "ENAME_1", "E"."JOB" "JOB_2",
         "E"."MGR" "MGR_3", "E"."HIREDATE" "HIREDATE_4", "E"."SAL" "SAL_5",
         "E"."COMM" "COMM_6", "E"."DEPTNO" "DEPTNO_7"
    FROM "HEAEUN"."EMP" "E"
   WHERE "D"."DEPTNO" = "E"."DEPTNO" AND "E"."SAL" > 1000))(+) "from$_subquery$_004"

SELECT "D"."DEPTNO" "DEPTNO", "D"."DNAME" "DNAME", "D"."LOC" "LOC",
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO"
  FROM "HEAEUN"."DEPT" "D", "HEAEUN"."EMP" "E"
 WHERE "E"."SAL"(+) > 1000 AND "D"."DEPTNO" = "E"."DEPTNO"(+) 

============
Plan Table
============
----------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name       | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |            |       |       |     5 |           |
| 1   |  MERGE JOIN OUTER              |            |    13 |   741 |     5 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID  | DEPT       |     4 |    80 |     2 |  00:00:01 |
| 3   |    INDEX FULL SCAN             | DEPT_PK    |     4 |       |     1 |  00:00:01 |
| 4   |   SORT JOIN                    |            |    13 |   481 |     3 |  00:00:01 |
| 5   |    TABLE ACCESS BY INDEX ROWID | EMP        |    13 |   481 |     2 |  00:00:01 |
| 6   |     INDEX RANGE SCAN           | EMP_SAL_IDX|    13 |       |     1 |  00:00:01 |
----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
4 - filter("D"."DEPTNO"="E"."DEPTNO")
6 - access("E"."SAL">1000)
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 584284158
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$9E43CB6E")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$9E43CB6E" "D"@"SEL$2" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$9E43CB6E" "E"@"SEL$1" ("EMP"."SAL"))
      LEADING(@"SEL$9E43CB6E" "D"@"SEL$2" "E"@"SEL$1")
      USE_MERGE(@"SEL$9E43CB6E" "E"@"SEL$1")
    END_OUTLINE_DATA
  */

where절에 기술한 inner 쪽 필터 조건이 의미 있게 사용되는 경우 : is null 조건을 체크


Current SQL statement for this session:
select *
from  dept d left outer join emp e on d.deptno = e.deptno
where e.empno is null

Transformation SQL statement : 
SELECT "D"."DEPTNO" "QCSJ_C000000000300000", "D"."DNAME" "DNAME",
       "D"."LOC" "LOC", "from$_subquery$_004"."EMPNO_0" "EMPNO",
       "from$_subquery$_004"."ENAME_1" "ENAME",
       "from$_subquery$_004"."JOB_2" "JOB",
       "from$_subquery$_004"."MGR_3" "MGR",
       "from$_subquery$_004"."HIREDATE_4" "HIREDATE",
       "from$_subquery$_004"."SAL_5" "SAL",
       "from$_subquery$_004"."COMM_6" "COMM",
       "from$_subquery$_004"."DEPTNO_7" "QCSJ_C000000000300001"
  FROM "HEAEUN"."DEPT" "D",
LATERAL ((SELECT "E"."EMPNO" "EMPNO_0", "E"."ENAME" "ENAME_1", "E"."JOB" "JOB_2",
         "E"."MGR" "MGR_3", "E"."HIREDATE" "HIREDATE_4", "E"."SAL" "SAL_5",
         "E"."COMM" "COMM_6", "E"."DEPTNO" "DEPTNO_7"
    FROM "HEAEUN"."EMP" "E"
   WHERE "D"."DEPTNO" = "E"."DEPTNO"))(+) "from$_subquery$_004"

SELECT "D"."DEPTNO" "DEPTNO", "D"."DNAME" "DNAME", "D"."LOC" "LOC",
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO"
  FROM "HEAEUN"."DEPT" "D", "HEAEUN"."EMP" "E"
 WHERE "E"."EMPNO" IS NULL AND "D"."DEPTNO" = "E"."DEPTNO"(+)
 
SELECT "D"."DEPTNO" "DEPTNO", "D"."DNAME" "DNAME", "D"."LOC" "LOC",
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO"
  FROM "HEAEUN"."DEPT" "D", "HEAEUN"."EMP" "E"
 WHERE "D"."DEPTNO" = "E"."DEPTNO"(+) AND "E"."EMPNO" IS NULL

============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |         |       |       |     6 |           |
| 1   |  FILTER                        |         |       |       |       |           |
| 2   |   MERGE JOIN OUTER             |         |    14 |   798 |     6 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | DEPT    |     4 |    80 |     2 |  00:00:01 |
| 4   |     INDEX FULL SCAN            | DEPT_PK |     4 |       |     1 |  00:00:01 |
| 5   |    SORT JOIN                   |         |    14 |   518 |     4 |  00:00:01 |
| 6   |     TABLE ACCESS FULL          | EMP     |    14 |   518 |     3 |  00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("E"."EMPNO" IS NULL)
5 - access("D"."DEPTNO"="E"."DEPTNO")
5 - filter("D"."DEPTNO"="E"."DEPTNO")
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 3289000527
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$9E43CB6E")
      MERGE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$9E43CB6E" "D"@"SEL$2" ("DEPT"."DEPTNO"))
      FULL(@"SEL$9E43CB6E" "E"@"SEL$1")
      LEADING(@"SEL$9E43CB6E" "D"@"SEL$2" "E"@"SEL$1")
      USE_MERGE(@"SEL$9E43CB6E" "E"@"SEL$1")
    END_OUTLINE_DATA
  */

9. Outer 조인을 Inner 조인으로 변환 (by joang77) [2010.10.16]

변환 목적

테스트