메뉴 건너뛰기

bysql.net

6. 조인 제거

2011.05.30 20:10

멋진넘 조회 수:4592

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 부터 발생함

 

번호 제목 글쓴이 날짜 조회 수
40 10. 실체화 뷰 쿼리로 재작성 suspace 2011.06.07 5530
39 9. Outer 조인을 Inner 조인으로 변환 darkbeom 2011.06.07 7834
38 8. 공통 표현식 제거 darkbeom 2011.06.07 5218
37 12. 기타 쿼리 변환 [3] 실천하자 2011.06.03 6645
36 4. 조건절 Pushing 실천하자 2011.05.31 7018
35 7. OR-Expansion 멋진넘 2011.05.31 8717
» 6. 조인 제거 멋진넘 2011.05.30 4592
33 5. 조건절 이행 휘휘 2011.05.30 5406
32 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19699
31 3. 뷰 Merging 실천하자 2011.05.15 23377
30 7. 비용 휘휘 2011.05.03 6166
29 1. 쿼리 변환이란? 실천하자 2011.05.02 4925
28 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31058
27 5. 카디널리티 suspace 2011.04.26 5953
26 4. 통계정보 Ⅰ darkbeom 2011.04.26 18086
25 6. 히스토그램 실천하자 2011.04.25 10917
24 3. 옵티마이저의 한계 멋진넘 2011.04.19 7856
23 1. 옵티마이저 file 실천하자 2011.04.18 11210
22 2. 옵티마이저 행동에 영향을 미치는 요소 휘휘 2011.04.18 5091
21 8. 고급 조인 테크닉-2 file suspace 2011.04.05 7014