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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
60 | 진행기록 | 운영자 | 2011.08.23 | 3327 |
59 | 1. 기본 개념 | 멋진넘 | 2011.06.28 | 13431 |
58 | 1. 테이블 파티셔닝 | suspace | 2011.06.21 | 6826 |
57 | 7장. 병렬 처리 | 운영자 | 2011.06.15 | 4622 |
56 | 6장. 파티셔닝 | 운영자 | 2011.06.15 | 3184 |
55 | 5장. 소트 튜닝 | 운영자 | 2011.06.15 | 3301 |
54 | 4장. 쿼리 변환 | 운영자 | 2011.06.15 | 3133 |
53 | 3장. 옵티마이저 원리 | 운영자 | 2011.06.15 | 3164 |
52 | 1. 소트 수행 원리 | 휘휘 | 2011.06.12 | 5755 |
51 | 1. 쿼리 변환이란? | 실천하자 | 2011.05.02 | 4939 |
50 | 1. 옵티마이저 | 실천하자 | 2011.04.18 | 11230 |
49 | 1. Nested Loops 조인 | suspace | 2011.03.23 | 7275 |
48 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.23 | 3324 |
47 | 1. 인덱스 구조 [1] | 실천하자 | 2011.02.16 | 14228 |
46 | 1장. 인덱스 원리와 활용 | 운영자 | 2011.06.15 | 5615 |
45 | 온라인 OT 및 스터디 툴 사용 방법 | 휘휘 | 2011.02.16 | 3339 |
44 | Front Page | 운영자 | 2011.02.16 | 149434 |
43 | 2. 인덱스 기본 원리 | 실천하자 | 2011.02.21 | 3378 |
42 | 2. 소트 머지 조인 | 실천하자 | 2011.03.22 | 5817 |
41 | 2. 옵티마이저 행동에 영향을 미치는 요소 | 휘휘 | 2011.04.18 | 5115 |