[Oracle Tunning] Join

티스토리 뷰

Database

[Oracle Tunning] Join

춘햄 2024. 8. 28. 14:13

1. NL Join (Nested Loop)

NL 조인은 두 테이블이 조인할 때, 드라이빙 테이블(Outer 테이블)에서 읽은 결과를 Inner 테이블로 건건이 조인을 시도하는 방식이다. 즉, 아래와 같이 동작한다.

for(i = 0; i < I_MAX; i++) { // DEPT 테이블을 읽음
    for(j = 0; j < J_MAX; j++) { // EMP 테이블을 읽음
        // 조인 로직
    }
}

- 사용 쿼리 예시

SELECT /*+ ORDERED USE_NL(E) */
    *
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO;

ORDERED: FROM 절에서 나열된 순서로 테이블을 읽도록 지시
USE_NL(E): E와 조인할 때 NL 조인을 사용하도록 지시
DEPT 테이블에서 데이터를 한 건 읽어서 EMP 테이블로 조인을 시도하고, 조인에 성공하는 만큼을 결과 집합에 담는다.
다시 DEPT 테이블로 돌아가서 다음 한 건을 읽고, 조인을 반복하는 방식이다.

- 실제 사용 예시

SELECT /*+ ORDERED USE_NL(B) */
    A.*,
    B.*
FROM ITEM A, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID --- 3
    AND A.ITEM_TYPE_CD = '100100' --- 1
    AND A.SALE_YN = 'Y' --- 2 
    AND B.SALE_YN = 'Y'; --- 4

ITEM_X01 => ITEM_TYPE_CD 인덱스
UITEM_PK => ITEM_ID + UITEM_ID 인덱스
위 쿼리에서는 ORDERED 힌트를 사용했기 때문에 ITEM을 먼저 읽는다.
1. ITEM의 인덱스(ITEM_X01)를 이용해 A.ITEM_TYPE_CD = '100100'에 해당하는 데이터를 추출한다.
2. 추출된 데이터를 가지고 테이블로 이동하여 A.SALE_YN = 'Y'에 해당하는 데이터를 최종 드라이빙 데이터로 만든다.
3. ITEM 테이블에서 최종적으로 추려진 데이터를 가지고 UITEM 테이블과 조인 조건인 A.ITEM_ID = B.ITEM_ID 구문을 수행한다.
4. 조인에 성공하면 마지막 조건인 B.SALE_YN = 'Y'에 해당하는 데이터를 최종 집합으로 만든다.
NL 조인은 랜덤 액세스 방식이라 비효율적으로 보이지만, 한 건씩 처리하기 때문에 대용량 테이블에서 페이징 처리 시 적절한 인덱스만 있다면 극적으로 처리 속도 개선의 여지가 있다.
최적의 인덱스 설계:
1. ITEM_NEW => ITEM_TYPE_CD + SALE_YN
2. UITEM_NEW => ITEM_ID + SALE_YN

2. Sort Merge Join

Sort Merge Join은 두 테이블을 각각 조건에 맞게 읽고, 조인 컬럼을 기준으로 정렬한 후 조인을 수행하는 방식이다.
NL 조인은 드라이빙 테이블을 먼저 읽어 조인 대상을 먼저 거르고, 조인 대상이 되는 데이터가 한 건 발생할 때마다 inner 테이블과 조인을 시도한다. 반면, Sort Merge Join은 각 테이블에서 조인 대상이 되는 조건을 먼저 걸러 대상이 되는 두 테이블 데이터를 만든 후에 조인을 시도한다.
오라클에서는 정렬을 PGA라는 공간에서 수행하며, 이 공간은 프로세스에 할당된 독립된 공간이므로 버퍼 캐시를 사용하는 NL 조인에 비해 조인을 시도하는 데이터 접근이 더 빠르다.

- 사용 쿼리 예시

SELECT /*+ ORDERED USE_MERGE(E) */
    * 
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO;

- 세부 조인 동작

1. First 테이블(Outer 테이블)을 조인 컬럼 기준으로 정렬한다.
SELECT * FROM DEPT ORDER BY DEPTNO;

2. Second 테이블(Inner 테이블)도 조인 컬럼 기준으로 정렬한다.
SELECT * FROM EMP ORDER BY DEPTNO;

3. 두 테이블을 조인한다.
D.DEPTNO = E.DEPTNO

begin_point = 0;

for(i = 0; i < I_MAX; i++) {
    for(j = begin_point; j < J_MAX; j++) {
        while (d.deptno == e.deptno) {
            // 조인 대상이 있는 동안 반복해서 조인 시도
            begin_point = j + 1; // 다음 시작 위치 저장
        }
        break; // 조인 시도 후 루프 탈출
    }
}

각 테이블을 한 번씩만 읽어 데이터를 추출, 정렬하여 조인키로 정렬했으므로 조인 횟수를 줄일 수 있는 기법이다.
Sort Merge 조인은 정렬이 필요하므로 조인 대상이 되는 데이터가 많거나 조인 컬럼에 인덱스가 없는 경우 부하가 발생할 수 있다.

- 실제 사용 예시

SELECT /*+ ORDERED USE_MERGE(B) */
    A.*,
    B.*
FROM ITEM A, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID --- 4
    AND A.ITEM_TYPE_CD = '100100' --- 1
    AND A.SALE_YN = 'Y' --- 2 
    AND B.SALE_YN = 'Y'; --- 3

ITEM_X01 => ITEM_TYPE_CD 인덱스
UITEM_PK => 없음

ITEM 테이블의 ITEM_X01 인덱스를 이용해 A.ITEM_TYPE_CD = '100100'에 해당하는 데이터를 추출한다.
추출된 데이터를 가지고 테이블로 이동해 A.SALE_YN = 'Y'에 해당하는 데이터를 최종 추출 데이터로 만든다.
ITEM에서 최종적으로 추려진 데이터를 가지고 조인 컬럼인 A.ITEM_ID를 기준으로 정렬한다.
UITEM 테이블을 읽는데, 이 테이블에는 인덱스가 없기 때문에 테이블 전체를 스캔하면서 B.SALE_YN = 'Y'에 해당하는 데이터를 최종 추출 데이터로 만들고, 마찬가지로 B.ITEM_ID를 기준으로 정렬한다.
조인 조건인 A.ITEM_ID = B.ITEM_ID 조건을 수행한다.

최적의 인덱스 설계:
1. ITEM_NEW => ITEM_TYPE_CD + SALE_YN + ITEM_ID
2. UITEM_NEW => SALE_YN + ITEM_ID
Sort Merge Join은 실제로 잘 사용되지 않는다. 조인을 하기 위해 조인과 직접적으로 관련이 없는 정렬을 하기 때문에 그 성능이 해시 조인보다 떨어질 수 있다.

3. Hash Join

Hash Algorithm

오라클이 사용하는 해시 알고리즘은 해시 버킷을 생성하고 해시 함수를 통해 반환된 값으로 해시 버킷을 찾아가 해시 체인을 구성하여 해시 맵을 만드는 방식이다.
예를 들어, f(x) = mod(x, 10)가 해시 함수라면, 함수의 output으로 나올 수 있는 0 ~ 9까지가 해시 버킷이 되며, 해시 버킷 내에 체인을 형성한다.

Hash Join

오라클에서 해시 조인을 위해 해시 맵을 만들 때 두 테이블 중 작은 테이블을 읽어 만든다.
큰 테이블을 읽어 해시 함수를 통해 해시 버킷을 찾아가 실제 데이터를 찾는다.
- 작은 테이블: Build Input
- 큰 테이블: Probe Input
해시 맵에는 조인 컬럼과 SELECT 절에서 사용한 컬럼까지 포함된다.
SQL 작성 시 꼭 필요한 컬럼만 기술하는 것이 PGA 사용량을 줄이는 방법이다.

- 사용 쿼리 예시

SELECT /*+ ORDERED USE_HASH(E) */
    * 
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO;

ORDERED를 사용하면 첫 번째 테이블인 DEPT를 먼저 읽어 Build Input으로 선택하여 해시 맵을 만들고, EMP 테이블을 Probe Input으로 선택해 EMP 테이블을 읽으면서 조인을 시도한다.
Build Input을 옵티마이저에게 선택하도록 맡기려면 아래와 같이 힌트를 사용해야 한다.

SELECT /*+ USE_HASH(E) */
    * 
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO;

3개 이상의 테이블을 조인할 경우 Build Input을 사용자가 지정하려면 아래와 같이 SWAP_JOIN_INPUTS를 사용해야 한다.

SELECT /*+ USE_HASH(E) SWAP_JOIN_INPUTS(E) */
    * 
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO;

Hash Join 시에는 Build Input이 작아야 유리하다.
- Build Input이 지나치게 큰 테이블로 선택되면 PGA 내 해시 영역에 적재하기 힘들어지고, 디스크 공간을 사용하게 되며, 이런 상황이 발생하면 해시 조인의 성능이 크게 떨어질 수 있다.
Hash 함수는 같은 값이 입력되면 반환 값도 같으므로, 조인 키 값의 Distinct Value 수와 전체 값의 수가 큰 차이를 보이면 성능 저하가 발생할 수 있다.
- 조인 컬럼의 값에 중복 값이 적거나 WHERE 절에 조인 컬럼으로 값을 걸러내는 필터 조건이 없어야 한다.
또한 Sort Merge와 Hash 조인은 정렬 과정과 해시 맵을 만드는 과정에서 많은 CPU 부하를 가져온다. NL 조인으로 처리할 수 있음에도 해시 조인으로 처리하면 쉽게 성능 향상 효과가 나타나기 때문에 해시 조인을 남용하는 경향이 있지만, 이는 시스템 전체 측면에서 좋은 현상이 아니다.

각 Join 기법 사용 시기

NL Join:
- 온라인 트랜젝션이 많고 부분 범위 처리(주로 페이징에서 사용 가능)가 가능할 때
- 각 테이블의 데이터는 많지만 추출 대상이 되는 데이터의 양이 많지 않을 때
Sort Merge Join:
- 첫 번째 테이블에 조인 컬럼 기준으로 인덱스가 있어 정렬 부하가 발생하지 않을 때
- Group by 또는 Order by 등으로 이미 정렬한 서브 쿼리와 조인 시도 시 두 번째 테이블의 양이 적을 때
- 테이블의 양이 매우 커서 NL 조인이 힘들고, 조건이 = 조인이 아니어서 해시 조인도 사용하기 힘들 때
Hash Join:
- 대용량 테이블을 조인 시도하는데 조인 컬럼에 적당한 인덱스가 없어서 NL 조인이 힘들 때
- 조인 컬럼에 인덱스가 있지만 드라이빙 테이블의 결과 건수가 많아 Inner 테이블로 많은 양의 랜덤 액세스가 발생할 때
- 두 테이블의 양이 많아 Sort Merge 조인 시도 시 정렬로 인한 부하가 클 때

Join 방식에 따라 달라지는 인덱스 설계

아래 예제 쿼리를 튜닝한다고 가정했을 때, 각 조인 기법에 따라 인덱스 설계를 어떻게 해야 하는지 알아보자.

SELECT
    A.*,
    B.*
FROM ITEM A, UITEM B
WHERE A.ITEM_ID = B.ITEM_ID
AND A.ITEM_TYPE_CD = '100101'
AND A.SALE_YN = 'Y'
AND B.SALE_YN = 'Y';

1. NL Join

ITEM은 드라이빙 테이블이므로 조인 컬럼을 인덱스에 추가해야 할 상황이라면 뒤에 배치해야 한다.
즉, 인덱스의 선두 컬럼은 ITEM_TYPE_CD와 SALE_YN이 후보이다.
조인 컬럼을 인덱스에 추가했을 때 테이블로의 랜덤 액세스가 사라진다면 성능 향상 효과가 있는 것이다. 그렇지 않다면 조인 컬럼을 추가하지 않는 것이 좋다.
UITEM 테이블은 NL 조인에서 Inner 테이블이다. NL 조인의 핵심은 Inner 테이블에 조인 컬럼이 반드시 인덱스로 있어야 한다는 점이다.
- 따라서 인덱스는 ITEM_ID + SALE_YN 또는 SALE_YN + ITEM_ID가 되어야 한다.

2. Sort Merge Join

Sort Merge Join은 각 테이블에서 조인 컬럼으로 정렬이 발생한다.
이를 염두에 두고 인덱스를 설계해야 한다.
- ITEM 테이블은 ITEM_TYPE_CD + SALE_YN 또는 SALE_YN + ITEM_TYPE_CD 순서로 구성해야 하며, 정렬이 발생하지 않도록 조인 컬럼을 추가해야 한다.
- 따라서 인덱스는 ITEM_TYPE_CD + SALE_YN + ITEM_ID 또는 SALE_YN + ITEM_TYPE_CD + ITEM_ID로 구성할 수 있다. 인덱스에서 ITEM_ID 컬럼을 제거하면 정렬이 발생할 수 있다.
- UITEM 테이블도 마찬가지이다. Sort Merge Join은 두 테이블이 모두 정렬을 위한 인덱스가 있어도 inner 테이블은 조인 대상 전체를 읽어 PGA에 적재한다. 따라서 Inner 테이블에서도 조인 컬럼을 인덱스에 추가하여 정렬이 발생하지 않도록 해야 한다.
- INDEX_02 = SALE_YN + ITEM_ID로 구성이 가능하다.

3. Hash Join

Hash Join으로 조인할 경우, 다른 조인에 비해 인덱스를 만드는 경우가 덜 발생한다.
또한 인덱스를 만드는 것도 가장 간단하다.
- ITEM 테이블에서는 INDEX_01 = ITEM_TYPE_CD + SALE_YN 또는 SALE_YN + ITEM_TYPE_CD로 구성할 수 있다.
- UITEM 테이블에서는 INDEX_02 = SALE_YN으로만 구성할 수 있다.
Hash Join에서는 조인 컬럼이 인덱스에 포함되지 않아도 성능상의 손익이 크지 않다.

Outer 조인

1. Outer NL Join

Outer NL 조인은 NL 조인의 특성상 Outer 조인을 할 때, 조인 방향이 한쪽으로 고정된다.
Outer 기호(+)가 붙지 않은 테이블을 항상 드라이빙 테이블로 선택한다. LEADING이나 ORDERED 힌트를 사용하더라도 그 순서를 변경할 수 없다.

SELECT /*+ LEADING USE_NL(E) */
    *
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO(+);

위 쿼리를 호출하면, USE_NL(E)는 무시되고, Hash 조인으로 옵티마이저가 알아서 변경한다.

2. Outer Sort Merge Join

Sort Merge Join은 각 테이블의 조인 대상 집합을 정렬한 후 조인한다는 점에서 NL 조인과 다르지만, 그 원리는 동일하다.
NL 조인과 마찬가지로 조인 방향이 한쪽으로 고정된다.
즉, NL 조인과 마찬가지로 LEADING이나 ORDERED 힌트를 사용해도 그 순서를 바꿀 수 없다.

SELECT /*+ LEADING USE_MERGE(E) */
    *
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO(+);

3. Outer Hash Join

Hash Join에서는 데이터가 작은 테이블로 Hash Map을 만들어야 부하를 줄일 수 있는데, Outer Hash Join에서는 SWAP_JOIN_INPUTS 힌트를 사용해 이를 결정할 수 있다.

SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
    *
FROM DEPT D, EMP E
WHERE 1 = 1
AND D.DEPTNO = E.DEPTNO(+);

스칼라 서브 쿼리로의 조인

스칼라 서브 쿼리는 프로그래밍 언어의 함수처럼 입력 값에 대한 반환 값을 하나만 반환하는 쿼리이다. SELECT 문에서 사용된다.

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    (SELECT DNAME FROM DEPT WHERE DEPTNO  = E.DEPTNO) DNAME
FROM EMP E;

위와 같은 쿼리에서, 만약 E.DEPTNO에 해당하는 값이 테이블에 없다면 NULL을 반환한다. 즉, Outer Join과 같은 결과를 기대할 수 있다.
스칼라 서브 쿼리의 가장 큰 특징은 내부적으로 캐시에 값을 저장해 둔다는 점이다.
- 입력 값과 출력 값이 같으면 쿼리를 수행하지 않는다는 원리로 캐시에 저장한다.
- 이때 입력 값과 출력 값을 저장해 두고 스칼라 서브 쿼리가 실행될 때, 캐시에서 입력 값(위 쿼리에서는 E.DEPTNO)을 찾아본다.
- 만약 값이 있으면 미리 저장된 출력 값을 반환하고, 없으면 스칼라 서브 쿼리를 실행해 결과 값을 캐시에 저장한다.
스칼라 서브 쿼리는 자주 반복되고 값의 종류가 다양하지 않은 테이블(예: 공통 코드 테이블)에 조인을 할 때 효과적이다.
그러나 스칼라 서브 쿼리는 반환 값을 하나밖에 사용하지 못하는 단점이 있다. 이를 극복할 방법도 있다.

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    (SELECT DNAME FROM DEPT WHERE DEPTNO  = E.DEPTNO) DNAME,
    (SELECT LOC FROM DEPT WHERE DEPTNO  = E.DEPTNO) LOC
FROM EMP E;

위 쿼리는 같은 테이블에 같은 조건으로 스칼라 서브 쿼리를 두 개 이상 사용하고 있다. 이는 같은 테이블을 두 번 액세스하는 비효율이 발생한다.

1. 정규식을 사용하여 해결

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    REGEXP_SUBSTR(SUB, '[^$]+', 1, 1) DNAME,
    REGEXP_SUBSTR(SUB, '[^$]+', 1, 2) LOC
FROM (
    SELECT 
        E.EMPNO,
        E.ENAME,
        E.JOB,
        (
            SELECT 
                DNAME || '$' || LOC 
            FROM DEPT 
            WHERE DEPTNO = E.DEPTNO
        ) SUB
    FROM EMP E
);

여기서 DNAME 컬럼과 LOC 컬럼 값에는 $ 문자가 절대 존재해서는 안 된다.
REGEXP_SUBSTR 함수는 문자열에 대해 정규식을 사용하므로, 쿼리 결과 값이 많으면 약간의 부하가 발생할 수 있다.

2. TRIM(SUBSTR())을 사용하여 해결

SELECT 
    E.EMPNO,
    E.ENAME,
    E.JOB,
    TRIM(SUBSTR(SUB, 1, 20)) DNAME,
    TRIM(SUBSTR(SUB, 21, 20)) LOC
FROM (
    SELECT 
        E.EMPNO,
        E.ENAME,
        E.JOB,
        (
            SELECT 
                LPAD(DNAME, 20) || LPAD(LOC, 20)
            FROM DEPT 
            WHERE DEPTNO = E.DEPTNO
        ) SUB
    FROM EMP E
);

LPAD 함수를 사용하여 두 컬럼 값을 연결한 뒤, 추출하여 사용한다.
LPAD 함수를 사용할 때는 컬럼 길이보다 LPAD로 받는 문자열 길이를 길게 설정해야 한다.

저작자표시

'Database' 카테고리의 다른 글

[Oracle Tunning] 인덱스 활용과 튜닝 (0)	2024.08.28
[Oracle Tunning] INDEX Scan 방식 (0)	2024.08.28
[Oracle Tunning] INDEX 기본 (1)	2024.08.28
[Oracle Tunning] DBMS_XPLAN.DISPLAY_CURSOR (0)	2024.08.28
[Oracle Tunning] 오라클 아키텍쳐 (0)	2024.08.28

공유하기 링크

Comments

Related Articles more

최근에 올라온 글

최근에 달린 댓글

TAG more

Total

Today

Yesterday

Jun's TID

티스토리 뷰

[Oracle Tunning] Join

1. NL Join (Nested Loop)

2. Sort Merge Join

3. Hash Join

각 Join 기법 사용 시기

Join 방식에 따라 달라지는 인덱스 설계

1. NL Join

2. Sort Merge Join

3. Hash Join

Outer 조인

1. Outer NL Join

2. Outer Sort Merge Join

3. Outer Hash Join

스칼라 서브 쿼리로의 조인

'Database' 카테고리의 다른 글

티스토리툴바