메뉴 건너뛰기

bysql.net

이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

2.3. 조인 종류별 특징및 활용 방안

2009.09.13 02:16

휘휘 조회 수:93137

 534-564
2.3. 조인 종류별 특징및 활용 방안

조인은 크게 두가지 분류로 나눌수 있다.
- 어떤 집합을 먼저 액세스 하여 상수값을 만들고 그것을 이용해 대응되는 집합을 찾아가는것 (Nested Loops)
- 서로 연결하기 좋도록 미리 소정의 선행작업을 수행한 다음 서로를 대응 시켜 가는 방법 (Sort Merge)

2.3.1. Nested Loops 조인
- 가장 전통적인 조인방법이다.
- 먼저 액세스한 결과를 다음의 액세스에 상수값으로 제공해준다.

2.3.1.1. Nested Loops 조인의 기본개념
가) Nested Loops 조인의 특징
- 순차적
( Driving TABLE의 처리범위에 있는 각각의 로우와 테이블간의 연결모두 순차적)
- 선행적
( 초기 액세스 테이블의 처리범위에 의해 처리량이 결정)
- 종속적
( 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스)
- 랜덤 액세스
( 선행테이블은 첫번째 경우만 랜덤, 연결테이블은 모두 랜덤 액세스로 수행)
- 선택적
( 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라짐)
- 방향성, 연결고리 상태
( 연결고리의 인덱스 유무에 따라 액세스 방향및 수행속도에 많은 차이 발생)
- 부분범위 처리 가능
( 부분범위 처리 조건을 만족하면 운반단위가 채워지는 순간에 우선 멈출수 있음
- 체크조건의 영향력
( 부분범위의 경우 조건이 넓으면 운반단위를 빨리채우므로 빨리 수행
   전체범위 처리일 경우 가공할 대상이 많아지므로 수행속도가 나빠짐)



나) Nested Loops 조인의 적용 기준
- 부분범위 처리
- 조인되는 한쪽이 상대방 테이블의 결과를 제공받아야만 처리범위를 줄일수 있는 상황일 경우
- 처리량이 적은 경우 (많다면 부분처리)
- 연결고리에 이상이 없는 경우
- 먼저 수행한 집합의 처리범위의 크기와 얼마나 많은 처리 범위를 미리 줄여 줄 수 있느냐가 수행속도에 영햘을 미침
- 운반단위의 크기가 수행속도에 상당한 영향을 미침
- 대용량 처리일시라도 메모리 사용이 부담될경우 유리 (Sort조인 이나 해쉬조인은 과도하게 메모리 사용을 요구)



2.3.1.2. Nested Loops 조인의 순서결정
- 어떤순서로 수행되느냐에 따라 조인의 효율이 달라짐

SELECT ......
FROM TAB x, TAB2 y, TAB3 z
WHERE x.A1 = y.B1
    AND z.C1 = y.B2
    AND x.A2 = y.B2
    AND x.A2 = '10'
    AND y.B2 LIKE 'AB%';


(1) TAB1 - TAB2 -TAB3

순서
ACCESS PATH
설명
TAB1 x
TAB2 y
TAB3 z
A2='10'
B1=A1 and B2 LIKE 'AB%'
C1=B2
x.A1=y.B1 은 y가 아직 액세스 되지않았음
z.C1=y.B2 의경우 z가 아직 액세스 되지 않았음
이미 x 와 y가 액스세 되어있으므로 선택





(2) TAB2 - TAB3 - TAB1

순서
ACCESS PATH
설명
TAB2 y
TAB3 z
TAB1 x
B2 LIKE 'AB%'
C1=B2
A1=B1 and A2='10'
y 중 상수값을 가진 값만 선택됨
y 가 이미 액세스 되었으므로 선택
이미 y와 z가 액세스 되어있으므로 x와 조인된 모든것 선택


(3) TAB3 - TAB2 - TAB1

순서
ACCESS PATH
설명
TAB3 z
TAB2 y
TAB1 x
FULL TABLE SCAN
B2=C1 and B2 LIKE 'AB%'
A1=B1 and A2='10'
z 의 유일한 조건인 z.c=y.b2 처리 불가능 z를 전체다 읽음
z 가 이미 액세스 되므로 z와 조인과 y의 상수대입 조건 수행
x 와 관련된 조건 수행

각각의 조건은 주어진 환경에 따라 성능이 달라진다.
자신의 처리범위를 줄여줄 수있는 다른 집합들이 액세스 된후에나
자신이 먼저 처리범위를 줄여줄 수 있다면 그들보다 먼저 수행도도록 하는것이 가능하면 좋다.


2.3.2. Sort Merge 조인
조인할 집합들을 정렬한후 서로 연결하여 수행

가) Sort Merge 조인의 특징
1) 동시적
( 상대 집합의 처리결과와 상관없이 자신이 보유한 처리조건만 가지고 액세스하여 정렬을 완료한후 머지 수행)
2) 독립적
( 자신에게 주어진 상사값에 의해서만 범위를 줄임)
3) 전체범위 처리
( 정렬이 완료된 후에라야 조인을 시작할수 있음)
4) 스캔방식
( 스캔방식으로 처리)
5) 선택적
( 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않음)
6) 무방향성
(조인의 방향과는 거의 무관)
7) 자신의 처리범위를 줄이기위해 인덱스는 한가지만 사용되고 나머지는 머지 작업대상을 줄여주기 때문에 중요함

나) Sort Merge 조인의 적용기준
1) 전체처리로만 가능한 형태일 경우 검토
2) 상대 테이블에서 값을 받지 않고도 처리범위를 줄일수 있다면 효과가 큼
3) 스캔방식의 처리로 많은 양의 랜덤 액세스를 줄일수 있다.
4) 연결고리 이상 상태에 영향을 받지 않음
5) 자신의 처리범위를 줄여줄수 있는 인덱스 구성이 중요
6) 운반단위의 크기가 수행속도에 영향을 미치지 않음
7) 온라인 상태에서는 NL이 유리
8) 옵티마이져가 'ALL_ROWS'일경우 'SORT MERGE 조인' 이나 '해쉬조인'의 실행계획이 자주 수립됨
9) 충분한 메모리 활용이 가능하고 병렬처리를 통해 빠르게 정렬작업이 가능한경우



2.3.3. Nested Loops 조인과 Sort Merge 조인의 비교

1) 조건처리

SELECT a.FLD1, ..., b.COL1 ...
FROM TAB1 a, TBA2 b
WHERE a.KEY1 = b.KEY2
    AND a.FLD1 = '111'
    AND a.FLD2 like 'AB%'
    AND b.COL1='10'  ---> 삭제

Nested Loops
- 삭제된 조건은 최종적으로 체크하는 역할을 수행하므로 일의양에는 큰차이가 없음
  부분처리의 경우 운반단위가 빨리 채워졌으므로 더 유리

Sort Merge
- TAB2의 범위를 줄여주는 역할이었지만 삭제되었으므로 TAB2를 FULL SCAN후 정렬
   머지할 양의 증가

2) 전체 범위 처리의경우


SELECT a.Fld1, ...,b.COL1, ...
FROM TAB1 a, TAB2 b
WHERE a.KEY1=b.KEY2
ORDER BY a.FLD5, b.COL5;

Nested Loops
- TAB1 (어느한쪽도 될수있음) 이 먼제 전체 테이블을 스캔
- KEY1에 따라 KEY2인덱스에서 찾아 TAB2의 해당 로우를 액세스
- 전체 테이블을 대상으로 랜덤 액세스 발생 (대량의 랜덤액세스)
- ORDER BY 가 없다면 운반단위처리가 가능해져 수행속도가 빨라 질수 있다

Sort Merge 조인
- 각각의 테이블마다 전체 테이블 스캔이 발생하여 연결고리가 되는 컬럼으로 정렬 후 머지
- 대량의 랜덤 액세스 발생이 없음 (NL 조인보다 유리)
- Sort area size의 크기가 적으면 정렬작업에 대한 부하로 인해 NL보다 늦어질수도 있음