Oracle Outer Join 과 Leteral에 대한 주의사항

Oracle의 ANSI OUTER JOIN 사용시 조심해야 할 것이 있다.

Oracle에서 ANSI LEFT OUTER JOIN은 내부적으로 LEFT OUTER JOINED LATERAL VIEW로 표현된다. (Oracle은 ANSI SQL 2003 에서는 Lateral View를  내부적으로만 사용하지 사용자에게 지원해 주지는 않는다. Query에서 명시적으로 사용하는 것은 12c 부터 가능하다.)

Lateral View는 FROM 절에서 앞서 나타난 다른 TABLE을 참조하는 관계를 포함하는 INLINE VIEW이다. 즉 Scalar Inline View 라고 보면 된다.

아래 Query 내용들을 보고 과연 저게 버그가 아닌지 한번 살펴보자.

SELECT * FROM SCOTT.EMP;

SELECT * FROM SCOTT.DEPT;

SELECT EMPNO, ENAME, DNAME, D.DEPTNO

  FROM SCOTT.EMP E LEFT OUTER JOIN SCOTT.DEPT D

      ON E.DEPTNO = D.DEPTNO

    AND E.EMPNO > 7600;

뭔가 이상하다. 우리가 예상한 결과랑 다르다. 사번이 7600이상인 것만 나와야 할텐데, 7600이상인 것에 대해서만 OUTER JOIN이 이루어졌다. 버그인가 ?

Oracle Optimizer가 위 SQL문을 어떻게 변환했는지를 보면 버그가 아니란 것을 알 수 있다.

SELECT EMPNO, ENAME, DNAME, D.DEPTNO

 FROM SYS.EMP E,

       LATERAL(SELECT D.DEPTNO, D.DNAME

                       FROM SYS.DEPT D

                     WHERE E.DEPTNO = D.DEPTNO

                       AND E.EMPNO > 7600)(+) D;

위의 Lateral Inline View는 결과 건수에는 영향을 미치지 않는 Scalar Inline View 이다. 즉 7600이상이라는 조건은 결과건수에는 영향을 미치지 않고 Join 건수에만 영향을 미친다.

그럼 제대로 결과가 나오게 할려면 어떻게 해야 할까 ?

SELECT EMPNO, ENAME, DNAME, D.DEPTNO

 FROM SCOTT.EMP E LEFT OUTER JOIN SCOTT.DEPT D

     ON E.DEPTNO = D.DEPTNO

  WHERE E.EMPNO > 7600;

위의 Query를 실행시키면 제대로 결과가 나온다.
위 Query는 Optimizer가 아래와 같이 변환시킨다.

SELECT EMPNO, ENAME, DNAME, D.DEPTNO

 FROM SYS.EMP E,

       LATERAL(SELECT D.DEPTNO, D.DNAME

                       FROM SYS.DEPT D

                     WHERE E.DEPTNO = D.DEPTNO)(+) D

  WHERE E.EMPNO > 7600;

참고 : http://scidb.tistory.com/14
         http://docs.oracle.com/database/121/SQLRF/statements_10002.htm