6. 조인 제거
2011.05.30 20:10
Chapter 6 - Join Elimination
1:M조인을 하는 테이블 중 조인에만 참가하고 조건절이나 Select List에서 참조되지
않는 1쪽 테이블이 있다면 결과집합에 영향을 미치지 않으므로 M쪽 테이블만 읽도록
쿼리를 변환하는데, 이를 '조인 제거(Join Elimination' 또는 '테이블 제거(Table
Elimination)'라고 한다.(★SELECT LIST에 1쪽의 컬럼이 존재한다면 작동안함★)
JE의 기능은 Oracle 10g부터 작동하기 시작(SQL Server의 경우 오래 전부터 적용)
1. JE작동을 위한 전제조건
▶ PK/FK의 Relation이 반드시 존재해야 함
PK : 두 테이블간의 조인 카디널리티를 파악하기 위해서 필수
FK : NULL을 제외한 레코드 중 조인이 실패하는 레코드가 없다는 것을 보장
NULL에 대한 처리를 위해 옵티마이져는 내부적으로 IS NOT NULL조건을 추가함
(NULL과의 비교는 비교불능이기에...내부적으로 처리함. 아래 참조)
아래의 내용은 11g에서 실습한 내용임
- 황배씨 서버 : 11g Enterprise Edition Release 11.2.0.1.0
- SCOTT 계정대신 HR 계정사용함
2. OPTIMIZER_FEATURES_ENABLE 힌트 사용
- 버전별 테스트가 가능함
- 당연히 상위 버전에서 하위버전 테스트만 가능함
3. SELECT LIST에 1쪽의 컬럼이 존재한다면...
SELECT /*+ ORDERED USE_NL(E, D) */
E.*, D.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9540 | 111 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 106 | 9540 | 111 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7383 | 4 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
▶JE 발생안함(SELECT LIST에 1쪽의 컬럼 존재함)
4. PK/FK의 Relation 관계가 없다면...
ALTER TABLE HR.EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK;
SELECT /*+ */
E.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7738 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 7738 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 4 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
ALTER TABLE HR.EMPLOYEES ADD
(
CONSTRAINT EMP_DEPT_FK
FOREIGN KEY ( DEPARTMENT_ID )
REFERENCES HR.DEPARTMENTS ( DEPARTMENT_ID )
);
5. 1:M 에서 PK/FK 의 RELATION이 설정되어 있는 경우에 있어서 VERSION별 처리
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0.8') */
E.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7738 | 2 |
| 1 | NESTED LOOPS | | 106 | 7738 | 2 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 2 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 1 | 4 | |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.4') */
E.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7314 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 106 | 7314 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) ▶내부적으로 IS NOT NULL조건을 추가함
6. OUTER JOIN에도 JE가 가능한데 10g까지는 구현되지 않았음(11g부터 구현가능함)
7. 1:M의 RELATION이 설정되어 있는 경우에 있어서 VERSION별 처리
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.4') */
E.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7811 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 107 | 7811 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 4 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
SELECT /*+ */
E.*
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE 1 = 1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8. 정리
Join Elimination ▶10g 부터 발생함
Outer Join Elimination ▶11g 부터 발생함
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
20 | 1장. 인덱스 원리와 활용 | 운영자 | 2011.06.15 | 5608 |
19 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5531 |
18 | 5. 조건절 이행 | 휘휘 | 2011.05.30 | 5409 |
17 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5223 |
16 | 2. 옵티마이저 행동에 영향을 미치는 요소 | 휘휘 | 2011.04.18 | 5096 |
15 | 5. Outer 조인 | 실천하자 | 2011.03.30 | 5030 |
14 | 1. 쿼리 변환이란? | 실천하자 | 2011.05.02 | 4930 |
13 | 7장. 병렬 처리 | 운영자 | 2011.06.15 | 4611 |
» | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4596 |
11 | 2. 소트를 발생시키는 오퍼레이션 | 휘휘 | 2011.06.12 | 3456 |
10 | 2. 인덱스 기본 원리 | 실천하자 | 2011.02.21 | 3370 |
9 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3356 |
8 | 온라인 OT 및 스터디 툴 사용 방법 | 휘휘 | 2011.02.16 | 3332 |
7 | 진행기록 | 운영자 | 2011.08.23 | 3319 |
6 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.23 | 3315 |
5 | 5장. 소트 튜닝 | 운영자 | 2011.06.15 | 3288 |
4 | 6장. 파티셔닝 | 운영자 | 2011.06.15 | 3174 |
3 | 3장. 옵티마이저 원리 | 운영자 | 2011.06.15 | 3152 |
2 | 4장. 쿼리 변환 | 운영자 | 2011.06.15 | 3124 |
1 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2753 |