Chap02 조인 원리와 활용

01. Nested Loops 조인

    1) 기본 메커니즘

        -> 이중 For문

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

        -> USE_NL(테이블 명 OR Alias)

    3) NL 조인 수행 과정 분석

        - OLTP 시스템에서 조인을 튜닝 시 NL 조인부터 고려

        - NL 조인의 부하 지점

              ⓐ Index Leaf Block에서 Rowid를 통한 Table Random Access 시

              ⓑ Table Random Access 후 또다른 Index와 Random Access 시

              ⓒ Secon Table의 Index Random Access 후 Leaf Block에서 Rowid를 통한 Table Random Access 시

 

    4) NL 조인의 특징

        - Random Access 위주의 조인 방식

        - 한 레코드씩 순차적으로 진행

        - 대용량 처리 시 치명적인 한계점 발생

        - 다른 조인 방식과 비교하였을 때 인덱스 구성 전략이 특히 중요

        - OLTP(온라인 트랜잭션 환경) 환경에 적합

 

    5) 테이블 Prefetch

 

        Prefetch 종류

        ⓐ Index Prefetch

            - DB_FILE_SEQUENTIAL READ 대기이벤트 발생

            - Single Block I/O(수직적탐색, Leaf Block 탐색)

        ⓑ Table Prefetch

            - DB_FILE_PARALLEL_READ 대기이벤트 발생

            - 인덱스 Leaf Block 탐색하면서 Table Access 시 Disk I/O가 예상될 때 관련 Block을 한꺼번에

              읽어 적재하는 방법

            - Random Access 성능 향상 -> Buffer Pinning 효과 -> 논리적 Block 요청 횟수 감소

            - Table Prefetch -> Disk I/O 대기 횟수 감소

 

        - MultiBlock I/O도 Prefetch 기능중 하나

 

        ※ MultiBlock I/O VS Prefetch

           MultiBlock I/O : 한 Extent에 속한 인접한 Block을 Prefetch 한다.

           Table Prefetch & Index Prefetch : 인접하지 않는 Block을 적재

                                                      (서로 다른 Extent에 위치한 Block을 배치 방식으로 미리 적재 하는 것)

                                                       이 때 성능은 더 극대화가 된다.

       

DB_FILE_PARALLEL_READ	DB_FILE_SCATTERED_READ
- Prefetch - Batch I/O	- Multi Block I/O   1) Table Full Scan   2) Index Fast Full Scan

 

 

    6) 배치 I/O

       

Batch I/O 실행계획

       결과집합을 한번에 생성하는것이 아니라

       중간중간마다 결과집합을 생성

       

 

    7) 버퍼 Pinning 효과

 

 

02. 소트 머지 조인

    1) 기본 메커니즘

        - 두 테이블을 각각 정렬(Sort)한 다음에 두 집합을 머지(Merge)하면서 조인수행

 

        소트단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬

        머지단계 : 정렬된 양쪽 집합을 Merge

    

        조인을 위해 실시간으로 인덱스를 생성하는것과 같은 효과

        양쪽 집합을 정렬 후 NL조인과 같은 방식으로 진행

        PGA 영역에서 처리로 빠른 속도

        소트 부하만 감수한다면 버퍼 캐시에서 조인하는게 NL 조인보다 유리

        조인 컬럼에 인덱스 유무와 상관없음

 

    2) 소트머지 조인 특징

        ⓐ 유용할 때

            - First Table에 소트 연산을 대체할 인덱스가 있을 때

            - 조인할 First 집합이 이미 정렬돼 있을 때

            - 조인 조건식이 등치 조건이 아닐 때

 

03. 해시 조인

    1) 기본 메커니즘

        - 둘 중 작은 집합(Build Input)을 읽어 Hash Area에 Hash Table을 생성,

          반대쪽 큰 집합(Probe Input)을 읽어 Hash Table을 탐색하면서 Join하는 방식

 

        - Hash Table을 탐색 시 Hash Function을 이용하여 탐색

        - 등치('=')조건만 사용 가능

 

        - NL 조인처럼 Random Access 부하 없음

        - Sort Merge 조인처럼 소트부하도 없음

        - 단, Build Input이 Hash Area에 담길만큼 작아야 함 -> 성능의 성패

        - NL조인은 Inner Table의 Buffer Cache 탐색을 위해 Latch 획득 과정으로 이슈가 생길수있지만

          Hash 조인은 래치획득과정이 없는 PGA에서 처리하는 바 빠른 탐색

 

        ※ 한쪽 Table이 Hash Area에 담길 정도로 충분히 작아야 함

        ※ Build Input Hash Key값의 중복이 거의 없어야 함

 

    2) 힌트를 이용한 조인 순서 및 Build Input 조정

 

        ⓐ SWAP_JOIN_INPUTS : Build Input을 사용자가 지정

        ⓑ NO_SWAP_JOIN_INPUTS : Build Input을 사용자가 지정을 안할 때

 

    3) Hash Join의 두가지 실행계획

       

       ⓐ 첫번째

                       

                   1. Region Table -> Build Input

                   2. Country Index와 Hash Join

                   3. 2번결과 -> Build Input 과 Locations Table의 Hash Join

                   4. 3번결과 -> Build Input 과 Departments Table의 Hash Join

                   .....

 

      ⓑ 두번째

     

                    1. Employees, Departments, Locations, Regions의 Hash Table 생성

                    2. Country Index의 한건을 읽어 Regions Hash Table을 탐색

                    3. 2번 조인에 성공한 레코드는 Locations Hash Table을 탐색

                    4. .....(Departments)

                    5. .....(Employees)

                    6. 다시 2번부터 수행(Index가 마칠때까지)

 

 

    4) Build Input이 Hash Area를 초과할 때 처리방식

   

        ⓐ Grace 해쉬조인

            1. 파티션단계

            2. 조인단계

        ⓑ Hybrid 해쉬조인

        ⓒ Recursive 해쉬조인

 

 

 

04. 조인 순서의 중요성

 

    1) NL Join 시

       where 절의 필터 적용 후 Data 량이 적은 쪽이 Driving

       -> Leaf Block의 Table Random Access 후 반대편 Index의 Random Access량이 많다면

           성능 부하

       

    2) Sort Merge Join

        ⓐ Disk Sort가 필요한 경우 : 큰 테이블 Driving이 유리(Disk I/O 회수 감소 유도)

        ⓑ PGA Sort Area 안에 담길경우 : 적은 테이블 Driving이 유리(Join 회수 감소)

 

    3) Hash Join

        Hash Area에 충분히 담길정도로 작은 테이블이 Driving(Build Input)이 되어야 함

 

 

05. Outer 조인

 

    1) Outer NL 조인

        (+)의 반대 쪽이 Driving Table로 선택

        Leading Hint로도 순서 변경 불가능 -> 논리적 모순

 

    2) Outer Sort Merge Join

       (+)의 반대 쪽이 Driving Table로 선택

       Leading Hint로도 순서 변경 불가능 -> 논리적 모순

 

    3) Outer Hash Join

       ⓐ Dept(Outer)를 Hash Table로 적재

       ⓑ Emp를 읽어 조인 성공한 건 결과 집합 적재

       ⓒ Dept Hash Table에 조인 성공한 건 표기

       ⓓ Dept Hash Table에서 조인 실패 건 결과 집합 적재

 

 

06. 스칼라 서브쿼리를 이용한 조인

 

    1) 스칼라 서브쿼리

        함수와 같이 하나의 값을 반환하는 Subquery

 

    2) Scala Subquery의 캐싱 효과

       Oracle은 스칼라 서브쿼리 수행횟수를 최소화하려고 그 입력 값과 출력 값을

       내부 캐시에 저장해둔다.

       - 스칼라 서브쿼리를 위한 캐시

         입력되는 값(메인쿼리의 결과 값)이 캐시 수보다 적어야 하며,

         코드성과 같이 동일 값이 자주 나타나야 효과적

       ※ 주로 메인쿼리가 M측이며, 스칼라서브쿼리는 1측을 조회할 때 효과적

       ※ DW에서는 서브쿼리 사용하면 안됨(몇 억건씩마다 저장된 캐시를 사용하지 않기 때문)

 

    3) 두 개 이상의 값을 리턴하고 싶을 때

 

        ⓐ 한번에 조회한 후 바깥 테이블에서 가공하여 사용

EMP Table을 전체 읽어야 하는 부담

Subquery로 변경 후 EMP Table을 3번 Access 부담

서브쿼리로 3개의 값을 한번에 조회한 후 바깥 테이블에서 가공하여 사용

        ⓑ 오브젝트 Type

            Type을미리 선언해야하는 불편함

Object 생성

 

07. 조인을 내포한 DML 튜닝

 

    1) 수정 가능 조인 뷰 활용

 

        ⓐ 전통적인 방식의 UPDATE 문

       

거래 테이블을 총 4번 Access로 비효율

 

거래 Table set절 1번 where 절 1번으로 Access

 

 

      ⓑ 수정 가능 조인 뷰

BYPASS_UJVC

         - Updatable Join View는 1:M 관계에서 M쪽만 변경가능

         - 1쪽 집합에 Unique Index가 없을 시, DBMS는 어느 테이블이 1쪽 집합인지 알 수 없음

         - 11g에서는 Merge로 대체 가능 12c 다시 살아남

 

 

 

 

 

08. 고급 조인 테크닉

 

    ⓐ 누적매출 구하기

        1) Window Function 활용(최근)

        2) Self Join(옛날)

        3) WITH ..CTE절(ms-sql)

[SQL]

SELECT 지점, 판매월, 매출액

SUM(매출액) OVER (PARTITION BY 지점 ORDER BY 판매월

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적액

FROM 누적매출_예제;

[SQL]

SELECT T1.지점, T1.판매월

MIN(T1.매출액) 매출액

SUM(T2.매출액) 누적액

FROM 누적매출_예제1 T1, 누적매출_예제1 T2

WHERE T2.지점 = T1.지점

AND T2.판매월 <= T1.판매월

GROUP BY T1.지점, T1.판매월;

 

    ⓑ 선분이력 끊기

     

 

 

 

 

    ⓒ 데이터 복제를 통한 소계 구하기

[SQL]

SELECT ROWNUM NO

FROM DUAL

CONNECT BY LEVEL <= 2;

[SQL]

SELECT EMPNO, SAL, DEPTNO

FROM EMP;

두 Query를 합쳐 카테시안 곱으로 나타내 후 소계를 구해보도록 하겠습니다.

 

[SQL-카테시안곱]

SELECT B.NO, A.DEPTNO, A.EMPNO, A.SAL

FROM EMP A,

(SELECT ROWNUM NO FROM DUAL

CONNECT BY LEVEL <= 2

) B;

 

[SQL-소계구하기 1단계]

SELECT A.DEPTNO

, DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계') 사번

, SAL

FROM EMP A,

(SELECT ROWNUM NO FROM DUAL

CONNECT BY LEVEL <= 2

) B;

 

[SQL-소계구하기 2단계]

SELECT A.DEPTNO

, DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계') 사번

, SUM(SAL) 급여합

, ROUND(AVG(SAL)) 급여평균

FROM EMP A,

(SELECT ROWNUM NO FROM DUAL

CONNECT BY LEVEL <= 2

) B

GROUP BY DEPTNO, NO, DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계')

ORDER BY 1, 2;

 

[SQL-Rollup으로 간편한 소계]

SELECT DEPTNO

, CASE WHEN GROUPING(EMPNO) = 1 AND GROUPING(DEPTNO) = 1 THEN '총계'

WHEN GROUPING(EMPNO) = 1 THEN '부서계'

ELSE TO_CHAR(EMPNO)

END 사원번호

, SUM(SAL) 급여합

, ROUND(AVG(SAL)) 급여평균

FROM EMP

GROUP BY ROLLUP(DEPTNO, EMPNO)

ORDER BY DEPTNO, 사원번호;

 

 

    ⓓ 상호배타적 관계의 조인

       

        1) 외래키 분리방법

            OUTER Join 조건으로 수행

            AND B.온라인권번호(+) = A.온라인권번호        

            AND C.실권번호(+) = A.실권번호;

 

        2) 외래키 통합 방법

            Index 구성에 따라 방법 2종

            - Join 컬럼을 Decode로 작성

              AND B.온라인권번호(+) = DECODE(A.상품권구분, '1', A.상품권번호)       

              AND C.실권번호(+) = DECODE(A.상품권구분, '2', A.상품권번호)

 

            - Union All 작성

              WHERE A.상품권구분 = '1'

              ....

              UNION ALL

              ...

              WHERE X.상품권구분 = '2'

              .....

 

 

 

    ⓔ 최종 출력 건에 대해서만 조인

        페이징 처리 시 제일 안쪽 Table에서 조인과 Filter를 다한 후 정렬하는 것은 많은 부하가 예상된다.

        원하는 겨로가를 먼저 추출 후 마지막에 Join 수행

 

 

    ⓕ 징검다리 테이블 조인을 이용한 튜닝

        같은 테이블을 또다시 읽어 Rowid를 통한 조인만 이뤄지도록(징검다리)