2.3.5. 세미(Semi) 조인 (by rnsl) [2009.05.17]
2.3.5. 세미(Semi) 조인
세미 조인이란 말 그대로 조인과 매우 유사한 데이터 연결 방법을 뜻한다.
다시 말해서 서브쿼리를 사용했을 때 메인쿼리와의 연결을 처리를 의미한다.
2.3.5.1 세미 조인의 개념 및 특징
세민 조인이란 말은 원래 분산질의를 효율적으로 수행하기 위하여 도입된 개념이다.
두 테이블 간에 조인을 할 때 한 테이블을 다른 사이트에 전송하기 전에 먼저 조인에
필요한 속성만을 추출하는 프로젝션을 실시하여 전송한 후, 조인에 성공한 로우의
집합만을 다시 전송함으로써 네티워크를 통해 전송되는 데이터의 양을 줄이고자 하는
개념으로 도입된 것이다.
여기서 말하는 세미 조인은 주로 서브쿼리를 사용했을 때 메인쿼리와의 연결을 하기
위해 적용되는 광범위함 유사 조인을 의미한다. 서브쿼리의 서브(Sub)는 말 그대로
'하위','부'를 뜻한다. 서브쿼리는 메인(Main)이 되는 메인쿼리에 종속되는
하위의 쿼리이다. 이 종속의 의미는 반드시 주의 영역을 초과할 수 없음을 의미하고
있다. 또한 별도의 정의를 하지 않아도 주영역의 모든 속성을 그대로
상속받을 수 있다.
조인과 서브쿼리는 서로 매우 유사한 특징을 가지고 있지만 본질적인 차이는 집합 간의
종속성에 있다. 어떤 집합들을 연결하고자 할 때 조인은 동일한 등급(Grade) 상에 위치
하지만 서브쿼리는 주종관계를 가진다. 동일한 등급이냐 아니냐는 그들 간에 교환법칙이
성립하느냐에 있다고 할수 있다.
서로 같에 위치를 바꾸어도 동일한 결과를 얻을 수 있다면 동등 관계에 있지만 그렇게
했을 때 결과가 달라진다면 교환법칙이 성립하지 않는 것이고, 종속 관계에 있다는 것을 의미한다.
[VLDB: 그림 2-2-23 ]
\- 조인은 집합의 곱이다.
\- 세미조인의 결과는 항상 메왼쿼리 집합과 동일한 결과이다.
서브쿼리는 메인쿼리에 종속적이기 때문에 어떠한 형태의 관계를 가지더라도 결코
메인쿼리의 집합을 변화시킬 수 없다.
\- 세미 조인이 조인과 다른 또 하나의 큰 차이점은 조인 과정에서 등장한 컬럼들을
사용할 수 있는 상속석에 대한 부분이다. 쉽게 설명한다면, 조인은 조인된 집합에
모든 컬럼들을 아무 제약없이 사용할 수가 있지만 세미조인은 그렇지 못하다는 것이다.
서브쿼리는 메인쿼리의 컬럼들을 마음대로 사용할 수 있지만, 메인쿼리에는 서브쿼리의
집합에 있는 컬럼들을 사용할 수 없다. 일반적인 아버지(메인)와 아들(서브)간의
상속의 개념으로 이해 하면 쉽다.
2.3.5.2 세미조인의 실행계획
가) Nested Loops 형 세미조인
서브쿼리가 먼저 수행되어 SELECT-List 의 연결고리 값을 상수값으로 만들고, 이것을
메인쿼리 연결고리에 대응시키는 방법(서브쿼리가 제공자 역할)과
메인쿼리가 먼저 수행되어 상수값이 된 연결고리 값을 서브쿼리의 연결고리
(서브쿼리 SELECT-List 에 있는 컬럼)에 제공하는 방법이 모두 발생할 수 있다.
(확인자 역할 - 세미조인의 특성상 서브쿼리가 나중에 수행되면 그 역할은 단지
조건을 체크(확인)하는 역할만 하기 때문이다.)
[VLDB:그림 2-2-24]
세미조인이 절대적으로 준수해야만 하는 철칙인 '메인쿼리 집합의 보호'를 위해
'SORT(UNIQUE)을 하는 단계가 추가로 삽입되어 있음을 확인할 수 있다.
[VLDB:그림 2-2-25]
세미조인이 절대적으로 '메인쿼리 집합 보호'를 철직으로 준수해야 하므로 이를 위해서
등장한 것이 바로 'FILTER' 라는 방식의 처리다.
이 방식은 조인은 기존의 Nested Loops 조인과 동일한 방법으로 진행하되 대응하는
결과 집합 전체를 대상으로 하지 않고 대응하는 첫 번째까지만 연결하는 조인 방식이다.
이 방식은 먼저 수행되는 집합에서 처리하는 각각에 대해서 언제나 단 하나씩과 연결을
하게되므로 결코 메인쿼리의 집합에 훼손을 가져오지 않는다.
서브쿼리가 '확인자'의 역할을 할 때는 이와 같이 'FITER' 형식으로 조인이 발생하게 된다.
위의 그림에 표시한 (a) 부분을 자세히 살펴보자. 이처럼 이중으로 기술한 이유는
서브쿼리 내에서 메인쿼리의 컬럼이 존재한다는 것은 곧 종속성을 의미한다. 다시 말해서
이 서브쿼리는 논리적으로 절대 먼저 수행 될 수 없도록 종속되었다는 것이다.
UPDATE 청구 x
SET 입금액 = nvl(입금액,0) + :int_amt
WHERE 청구년월 = '200503'
AND 고객번호 IN ( SELECT 고객번호
FROM 고객 y
WHERE 납입자 = :in_cust
AND y.고객번호 = x.고객번호 ) ;
==> 서브쿼리 내에서 메인쿼리의 칼럼이 존재 하므로 종속적(확인자)로 실행됨
아래와 같이 수정하자 \!\!
UPDATE 청구 x
SET 입금액 = nvl(입금액,0) + :in_amt
WHERE 청구년월 = '200503'
AND 고객번호 IN ( SELECT 고객번호 ..... (b)
FROM 고객 y
WHERE 납입자 = :in_cust ) ;
나) Sort Merge형 세미조인
SELECT .....
FROM 사원
WHERE 부서코드 IN ( SELECT 부서코드
FROM 근태
WHERE 일자 BETWEEN '20050601' AND '20050612'
AND 근태유형 = '무단결근' )
AND 직책 > '과장' ;
사원:근태=M:M 관계이고, 서브쿼리의 특성상 항상 메인쿼리의 집합을 보존되므로
물론 그 결과는 M:1조인과 동일하다.
SELECT STATEMENT
MERGE JOIN
SORT(JOIN)
TABLE ACCESS (FULL) OF '사원'
SORT(JOIN)
VIEW
SORT(UNIQUE)
TABLE ACCESS ( BY ROWID) OF '근태'
INDEX (RANGE SCAN) OF '유형_일자_IDX' ( NON-UNIQUE)
다) 필터(Filter)형 세미조인
필터란 말 그대로 '골라내는 작업' 방법을 말한다.
확인자 역할이아는 것은 먼저 수행하여 액세스한 결과를 서브쿼리를 통해서 체크하여
취할 것인지, 아니면 버려야 할 것인지를 결정하는 역할이다. 이 형식의 세미조인은
이러한 작업을 보다 효율적으로 수행하기 위해 버퍼(Buffer)내에 이전의 값을 저장해
두었다가 대응되는 집합을 액세스하기 전에 먼저 저장된 값과 비교함으로써 액세스를
최소화하는 방법이다.
주로 EXIST, 간혹 IN 을 사용한 일반적인 서브쿼리에서도 확인자 역할을 하는 경우가 있다.
SELECT ..............
FROM ORDER x
WHERE ORDER LIKE '200506%'
AND EXIST ( SELECT 'X'
FROM DEPT y
WHERE y.DEPTNO = x.SALDEPT ..... (a)
AND y.TYPE1 = '1' );
메인쿼리의 조건문에도 연결고리가 보이지 않는다.
서브쿼리의 SELECT-List에도 연결고리가 없다. 'EXIST'의 연결고리는 (a)에서처럼
서브쿼리의 조건절에 마치 일반 조인처럼 표시되어 있다. 우리는 앞서 서브쿼리 내에
메인쿼리의 컬럼이 있으면 이미 논리적으로 서브쿼리가 먼저 수행될 수 없다고 했다.
따라서 이처럼 'EXISTS'를 사용한 세미조인은 언제나 확인자 역할만 가능하다.
판정을 하기 위해서 서브쿼리는 오직 한건만 나오면 된다.
앞의 SQL 의 실행 계획은 다음과 같이 나타난다.
FILTER
TABLE ACCESS ( BY ROWID ) OF 'ORDER'
INDEX ( RANGE SCAN ) OF 'ORDDATE_INDEX' (NON-UNIQUE)
TABLE ACCESS ( BY ROWID) OF 'DEPT'
INDEX ( UNIQUE SCAN ) OF 'DEPT_PK' ( UNIQUE )
ROWS EXECUTION PLAN
\--\-- --\
-\-
3200 FILTER
3200 TABLE ACCESS ( BY ROWID ) OF 'ORDER'
3201 INDEX ( RANGE SCAN ) OF 'ORDDATE_INDEX' ( NON_UNIQUE )
10 TABLE ACCESS ( BY ROWID ) OF 'DEPT'
10 INDEX ( UNIQUE SCAN ) OF 'DEPT_PK' (UNIQUE) \
---\- (b)
'ORDDATE_INDEX'를 3,201 개까지 차례로 범위처리를 하면서 3,200 건의 'ORDER' 테이블
의 로우를 읽어 내려 가면서 대응되는 DEPT 테이블을 연결한다. 그렇다면 'ORDER' 테이블
에서 액세스한 3,200 개의 로우에 대해서 매번 서브쿼리가 수행되어 EXISTS 를 체크 했으
므로 'DEPT' 테이블을 액세스하는 서브쿼리도 3,200 번 수행되어야 할 것이다.
그런데 위의 (b)을 살펴보면 이 서브쿼리가 'DEPT' 테이블을 액세스한 것은 단 10회에
불과하다. 자\! 이 현상을 어떻게 설명해야 할 것인가 ? 
[VLDB: 그림 2-22-26 ]
1) 먼저 ORDDATE_INDEX 에서 '200506%'를 만족하는 첫 번째 로우를 일고 그 ROWID로
ORDER 테이블의 해당 로우를 액세스한다.
2) 그 로우가 가지고 있는 SALDEPT 와 버퍼에 있는 DEPT 와 비교한 결과가 같지 않으므로
DEPT 테이블의 기본키를 이용해 액세스한 후 TYPE='1'을 체크 한다. 체크를 하여 조건을
만족하면 운반단위에 태우고 아니면 버린다.
3) 액세스한 DEPT 테이블의 비교 컬럼값들을 버퍼에 저장한다.
4) ORDERDATE_INDEX 의 두 분째 로우에 대한 ORDER 테이블 로우를 액세스한 후 버퍼와
체크한다. 이 때 ORDER 테이블의 SALDEPT 와 버퍼의 DEPT 가 동일하면 버퍼와의 비교만
수행하며, DEPT 테이블은 액세스하지 않는다. 버퍼의 DEPT 와 일치하지 않을 때는
DEPT 테이블을 액세스하여 체크조건을 비교하고 그 값을 다시 버퍼에 저장한다.
버퍼는 하나의 값만 저장할 수 있으므로 앞서 저장된 값은 갱신된다.
그림에서 처음에 버퍼에 있던 '1,11'이 '2,22'로 바뀐어진 것을 표현하였다.
5)이와 같은 방법으로 ORDDATE_INDEX의 처리범위가 완료될 때까지 수행된다.
만약 메인쿼리에서 액세스되는 로우가 서브쿼리에서 비교할 컬럼(연결고리)으로
정렬되어 있다면 서브쿼리의 테이블 액세스 양을 많이 감소시킬 수 있다.
즉. 선행처리되는 ORDER 테이블의 연결고리인 SALDEPT 가 만약 ORDERDATE + SALDEPT 로
인덱스 되어 있었다면, 그림에서처럼 '11' 이 끝난 다음 '22'가 나타날 것이므로 DEPT
를 액세스하러 가는 경우가 최소화 할 것이다.
필터형 세미조인과 Nested Loops 조인의 차이점을 다시 한번 정리 해보자.
램덤 액세스를 통해 다른 집합과 연결을 한다는 점에서 매우 유사하나,
필터처리는 연결을 시도하다가 성공하는 로우를 만나면 연결작업을 멈추지만,
Nested Loops 조인은 조건을 만족하는 모든 로우에 대해 처리한다.
또한, 버퍼를 이용한 처리를 하여 랜덤 액세스 양을 최소화 시킨다.
라) 해쉬(Hash) 형 세미조인
SELECT ......................
FROM ORDER x
WHERE ORDDATE LIKE '200506%'
AND EXISTS ( SELECT /+ hash_sj(x,y) \*/\* 'x'
FROM DEPT y
WHERE y.DEPTNO = x.SALDEPT
AND y.TYPE1 = '1' ) ;
이 SQL 의 실행계획은 다음과 같이 나타난다.
HASH JOIN SEMI
TABLE ACCESS ( BY ROWID) OF 'ORDER'
INDEX ( RANGE SCAN ) OF 'ORDDATE_INDEX' ( NON-UNIQUE )
TABLE ACCESS ( FULL ) 'DEPT'
서브쿼리에 'HASH_SF' 힌트를 사용하여 세미 조인이 해쉬형으로 수행되도록 유도한
것이다. 여기에는 몇 가지 준수해야 할 제한 요건이 있다. 가령 서브쿼리에는 하나의
테이블만 존재해야만 한다거나, 서브쿼리 내에 또 다시 서브쿼리를 사용했을 때는
적용이 불가능하다. 또한 연산자는 반드시 '='이 되어야 하며, 서브쿼리 내에 GROUP
BY, CONNECT BY, ROWNUM 을 사용할 수 없다는 제약이 있다.
마) 부정형(Anti) 세미조인
조인의 연결고리 조건에 부정형(NOT)이 들어 있다면 양쪽 집합을 연결하는 것 자체가
이미 논리적으로 결코 쉽지 않다. 조인이란 연결고리 조건에 대응되는 집합을 찾는
것이므로 부정형이 들어 가는 순간, 대응되지 않는 것을 찾아야 한다는 논리가 되기
때문이다.
그러나, 약간만 생각을 바꾸어 보면 길이 없는 것도 아니다.
집합간의 연결은 기존의 세미조인으로 실시하고, 그 결과의 판정만 반대로 하는 방식을
적용하기 때문에 앞서 설명했던 세미조인과 매우 유사하다고 할 수 있다.
필터형 실행계획의 장*단점을 설명하면서 밝혔듯이 제공자 역할을 하지 않기 때문에
나중에 수행될 수 밖에 없어 대부분의 경우 반복적인 랜덤을 발생시키게 되므로 대량의
처리에 부담이 되는 경우가 많다. 특히 'IN'을 사용한 서브쿼리는 상황에 따라서 제공자가
될 수도 있고, 확인자가 될 수도 있지만, 'NOT IN'을 사용하는 순간 항상 확인자 역할을
할 수 밖에 없다는 것에 유의하기 바란다. (Boolen 함수 역할)
SELECT .............
FROM TAB1
WHERE COL1 LIEK 'ABC%'
AND COL2 NOT IN ( SELECT FLD2
FROM TAB2
WHERE FLD3 between '20050101' and '20050131' );
이 SQL 실행계획을 살펴보자
SELECT STATEMENT
FILTER
TABLE ACCESS ( BY ROWID ) OF 'TAB1'
INDEX ( RANGE SCAN ) OF 'COL_INDEX' ( NON UNIQUE )
TABLE ACCESS ( BY ROWID ) OF 'TAB2'
INDEX ( RANGE SCAN ) OF 'FLD3_INDEX' ( NON UNIQUE ) \
---\- (a)
부정형 조인은 연결고리에 해당하는 TAB1 의 COL2 와 TAB2의 FLD2 가 NOT IN 으로 비교
되었으므로 이 서브쿼리는 논리적으로 결코 제공자의 역할을 할 수 없다. 그러므로
부정형 실행계획에서 필터형으로 수행될 때는 서브쿼리가 항상 나중에 수행된다.(확인자역할)
위의 실행계획에 있는 (a)를 살펴보면 아주 이상한 점이 있다. 그것은 바로 나중에
수행되는 서브쿼리의 처리주관 인덱스는 연결고리인 FLD2 인덱스가 아니라 FLD3 인덱스가
사용되고 있다는 것이다.
이것은 데이터 베이스 버전에 따라 발생할 수 있는 옵티마져의 잘못으로
아래와 같이 수정을 통해서 문제를 미연에 방지하자
SELECT .............
FROM TAB1
WHERE COL1 LIEK 'ABC%'
AND NOT EXISTS ( SELECT FLD2
FROM TAB2
WHERE FLD2 = COL2
AND FLD3 between '20050101' and '20050131' );
필터형식으로 처리되는 부정형 조인의 최대 장점은 Nested Loops 조인과 같이 선행
집합에서 요구한 로우들에 대해서만 수행한다는 것이다. 다시 말해서 선행집합에서
상수값을 제공받아서 처리한다는 것이다.
문서에 대하여
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
- 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^