WareValley http://www.WareValley.com

Database Audit and Protection [ DB 접근통제 ]

Database Encryption [ DB 암호화 ]

Database Vulnerability Assessment [ DB 취약점 분석 ]

Database SQL Query Approval [ DB 작업결재 ]

Database Performance Monitoring and Management [ DB 성능관리 및 개발 ]

WareValley

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

오렌지팀 윤석준 선임연구원

Database security and management, WareValley. http://www.WareValley.com

Oracle Outer Left Join

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; ?

Database security and management, WareValley. http://www.WareValley.com

Oracle Outer Left Join (계속)

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;

이렇게 나오리라 기대했겠지만 ? 현실은 …

왜죠 ? 버그인가… 아닙니다 !

Database security and management, WareValley. http://www.WareValley.com

Oracle Outer Left Join (계속)

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;

Oracle 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 AND E.EMPNO > 7600)(+) D;

LATERAL ? 그게 멍가영 ?

Database security and management, WareValley. http://www.WareValley.com

Leteral View (Scalar Inline View)

FROM 절에서 앞서 나타난 다른 Table을 참조하는 관계를 포함하는 Inline View

CREATE TABLE Sales(Year INTEGER NOT NULL PRIMARY KEY, Q1 INTEGER, Q2 INTEGER, Q3 INTEGER, Q4 INTEGER); INSERT INTO Sales VALUES (2004, 20, 30, 15, 10), (2005, 18, 40, 12, 27);

SELECT Year, Quarter, Results FROM Sales AS S, LATERAL(VALUES (1, S.Q1), (2, S.Q2), (3, S.Q3), (4, S.Q4)) AS T(Quarter, Results);

Year Quarter Results

2004 1 20

2004 2 30

2004 3 15

2004 4 10

2005 1 18

2005 2 40

2005 3 12

2005 4 27

e.g. DB2 Query 문법

Database security and management, WareValley. http://www.WareValley.com

Oracle Ansi Left Outer Join

Optimizer가 내부적으로 Left Outer Joined Lateral View로 표현

(사용자가 명시적으로 Leteral View를 사용하는 것은 12c부터 가능)

결과 건수에 영향을 미치지 않는 Scalar Inline View

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;

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;

Database security and management, WareValley. http://www.WareValley.com

Oracle Ansi Left Outer Join (계속)

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;

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;

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;

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;