메뉴 건너뛰기

bysql.net

6. 조인 제거

2011.06.01 23:32

AskZZang 조회 수:5440

06 조인 제거

 

조인 제거(Join Elimination) / 테이블 제거(Table Elimination)

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는 다면, 쿼리 수행시 1쪽 테이블은 읽지 않아도 된다.

결가집합에 영향을 미치지 않기 때문이다.

?  옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환한다.

 

)

select e.empno, e.ename, e.deptno, e.sal, e.hiredate

from   dept d, emp e

where  d.deptno = e.deptno;

 

위 쿼리에서 조인 조건식을 제외하면 1쪽 집합인 dept에 대한 참조가 전혀 없다.

따라서 아래와 같이 emp 테이블만 액세스한 것을 볼 수 있다.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   |    14 |   350 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   350 |     4   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("E"."DEPTNO" IS NOT NULL)

 

오라클의 경우 이러한 쿼리 변환이 10g부터 작동하기 시작하였다.

 

아래와 같은 파라미터 또는 힌트로 해당 기능을 제어할 수 있다.

alter session set "_optimizer_join_elimination_enabled" = true ;

 

-- 힌트를 이용한 제어

select /*+ eliminate_join(d) */ e.empno, e.ename, e.deptno, e.sal, e.hiredate

from   dept d, emp e

where  d.deptno = e.deptno;

 

select /*+ no_eliminate_join(d) */ e.empno, e.ename, e.deptno, e.sal, e.hiredate

from   dept d, emp e

where  d.deptno = e.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3074306753

 

------------------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |    14 |  392 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |         |    14 |  392 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP     |         14 |  350 |     4   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |          1 |     3 |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("D"."DEPTNO"="E"."DEPTNO")

 

 

조인 제거 기능이 작동하기 위한 전제 조건

PK FK 제약이 설정되어져 있어야 한다.

만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수 도 있어 쿼리 변환을 수행할 수가 없다.

 

FK가 설정돼 있더라도 emp deptno 컬럼이 Null 허용 컬럼이면 결과가 틀리게 될 수 있다.

조인 컬럼 값이 Null인 레코드는 조인에 실패해야 정상인데 조인문을 제거하면 그 레코드들이 결과집합에 포함되기 때문이다. 그런 오류를 방지하기 위해 옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해 준다.

 

Outer 조인시

10g까지는 조인 제거 기능이 수행되어지지 않으나 11g부터는 조인 제거 기능이 수행되어진다.

 

11g 테스트 결과)

SQL> select e.empno, e.ename, e.sal, e.hiredate

from emp e, dept d

where d.deptno(+) = e.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   |    14 |   308 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   308 |     4   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

 

11g 부터 PK FK 제약이 설정돼 있으면 in 또는 exists 서브쿼리도 일반 조인문처럼 제거 된다.

번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6057
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3695
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6158
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16003
30 6. 히스토그램 오예스 2011.04.25 17364
29 5. 카디널리티 오라클잭 2011.04.27 12913
28 7. 비용 file balto 2011.05.02 4990
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6257
25 3. 뷰 Merging 오라클잭 2011.05.17 6080
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2075
23 5. 조건절 이행 file balto 2011.05.30 5462
22 4. 조건절 Pushing 오예스 2011.05.31 17467
» 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8323
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3119
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9951
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4954
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4846