9. Outer 조인을 Inner 조인으로 변환
2011.06.07 01:30
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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
40 | 8. 고급 조인 테크닉-2 | suspace | 2011.04.05 | 7018 |
39 | 2. 옵티마이저 행동에 영향을 미치는 요소 | 휘휘 | 2011.04.18 | 5096 |
38 | 1. 옵티마이저 | 실천하자 | 2011.04.18 | 11213 |
37 | 3. 옵티마이저의 한계 | 멋진넘 | 2011.04.19 | 7858 |
36 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10921 |
35 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.26 | 18100 |
34 | 5. 카디널리티 | suspace | 2011.04.26 | 5954 |
33 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31097 |
32 | 1. 쿼리 변환이란? | 실천하자 | 2011.05.02 | 4930 |
31 | 7. 비용 | 휘휘 | 2011.05.03 | 6173 |
30 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23390 |
29 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.16 | 19705 |
28 | 5. 조건절 이행 | 휘휘 | 2011.05.30 | 5409 |
27 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4596 |
26 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8720 |
25 | 4. 조건절 Pushing | 실천하자 | 2011.05.31 | 7024 |
24 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.03 | 6646 |
23 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5223 |
» | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.07 | 7834 |
21 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5531 |