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