by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

9. Outer 조인을 Inner 조인으로 변환




변환 목적

  1. 조인 순서를 자유롭게 결정할 수 있어야 쿼리 최적화 가능
  2. ANSI Outer join문 : 조건절 위치 선택에 주의
    1. where절에 기술한 inner 쪽 필터 조건이 의미 있게 사용되는 경우 : is null 조건을 체크 혹은 조인에 실패하는 레코드를 찾고자 할 때 흔히 사용
    2. outer 쪽 필터조건은 on절에 기술하든 where절에 기술하든 결과집합이나 성능에 차이 없음
  3. 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
      */
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.