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 | Front Page | 운영자 | 2011.02.16 | 149420 |
59 | 3. 인덱스 파티셔닝 | darkbeom | 2011.06.20 | 53850 |
58 | 3. 다양한 인덱스 스캔 방식 | 멋진넘 | 2011.02.19 | 33811 |
57 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31103 |
56 | 2. 파티션 Pruning | 실천하자 | 2011.06.22 | 26021 |
55 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23390 |
54 | 3. 해시 조인 | darkbeom | 2011.03.21 | 21533 |
53 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.16 | 19705 |
52 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.26 | 18100 |
51 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.09 | 16909 |
50 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.14 | 15080 |
49 | 4. 테이블 Random 액세스 부하 [1] | darkbeom | 2011.02.24 | 14678 |
48 | 4. 조인 순서의 중요성 | 운영자 | 2011.03.28 | 14244 |
47 | 1. 인덱스 구조 [1] | 실천하자 | 2011.02.16 | 14205 |
46 | 1. 기본 개념 | 멋진넘 | 2011.06.28 | 13405 |
45 | 8. 고급 조인 테크닉-1 [1] | darkbeom | 2011.04.04 | 13272 |
44 | 9. 비트맵 인덱스 | 실천하자 | 2011.03.06 | 12356 |
43 | 1. 옵티마이저 | 실천하자 | 2011.04.18 | 11213 |
42 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10923 |
41 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 실천하자 | 2011.06.14 | 8861 |