2.2. 연결고리 상태가 조인에 미치는 영향 (by rnsl) [2009.04.30]
2.2. 연결고리 상태가 조인에 미치는 영향
조인 : 집합을 연결고리(조인 조건)를 통해 서로 연결 하는것
연결 고리란, 주어진 조인 조건의 연산자 형태 + 인덱스 상태
연결고리 상태에 비교적 자유로운 Sort Merge, Hash Join
2.2.1 연결고리 정상
조인 조건의 연결고리가 '정상'이라는 의미는 조건절에 기술되는 조인의
연결조건에 인덱스가 모두 존재하고 있는 상태를 말한다.
인덱스가 정상 상태라는 말의 정확한 의미는 연결을 시도할 때
SQL의 조건절에서 이미 주어진 상수값이나, 앞서 엑세그한 집합의 결과에서
제공받은 상수값이 될 것들을 이용해 대응되는 집합을 액세스할 때 인덱스에
문제가 없는 상태를 말한다.
[VLDB: 그림 2-2-6]
1) TAB1 의 처리범위에서 첫 번째 로우를 읽는다 ( 그 순간 KEY1 의 값을 'D' 가 됨)
2) TAB2 의 연결고리가 되는 인덱스(KEY2) 가 있으므로 TAB1 의 연결괴리의 값('D')에
대응되는 오르를 KEY2 인덱스에서 찾는다.
3) KEY2 인덱스에 있는 ROWID 를 이용하여 TAB2 의 해당 로우를 찾는다.
4) 다시 TAB1 의 두 번째 로우를 읽어 위의 작업을 반복하게 되며 TAB1 의 처리범위가
모두 끝날 때까지 계속한다.
[VLDB:그림 2-2-7]
1) TAB2 의 처리범위에서 첫 번째 로우를 읽는다. ( 그 순간 KEY2 의 값을 'B' 가 됨)
2) TAB1 의 연결고리가 되는 인덱스(KEY1_INDEX)가 있으므로 TAB2 의 연결고리의 값('B')에
대응되는 로우를 KEY1 인덱스에서 찾는다.
3) KEY2 인덱스에 있는 ROWID 를 이용하여 TAB1 을 읽는다.
4) 다시 TAB2 의 두 번째 로우를 읽어 위의 작업을 반복하게 되며 TAB2 의 처리범위가
모두 끝날 때까지 계속한다.
연결고리 양쪽 모두에 인덱스가 존재하는 경우는 어느 방향으로 연결작업이
수행되든지 인덱스를 통해 정상적인 연결작업을 수행할 수가 있다. 이러한 상태를
앞으로 우리는 '연결고리 정상' 상태라고 부르기로 한다.
연결고리가 정상인 경우에는 연결고리가 더 이상 수행속도를 좌우하는 요소가 아니다.
수행속도의 차이가 나는 것에는 더 큰 이유가 있다. 그것은 바로 연결작업의 시도횟수가
달라질 수 있다는 것이다.
결론적으로 연결고리에 이상이 없다면 연결을 시도하기 전에 처리범위를 보다
많이 줄여 줄 수 있는 경로가 유리하다는 것이다.
결론적으로 연결고리에 이상이 없다면 연결을 시도하기 전에 처리범위를 보다 많이
줄여 줄 수 있는 경로가 유리하다는 것이다. 이 결론을 확인 하는 의미에서 다음 SQL 을 보자
SELECT a.FLD1, ......, b.COL1, ....
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.COL2 LIK 'AB%'
AND a.FLD1 = '10';
가정) 1. TAB1 의 FLD1 = '10' 을 만족하는 범위는 5,000 건
2. TAB2 의 COL2 LIKE 'AB%' 를 만족하는 범위는 100건
3. 연결괴로 연결했을 때는 모두 연결에 성공
[VLDB: 그림2-2-8]
우리에게 리턴되는 결과는 100건 이내지만 내부적으로는 5,000 건에 대한 처리하는 것에
주목하기 바란다.
이번에는 똑 같은 가정하에서 TAB2가 먼저 액세스 되는 경우를 그림을 통해 살펴보기로 하자 
[VLDB: 그림 2-2-9 ]
조인의 양을 먼저 ?이 줄여 줄 수 있는 테이블을 먼저 액세스하면 처리할 양이 줄어
든다는 것을 확인할 수 있다.
위의 예에서 실행계획은 옵티마이져 모드에 따라 다를 수 있다.
RBO 시엔 TAB1 을 먼저 처리하는 실행계획을 수립 하므로,
힌트나 사용제한 기능을 이용하여 TAB2가 먼저 처리되도록 할 수 있다.
1) SELECT a.FLD1, .... b.COL1, ....
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.COL2 LIKE 'ABC%'
AND RTRIM(a.FLD1) = '10' ;
2) SELECT /\ ORDERED \*/* a.FLD1, .... , B.COL1, ...
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.COL2 LIKE 'ABC%'
AND a.FLD1 = '10';
2.2.2 한쪽 연결고리 이상
어느 한쪽의 연결고리에 인덱스가 없는, 즉 '연결로리 이상' 인 상태가 연결작업에 미치는 영형을 알아보자 
[VLDB: 그림 2-2-10 ]
어차피 KEY1은 상수값을 제공하는 연할만 하고 인덱스를 사용하지 않기
때문에 KEY1이 인덱스로 구성되어 있지 않더라도 하등의 영향을 받지 않는다.
[VLDB: 그림 2-2-11]
1) TAB2 에서 처리해야 할 범위의 첫 번째 로우를 읽는다.
( 읽혀진 TAB2의 모든 컬럼들은 모두 상수가 됨)
2) KEY2의 값이 상수가 되었으나 연결해야할 TAB1의 KEY1에 인덱스가 없으므로
어쩔 수 없이 TAB1을 전부 스캔하면서 'KEY1=KEY2'를 만족하는 로우를 찾을 수
밖에 없다.
3) 다시 TAB2의 두 번째 로우를 읽어 또 다시 TAB1의 전체 테이블 스캔한다.
이 작업은 TAB2의 처리범위가 끝날 때까지 계속해서 매번 TAB1 의 전체
테이블을 스캔해야 한다.
이와 같은 문제를 피하기 위해서 연결고리의 어느 한쪽에 이상이 발생한 테이블을 반드시
먼저 처리하도록 하거나 아예 양쪽 모두 연결고리의 인덱스를 사용하지 않는
소트머지나 해쉬 조인으로 수행하도록 하지 않으면 안된다.
이러한 이유 때문에 과거 규칙기준 옵티마이져에서는 연결고리의 어느 한쪽에 인덱스가
없다면 무조건 없는 쪽을 먼저 처리하도록 실행계획을 수립하였다.
실전에서는 양쪽에 정상적인 연결고리가 정의되어 있는데도 불구하고 연결고리의
이상 상태가 발생되는 경우가 나타나고 있다. 왜 이러한 사태가 발생하게 되는지
몇 가지 사례를 통해 살펴볼 필요가 있을 것 같다.
조인되는 컬럼이 1:1로 대응되지 않는 경우
1) SELECT ....., columns, .....
FROM TABLE1, TABLE2
WHERE A||B||C = D ;
TABLE1의 컬럼들의 사용제한이 되어 TABLE1 이 먼저 액세스 되는 실행계획이 수립됨
2) SELECT ...., columns, ......
FROM TABLE1, TABLE2
WHERE A = substr(D,1,2)
AND B = substr(D,3,1)
AND C = substr(D,4,3) ;
TABLE2의 컬럼의 사용제한이 되어 TABLE2 가 먼저 액세스 되는 실행계획이 수립됨
데이타 타입의 차이에 의해 발생되는 경우
판매일자 칼럼의 Varchar 와 Date 로 다른 데이타 타입으로 정의시 발생
2.2.3 양쪽 연결고리 이상
양쪽 연결고리 모두에 인덱스가 없다면 어느 방향으로 처리하든 연결고리 이상 상태가
되므로 이 경우 옵티마이져는 연결고리의 상태에 영향을 받지 않는 방식인 소트머지나
해쉬 방식으로 실행계획을 수립하게 된다.
이 방식들은 인덱스를 사용하지 않는다는 특징 이외에도 랜덤(I/O)이 크게 감소하므로
대량의 범위를 조인하고자 할때 장점이 된다.
큰 단점은 먼저 수행된 집합이 처리범위를 현격히 줄여 줄 수 있다면 그 영향을 받아야
함에도 불구하고 자신의 범위만 처리하기를 고집하는 불합리한 면이 있다.
SELECT ......
FROM TAB1 x, TAB2 y
WHERE y.KEY1 = x.KEY1
AND y.KEY2 BETWEEN '200501' AND '200503'; \
---\- (a)
가정 - TAB1:TAB2=1:M
TAB2의 인덱스가 KEY2+KEY1
==> KEY1 이 연결고리로 쓸데없이 많은 INDEX 를 SCAN 하기 때문에 [VLDB: 결합 인덱스의 선두 컬럼이 '=" 조건을 받지 않았을때 발생하는 현상 ]
선(BETWEEN) 을 점으로 만들고자 'IN' 을 이용한 연쇄실행계획(Concatenation Plan)이 수립되도록 유도한다.
SELECT ......
FROM TAB1 x, TAB2 y
WHERE y.KEY1 = x.KEY1
AND y.KEY2 IN ('200501','200502','200503') \
---\- (b)
만약 (a) 의 조건의 범위가 입력받은 값에 의해 다양하게 변하거나 일자로 되어
있다면 (b) 처럼 일일이 지정해서 사용하는 것은 문제가 있다. 아래와
같이 풀자 [ 점으로 입력받게 : - ) ]
SELECT ...
FROM TAB1 x, TAB2 y
WHERE y.KEY1 = x.KEY1
AND y.KEY2 IN ( SELECT YMD FROM DATE_DUAL
WHERE YMD between :start_date AND :emd_date ) \
-- ©
문서에 대하여
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
- 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^