CH02. 조인 원리와 활용 - 01. Nested Loops 조인

(1) 기본 메커니즘

Nested Loop란 중첩 루프문입니다. 아래와 같은 구조를 가지고 있습니다.

<PL/SQL>

for outer in 1..100 loop

for inner in 1..100 loop

 dbms_output.put_line(outer||':'||inner);

end loop;

end loop;

 

위 중첩 루프문과 같은 수행 구조를 사용하는 NL 조인이 실제 어떤 순서로 데이터를 액세스하는지 아래 PL/SQL문이 잘 설명해 줍니다.

 

begin

 for outer in (select deptno, empno, rpad(ename,10) ename from emp)

loop --outer 루프

for inner in (selkect dbame from dept where deptno = outer.deptno)

loop -- inner 루프

 dbms_output.put_line(outer.empno||':'||outer.ename||':'||inner.dname);

end loop;

end loo;

end;

 

위 PL/SQL문은 아래 쿼리와 100% 같은 순서로 데이터를 액세스하고 데이터 출력순서도 같습니다. 내부적으로 쿼리를 반복 수행하지 않느다는 점만 다릅니다.

 

select /*+ ordered use_nl(d) */ e.empno, e.ename, d.dname

from emp e, dept d

where d.deptno = e.deptno

 

소트머지 조인과 해시조인도 각각 Sort Area와 Hash Area에 가공해둔 데이터를 이용한다는 점만 다를 뿐 기본적인 조인 프로세싱은 다르지 않습니다.

 

(2) 힌트를 이용해 NL 조인을 제어하는 방법

select /*+ ordered use_nl(d) */ *

from  dept d, emp e

where e.deptno = d.deptno

 

ordered 힌트는 from절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용하고, use_nl 힌트는 NL방식으로 조인하라고 지시할 때 사용합니다. 위에서는 두개다 같이 사용했으므로 dept 테이블(outer테이블,Driving테이블)을 기준으로 emp테이블(inner테이블)과 조인할 때 NL 방식으로 조인하라는 뜻이 됩니다.

 

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

Outer 테이블, Inner 테이블

 

실행계획을 조인을 사용해서 보면 NL조인과 해시 머지 조인에서는 위쪽에 있는 테이블을 스캔하면서 아래쪽 테이블을 탐색하는 메커니즘이므로 위쪽에 있는 테이블을 Outer, 아래쪽에 있는 테이블을 Inner 테이블이라고 칭해도 무방합니다.

해시 조인의 메커니즘은 아래쪽에 있는 테이블을 스캔하면서 위쪽 테이블을 (해시테이블을 통해) 탐색하는 방식이므로 헷갈릴 수 있습니다 .하지만 트레이스파일에 사용된 용어가 표준 용어는 아니므로 혼란스러워 하지 말고 사용하면 됩니다. 가장 오래된 NL조인에 맞춰져서 그렇게 나온 것입니다.

 

아래와 같이 정의하고 진행하겠습니다.

 

	NL조인	소트 머지 조인	해시 조인
실행계획상 위쪽	Outer(Driving) 테이블	Outer(first) 테이블	Build Input
실행계획상 아래쪽	Inner(Driven) 테이블	Inner(Second) 테이블	Probe Input

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

 

 

세 개 이상을 조인할 때는 힌트를 아래처럼 사용해야 합니다.

 

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *

from A, B, C, D

where ....

 

해석해보면 A,B,C,D순으로 조인하되, B와 조인할 때 그리고 이어서 C와 조인할 때는 NL 방식으로 조인하고 D와 조인할 때는 해시 방식을 조인하라는 뜻입니다.

ordered 대신 leading 힌트를 사용해 조인 순서를 제어할 수도 있습니다. 9i까지는 leading힌트에 인자를 하나만 입력할 수 있었습니다. 조인할 때 가장 처음에 읽을 기준 집합(Outer Table)하나만 명시하는 것입니다. 그러다보니 leading 힌트만으로는 조인 순서를 세밀하게 제어할 수 없어 9i이전 버전에서는 ordered 힌트를 주로 사용했습니다. from절의 테이블 순서를 일일이 바꿔주면서 사용했습니다.

10g부터는 leading 힌트에 2개 이상 테이블을 기술할 수 있도록 기능이 개선돼 from절을 바꾸지 않고도 마음껏 순서를 제어할 수 있게 되었습니다. 이 때문에 ordered 힌트의 인기가 예전만 못하다고 합니다.

 

select /*+ leading(C,A,D,B) use_nl(A) use_nl(D) use_hash(B) */ *

from A,B,C,D

where ....

 

아래는 ordered 나 leading힌트를 기술하지 않았으므로 4개 테이블을 NL방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡기는 것입니다.

 

select /*+ use_nl(A,B,C,D) */ *

from A,B,C,D

where .....

 

 

(3) NL 조인 수행 과정 분석

아래 조인문에서 조건절 비교 순서는 어떻게 될까요? 

아래는 인덱스상황입니다.

 

아래는 정답입니다.

사용되는 Index는 dept.loc컬럼으로 만든 DEPT_LOC_INDEX와 emp.deptno컬럼으로 만든 emp_deptno_idx입니다.

조건비교 순서는 2 -> 3 -> 1 -> 4순입니다.

실행계획을 해설할 때 형제노드 간에는 위에서 아래로 읽습니다. 부모-자식 노드간에는 안쪽에서 바깥쪽으로, 즉 자식노드부터 읽습니다.

위 실행계획의 실행 순서를 나열하면 아래와 같습니다.

1.dept_loc_idx인덱스 범위 스캔(ID=4)

2.인덱스 rowid로 dept테이블 액세스(ID=3)

3.emp_drptno_idx 인덱스 범위 스캔(ID=6)

4.인덱스 rowid로 테이블 액세스(ID=5)

5.sal 기준 내림차순(desc)정렬(ID=1)

 

아래 그림은  실행계획을 그림으로 표현한 것입니다.

 

1.dept.loc='SEOUL'조건을 만족하는 레코드를 찾으려고 dept_loc_idx인덱스를 범위 스캔합니다.

2.dept_loc_idx인덱스에서 읽은 rowid를 가지고 dept테이블을 액세스해 dept.gb=2필터조건을 만족하는 레코드를 찾습니다.

3.dept 테이블에서 읽은 deptno 값을 가지고 조인 조건을 만족하는 emp 쪽 레코드를 찾으려고 emp_deptno_idx인덱스를 범위스캔합니다.

4.emp_deptno_idx인덱스에서 읽은 rowid를 가지고 emp테이블을 액세스해 sal >= 1500 필터 조건을 만족하는 레코드를 찾습니다.

5. 1~4과정을 통과한 레코드들을 sal 컬럼 기준 내림차순(desc)으로 정렬한 후 결과를 리턴합니다.

 

각 단계를 완료하고 나서 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다는 사실입니다. 단, order by는 전체 집합을 대상으로 정렬해야하므로 작업을 모두 완료한 후에 다음 오퍼레이션을 진행합니다.

 

 

위의 그림은 NL조인의 수행 절차를 좀더 상세하게 표현한 그림입니다.

11,19,31,32는 스캔할 데이터가 더 있는지 확인하는 one-plus 스캔을 표시한 것이며 (O}는 테이블 필터 조건에 의해 레코드가 걸러지지 않은 것을 의미하고, 반대로 (X}는 테이블 필터 조건에 의해 걸러진 것을 의미합니다.

 

그림 2-2를 보면 dept_loc_idx인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨을 이해해야합니다. 여기서는 단일 컬럼 인덱스를 '='조건으로 스캔했으므로 비효율 없이 6(5+1)건을 읽었고, 그만큼 테이블 Random 액세스가 발생했습니다. 우선 이부분이 첫 번째 부하지점입니다.

 

만약 dept테이블로 많은 양의 Random액세스가 있었는데, gb='2'조건에 의해 필터링되는 비율이 높다면 어떻게 해야 할까요? 이전에 나왔듯 dept_loc_idx에 gb컬럼을 추가하는 방안을 고려해야 합니다.

 

두 번째 부하지점은 emp_deptno_idx인덱스를 탐색하는 부분이며, Outer 테이블인 dept를 일고 나서 조인 액세스가 얼만큼 발생하느냐에 의해 결정됩니다. 이것 역시 Random 액세스에 해당하며, 그림 2-2에서는 gb='2'조건을 만족하는 건수만큼 3번의 조인시도가 있었습니다. 만약 emp_deptno_idx의 높이가 3이면 매 건마다 그만큼의 블록 I/O가 발생하고, 리프 블록을 스캔하면서 추가적인 블록 I/O가 더해집니다.

 

세 번째 부하지점은 emp_deptno_idx를 읽고 나서 emp테이블을 액세스하는 부분입니다. 여기서도 sal >= 1500조건에 의해 필터링되는 비율이 높다면 emp_deptno_idx인덱스에 sal 컬럼을 추가하는 방안을 고려해야 합니다.

 

 

(4) NL 조인의 특징

오라클은 블록 단위로 I/O를 수행하며 하나의 레코드를 읽으려고 블록을 통째로 읽는 Random 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재합니다. 그런데 NL 조인의 첫 번째 특징이 Random 액세스 위주의 조인 방식이라는 점입니다. 따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적입니다.

 

두 번째 특징은, 조인을 한 레코드씩 순차적으로 진행한다는 점입니다. 첫 번째 특징 때문에 대용량 데이터 처리 시 매우 치명적인 한계를 드러내지만, 반대로 이 두 번째 특징 때문에 아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있습니다. 부붐범위처리가 가능한 상황에서 그렇습니다. 그리고 순차적으로 진행하는 특징때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정됩니다.

 

다른 조인 방식과 비교했을 때 인덱스 구성 전략이 특히 중요하다는 것도 NL조인의 중요한 특징입니다. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라집니다.

 

(5)NL조인 튜닝 실습

 

jobs,employees 두 테이블이 있고, 인덱스 상황은 다음과 같습니다.

 

튜닝하고자 하는 쿼리는 아래와 같습니다.

 

조건절 비교 순서와 사용되는 인덱스를 분석해보겠습니다.

조건절 비교순서는 2,3,1,4이고 사용 인덱스는  jobs_max_Sal_ix emp_hiredate_ix입니다.

참고로 index 힌트에 어떤 인덱스를 사용하라고 명시하지 않았으므로 emp_hiredate_ix인덱스를 이용해 2,3,4,1순으로 처리될 수도 있습니다. 하지만 일반적으로 2,3,1,4순으로 처리되므로 이것을 기준으로 설명하겠습니다.

 

아래는 위 쿼리에 대한 SQL 트레이스 결과인데, 블록 I/O가 9개(query)밖에 안 되므로 튜닝할 필요가 없어보입니다.

만약 트레이스 결과가 아래와 같으면 어떤 문제가 있어보입니까?

 

job_max_sal_ix를 스캔하고 jobs테이블을 액세스한 횟수가 278회인데 테이블에서 job_type='A'조건을 필터링한 결과는 3건에 그칩니다. 불필요한 테이블 액세스를 많이 한 셈이고, 이처럼 테이블을 액세스한 후에 필터링되는 비율이 높다면 인덱스에 테이블 필터 조건 컬럼을 추가하는 것을 고려해 볼 필요가 있습니다.

 

job_max_sal_ix인덱스에 job_type컬럼을 추가하고서 아래의 트레이스처럼 나왔습니다.

 

Rows에 표시된 숫자만 보면 비효율적인 액세스가 없어 보이지만 테이블을 액세스하기 전 인덱스 스캔 단계에서의 일량을 확인하지 못했으므로 튜닝이 완료되었다고 볼 수는 없습니다.

인덱스가 max_salary + job_type순이고, 조건절을 보면 인덱스 선두 컬럼이 부등호 조건입니다. max_salary >= 1500조건에 해당하는 레코드가 엄청 많다면 많은 양의 인덱스 블록을 스캔하면서 job_type='A'조건을 필터링했을 것입니다.

 

오라클 7버전에서는 Rows부분에 각 단계의 처리한 건수를 보여주었으므로 실제 스캔량을 쉽게 확인할 수 있었습니다.

그러다가 바뀌기 시작해 9i에서 완전히 출력건수를 보여주는 방식으로 바뀌다 보니 각 단계의 처리 일량을 따로 분석해야 하는 불편함이 생겼습니다.

그래서 9iR2부터는 아래와 같이 각 처리 단계별 논리적인 블록 요청 횟수(cr)와 디스크에서 읽은 블록 수 (pr) 그리고 디스크에 쓴 블록 수 (pw)등을 표시하기 시작했습니다.

 

여기서 보면 job_max_sal_ix인덱스로부터 3건을 리턴하기 위해 인덱스 블록 1,000개 읽은 것을 알 수 있습니다.

튜닝방법은 인덱스 컬럼순서를 조정해 job_type + max_salary 순으로 구성해주면 됩니다.(영향도분석은 선행되어야 합니다)

 

 

이번에는 트레이스 결과가 아래와 같습니다. 어떤 문제가 있어보입니까?

jobs 테이블을 읽는 부분에서는 비효율이 없어보입니다. 인덱스에서 스캔한 블록이 4개이고 테이블을 액세스하고서 필터링되는 레토드도 전혀 없습니다. 일량은 많지만 비효율은 없다고 볼 수 있습니다. 문제는 jobs테이블을 읽고 나서 employees 테이블과의 조인 시도 횟수입니다. 1278번 조인 시도(JOBS테이블의 rows수)를 했지만 최종적으로 5건(맨위 Nested Loops의 Rows 수)밖에 성공하지 않았습니다.

 

이럴 때는 조인순서를 바꾸는 것을 고려해 볼 수 있습니다. 만약 hire_date 조건절에 부합하는 레코드가 별로 없다면 튜닝에 성공할 가능성이 높습니다.

 

조인 순서를 바꾸어도 별 소득이 없다면 소트 머지 조인과 해시조인을 검토해 보아야 합니다.

 

(6) 테이블 Prefetch

 

오라클9i부터 NL 조인 실행계획에 변화가 생겼습니다. 아래와 같이 인덱스 rowid에 의한 Inner 테이블 액세스가 Nested Loops 위쪽에 표시되곤 하는데, 이는 해당 테이블 액세스 단계에서 Prefetch 기능이 적용되었음을 표현하기 위함입니다.

 

테이블 Prefetch를 제어하는 파라미터 중 하나인 _table_lookup_prefetch_size를 0으로 설정하면 똑같은 SQL인데 아래와 같이 전통적인 방식의 NL 조인 실행계획으로 되돌아갑니다.

 

새로운 포맷의 실행계획이 나타난다고 항상 테이블 Prefetch가 작동하는 것은 아니고 단지 그 기능이 활성화되었음을 의미할 뿐입니다. Prefetch 방식으로 디스크 블록을 읽었는데 실제 버퍼 블록 액세스로 연결되지 못한 채 메모리에서 밀려나는 비율이 높다면 실행계획은 그대로인 채 내부적으로 기능이 비활성화되기 때문입니다.

참고로, Prefetch기능이 실제 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타납니다.

 

Prefetch는 디스크 I/O와 관련이 있습니다. 디스크 I/O를 수행하려면 비용이 많이 들기 때문에 한 번 I/O Call이 필요한 시점에 곧이어 읽을 가능성이 큰 블록들을 미리 캐시에 적재해 두는 기능입니다. 한번의 I/O Call로써 여러 Single Block I/O를 동시에 수행합니다.

 

NL 조인에서 항상 새 포맷의 실행계획이 나타나는 것은 아닙니다. 기본적으로 Outer 쪽 인덱스를 Unique Scan 할때는 동작하지 않습니다. 이 경우를 제외하면 언제든 새 포맷의 실행계획이 나타날 수 있습니다.

 

- Inner 쪽 Non-Unique인덱스를 Range Scan할때는 테이블 Prefetch 실행계획이 항상 나타남

- Inner 쪽 Unique 인덱스를 Non-Unique조건(모든 인덱스 구성컬럼이 '='조건이 아닐 때)으로 Range Scan할 때도 테이블 Prefetch 실행계획이 항상 나타남

- Inner 쪽 Unique 인덱스를 Unique 조건(모든 인덱스 구성컬럼이 '='조건)으로 액세스할 때도 테이블 Prefetch 실행계획이 나타날 수 있음. 이때 인덱스는 Range Scan으로 액세스하고 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 액세스

 

 

(7) 배치 I/O

관련 힌트는 nlj_batching, no_nlj_batching입니다.

오라클 11g에서 시작된 배치 I/O 메커니즘에 대해서는 아직 공식적으로 알려진 바가 없지만 아래 실행계획이 의미하는 바와 같이 Inner 쪽 인덱스만으로 조인을 (id=2)하고 나서 테이블과의 조인은 (id=1)은 나중에 일괄(batch)처리하는 메커니즘인 것으로 추정됩니다.

테이블 액세스를 나중에 하지만 부분범위처리는 정상적으로 작동합니다. 따라서 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩(Fetch Call 단위)나누어 처리하는 것을 알 수 있습니다.

 

배치 I/O 방식을 표현한 위 실행계획을 풀어서 설명하면 아래와 같습니다.

1. 드라이빙 테이블 에서 일정량의 레코드를 읽어 Inner 쪽 인덱스와 조인하면서 중간 결과집합을 만듭니다.

2.중간 결과집합이 일정량 쌓이면 Inner 쪽 테이블 레코드를 액세스합니다. 이때 테이블 블록을 버퍼 캐시에서 찾으면 바로 최종 결과집합에 담고 못찾으면 중간 집합에 남겨 둡니다.

3. 2번 과정에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크로부터 읽습니다. 이때 Multiplt Single Block I/O 방식을 사용합니다.

4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담습니다.

5. 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4번 과정을 반복합니다.

 

이것은 Outer 테이블로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해 테이블 Prefetch에 이어 추가로 도입된 메커니즘입니다.