오라클 SQL 튜닝 by Nested Loops Join

오라클 SQL 튜닝 by Nested Loops Join

오라클의 조인은 크게 조인방식과 조인형식으로 나누어집니다.

조인 방식

• 중첩 루프 조인 ( Nested Loops Join )
• 소트 머지 조인 ( Sort Merge Join )
• 해쉬 조인 ( Hash Join )

조인 형식

• 기본 조인
• 아우터 조인
• 세미 조인
• 카테시안 조인
• 부정 조인

여기서 얘기해 볼 내용은 실행계획을 볼 때 많이 보이는 Nested Loops Join 에 대해서 얘기해보려고 합니다.

일단 실행계획을 알아보기 전에 먼저 알아야 할 부분은 Driving 테이블과 Inner 테이블이 어떤 테이블인지 알아야합니다.

Driving 테이블 : 2개 테이블 중 Join 을 실행 시 먼저 Access 하는 테이블

Inner 테이블    : 2개 테이블 중 Join 을 실행 시 나중에 Access 하는 테이블

2개의 테이블을 예시로 들었지만 3개, 4개, 그 이상의 테이블을 조인시에도 먼저 Access 되는 테이블은 Driving 테이블 나중에 Access 되는 테이블은 Inner 테이블이 됩니다.

( 아무리 테이블이 많이 조인되어도 하나씩 풀어보면 2개의 테이블간 조인이 이루어지기 때문입니다. )

Nested Loops Join 의 수행방식을 알아보겠습니다.

SELECT DEPT_NM
     , EMP_NM
     , SALARY
  FROM EMP T1
 INNER JOIN DEPT T2
 WHERE T1.SALARY >= 100000
   AND T2.LOC = 'SEOUL'

Index 생성 내역

DEPT 테이블 : CREATE INDEX IX_DEPT ON DEPT ( LOC )
EMP 테이블 : CREATE INDEX IX_EMP ON EMP ( DEPT_NO )

1. Driving 테이블의 Index Scan 후 테이블 엑세스

    Driving 테이블이 DEPT 테이블로 선정이 되었다면 인덱스 스캔이 이루어져 LOC 이 SEOUL 인 항목의 값을 INDEX 에서 값을 엑세스합니다.

    그 후 엑세스 된 ROWID 값을 받아서 DEPT 테이블의 ROWID 값으로 DEPT_NO 값을 찾게됩니다.

2. INNER 테이블인 EMP 테이블의 IX_EMP 인덱스 스캔

    1에서 찾은 DEPT_NO 의 값을 찾아서 EMP 테이블의 IX_EMP 인덱스를 엑세스하여 DEPT_NO 의 값을 찾습니다.

    Driving 테이블과 마찬가지로 DEPT_NO 에 해당하는 ROWID 를 찾게됩니다.

 

3. EMP 테이블 스캔

    2에서 찾은 IX_EMP 인덱스의 ROWID 의 값으로 EMP 테이블의 DEPT_NO 를 랜덤엑세스합니다.

4. EMP 테이블의 조건 수행

    3 에서 추출된 데이터를 대상으로 EMP 테이블의 조건절에 해당하는 SALARY >= 100000 인 데이터를 확인하여 추출합니다.

5. 반복 수행

    1~4단계에 해당하는 과정을 반복수행하여 LOC = 'SEOUL' 인 데이터가 없을 때까지 반복 수행합니다.

이런 경우 실행계획은 아래와 같이 이루어집니다.

NESTED LOOPS
    TABLE ACCESS ( BY INDEX ROWID) OF 'DEPT' (TABLE)
          INDEX ( RANGE SCAN ) OF 'IX_DEPT' (INDEX)
    TABLE ACCESS ( BY INDEX ROWID) OF 'EMP' (TABLE)
          INDEX ( RANGE SCAN ) OF 'IX_EMP' (INDEX)

이렇게 실행계획이 나오면 NESTED LOOPS 밑에서 첫번째 나오는 테이블이 DRIVING 테이블, 두번째 나오는 테이블이 INNER 테이블이 됩니다.