4.9 Outer 조인을 Inner 조인으로 변환

- Outer 조인문을 작성하면서 일부 조건절에 Outer 기호(+)를 빠뜨리면 Inner 조인할 때와 같은 결과

-- 준비단계

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

CREATE INDEX DEPT_LOC_IDX ON DEPT(LOC);

CREATE INDEX EMP_DEPTNO_IDX ON EMP(DEPTNO);

CREATE INDEX EMP_SAL_IDX ON EMP(SAL);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP');


SELECT *
FROM   EMP E, DEPT D
WHERE  D.DEPTNO(+) = E.DEPTNO
AND    D.LOC = 'DALLAS'
AND    E.SAL >= 1000;

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=57)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=3 Bytes=111)
   2    1     NESTED LOOPS (Cost=3 Card=1 Bytes=57)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=1 Bytes=20)
   4    3         INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX' (INDEX) (Cost=1 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=0 Card=4)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   1 - filter("E"."SAL">=1000)
   4 - access("D"."LOC"='DALLAS')
   5 - access("D"."DEPTNO"="E"."DEPTNO")
-----------------------------------------------------------

-- 실제로 Outer join이 실행되려면

SELECT *
FROM   EMP E, DEPT D
WHERE  D.DEPTNO(+) = E.DEPTNO
AND    D.LOC(+) = 'DALLAS'
AND    E.SAL >= 1000;

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=12 Card=10 Bytes=570)
   1    0   NESTED LOOPS (OUTER) (Cost=12 Card=10 Bytes=570)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=10 Bytes=370)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=20)
   4    3       INDEX (RANGE SCAN) OF 'I_DEPT' (NON-UNIQUE) (Card=1)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter("E"."SAL">=1000)
   3 - filter("D"."LOC"(+)='DALLAS')
   4 - access("D"."DEPTNO"(+)="E"."DEPTNO")
-----------------------------------------------------------

-- 쿼리변환 시행하는 이유는 조인순서를 자유롭게 결정하기 위해서다.
-- Outer NL, Outer 소트머지 조인시 드라이빙 테이블은 항상 Outer 기호가 붙지않은쪽으로 고정
-- Outer 해시조인의 경우 10g 부터 자유롭게 조인 순서가 바뀌도록 개선(9i는 그렇지 않다.)

-- 만약 dept테이블을 먼저 드라이빙하는게 유리함에도 Outer 조인 때문에 항상 emp 테이블을 드라이빙해야 한다면
   불리한 조건에서 최적화하는 것이 된다.
  
-- Outer 조인을 써야하는 상황이라면 Outer 기호를 정확히 구사해야 올바른 결과집합을 얻을수 있다.
-- ANSI Outer 조인문일 때는 Outer 기호 대신 조건절 위치에 신경을 써야한다.

select *
from  dept d left outer join emp e on d.deptno = e.deptno
where e.sal > 1000 ;

-----------------------------------------------------------
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=12 Bytes=684)
   1    0   MERGE JOIN (Cost=5 Card=12 Bytes=684)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (JOIN) (Cost=3 Card=12 Bytes=444)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=12 Bytes=444)
   6    5         INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=4)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO")
   4 - filter("D"."DEPTNO"="E"."DEPTNO")
   6 - access("E"."SAL">1000)
-----------------------------------------------------------


-- on 절에 기술해주어야한다.
select *
from  dept d left outer join emp e on d.deptno = e.deptno and e.sal > 1000;

-----------------------------------------------------------
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=12 Bytes=684)
   1    0   MERGE JOIN (OUTER) (Cost=5 Card=12 Bytes=684)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     SORT (JOIN) (Cost=3 Card=12 Bytes=444)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=12 Bytes=444)
   6    5         INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=4)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
   4 - filter("D"."DEPTNO"="E"."DEPTNO"(+))
   6 - access("E"."SAL"(+)>1000)
-----------------------------------------------------------

-- 결과집합은 아래와 같이 동일하다. 하지만 위의 Predicate 정보를 보면 확인 할 수 있다.
10    ACCOUNTING    NEW YORK    7782    CLARK    MANAGER   7839    2011/06/06 14:42:50    2450       
10    ACCOUNTING    NEW YORK    7839    KING     PRESIDENT         2011/06/06 14:42:51    5000       
20    RESEARCH      DALLAS      7876    ADAMS    CLERK     7788    2011/06/06 14:42:51    1100       
20    RESEARCH      DALLAS      7566    JONES    MANAGER   7839    2011/06/06 14:42:50    2975       
20    RESEARCH      DALLAS      7902    FORD     ANALYST   7566    2011/06/06 14:42:51    3000       
20    RESEARCH      DALLAS      7788    SCOTT    ANALYST   7566    2011/06/06 14:42:51    3000       
30    SALES         CHICAGO     7521    WARD     SALESMAN  7698    2011/06/06 14:42:50    1250    500
30    SALES         CHICAGO     7654    MARTIN   SALESMAN  7698    2011/06/06 14:42:50    1250    1400
30    SALES         CHICAGO     7698    BLAKE    MANAGER   7839    2011/06/06 14:42:50    2850       
30    SALES         CHICAGO     7499    ALLEN    SALESMAN  7698    2011/05/07 14:42:49    1600    300
30    SALES         CHICAGO     7844    TURNER   SALESMAN  7698    2011/06/06 14:42:51    1500    0 
40    OPERATIONS    BOSTON      7934    MILLER   CLERK     7782    2011/06/06 14:42:52    1300      

-- ANSI Outer 조인문에서 where 절에 기술한 Inner 쪽 필터조건이 의미있게 사용되는 경우는
   아래처럼 is null 조건을 체크하는 경우뿐
-- 조인에 실패하는 레코드를 찾고자 할때 흔히 사용되는 SQL

select *
from  dept d left outer join emp e on d.deptno = e.deptno
where e.empno is null;

-- no rows........

-----------------------------------------------------------
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=798)
   1    0   FILTER
   2    1     MERGE JOIN (OUTER) (Cost=6 Card=14 Bytes=798)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   4    3         INDEX (FULL SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
   5    2       SORT (JOIN) (Cost=4 Card=14 Bytes=518)
   6    5         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   1 - filter("E"."EMPNO" IS NULL)
   5 - access("D"."DEPTNO"="E"."DEPTNO"(+))
   5 - filter("D"."DEPTNO"="E"."DEPTNO"(+))
-----------------------------------------------------------

마지막으로 Outer 쪽 필터조건은 on절에 기술하든 where절에 기술하든 결과집합이나 성능에 하등차이가 없다.

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 6월  5일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^