아우터 조인

Outer Join 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

Intro

아우터 조인

아우터 조인

• 테이블 A와 B가 있을 때 A 테이블을 기준으로 B 테이블이 조인에 성공하면 B 테이블의 데이터를 보여주고, 조인에 실패하면 B 테이블의 데이터를 보여주지 않는 조인 방식이다.

아우터 조인의 2가지 방식

• Left Outer Join

  • 왼쪽에 명시한 테이블을 기준이 되는 Outer 조인 방식

  • 오른쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식

• Right Outer Join

  • 오른쪽에 명시한 테이블이 기준이 되는 Outer 조인 방식

  • 왼쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식

아우터 조인 튜닝

• Outer 조인으로 구현된 SQL문의 성능을 극대화하는 일련의 모든 활동을 의미한다.

아우터 조인을 이용하여 테이블 스캔 최소화

• 테이블 A, B, C이 있다고 가정

• A = B + C의 관계가 성립하는 경우 테이블 B와 C는 테이블 A에 대하여 배타적 관계에 있다고 할 수 있다.

• 테이블 A를 기준으로 테이블 B와 C를 각각 조인할 때 UNION ALL문을 사용하여 두 개의 SELECT 문의 합집합을 구하게 된다.

• 이러한 경우 UNION ALL 문을 아우터 조인으로 변환하여 성능을 개선할 수 있다. (가장 큰 테이블인 A를 단 한번만 스캔하는 것이 핵심)

아우터 조인을 스칼라 서브쿼리로 변환

• 아우터 조인으로 구현된 SQL 문은 스칼라 서브쿼리로 변환할 수 있다.

• 오라클에서는 스칼라 서브쿼리로 한 번 이상 호출된 Input/Ouput 값을 멀티 버퍼에 저장해 둔 후 동일한 Input으로 호출되면 기존에 가지고 있던 Output 값을 바로 리턴하는 스칼라 서브쿼리 캐싱 기능이 있다.

• 이러한 오라클의 기능을 잘 활용하여 아우터 조인을 스칼라 서브쿼리로 변환하면 성능을 극대화할 수 있다.

아우터 조인을 위한 준비

• 테이블 생성

CREATE TABLE TB_JOB_ORDER
(
    JOB_NO        VARCHAR2(10),
    VISIT_PRE_DT  VARCHAR2(8),
    WORKER_ID     VARCHAR2(10),
    JOB_STATUS_CD VARCHAR2(2),
    JOB_GUBUN     VARCHAR2(1),
    REQ_NO        VARCHAR2(10),
    INST_DTM      DATE,
    INST_ID       VARCHAR2(50)
);

CREATE TABLE TB_OPEN_REQ
(
    OPEN_REQ_NO  VARCHAR2(10),
    OPEN_REQ_DT  VARCHAR2(8),
    OPEN_HOPE_DT VARCHAR2(8),
    CUST_NO      VARCHAR2(10),
    INST_DTM     DATE,
    INST_ID      VARCHAR2(50)
);

CREATE TABLE TB_DISABLE_REQ
(
    DISABLE_REQ_NO VARCHAR2(10),
    DISABLE_REQ_DT VARCHAR2(8),
    DISABLE_DTM    DATE,
    CUST_NO        VARCHAR2(10),
    INST_DTM       DATE,
    INST_ID        VARCHAR2(50)
);

• TB_OPEN_REQ 더미 데이터 입력

INSERT INTO TB_OPEN_REQ
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       RANDOM_DT,
       TO_CHAR(TO_DATE(RANDOM_DT, 'YYYYMMDD') + DBMS_RANDOM.VALUE(1, 30), 'YYYYMMDD'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'),
       SYSDATE,
       'DBMSEXPERT'
FROM (
         SELECT TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD') RANDOM_DT
         FROM DUAL
     ) A,
     DUAL
CONNECT BY LEVEL <= 1000000;

COMMIT;

• TB_DISABLE_REQ 테이블 더미 데이터 입력

INSERT INTO TB_DISABLE_REQ
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       RANDOM_DT,
       TO_DATE(RANDOM_DT || TO_CHAR(SYSDATE - 864000 / 24 / 60 / 60, 'HH24MISS'), 'YYYYMMDDHH24MISS'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'),
       SYSDATE,
       'DBMSEXPERT'
FROM (
         SELECT TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD') RANDOM_DT
         FROM DUAL
     ) A,
     DUAL
CONNECT BY LEVEL <= 1000000;

COMMIT;

• TB_JOB_ORDER 테이블 더미 데이터 입력

INSERT /*+ APPEND */ INTO TB_JOB_ORDER
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'),
       LPAD(TO_CHAR(TRUNC(MOD(DBMS_RANDOM.VALUE(1, 10000), 10))), 2, '0'),
       JOB_GUBUN,
       REQ_NO,
       SYSDATE,
       'DBMSEXPERT'
FROM DUAL,
     (SELECT '1' JOB_GUBUN, OPEN_REQ_NO REQ_NO
      FROM TB_OPEN_REQ
      UNION ALL
      SELECT '2'            JOB_GUBUN,
             DISABLE_REQ_NO REQ_NO
      FROM TB_DISABLE_REQ
     ) A;

COMMIT;

• 테이블 제약 조건 추가

ALTER TABLE TB_JOB_ORDER
    ADD CONSTRAINT TB_JOB_ORDER_PK
        PRIMARY KEY (JOB_NO);

ALTER TABLE TB_OPEN_REQ
    ADD CONSTRAINT TB_OPEN_REQ_PK
        PRIMARY KEY (OPEN_REQ_NO);

ALTER TABLE TB_DISABLE_REQ
    ADD CONSTRAINT TB_DISABLE_REQ_PK
        PRIMARY KEY (DISABLE_REQ_NO);

• 통계 정보 생성

ANALYZE TABLE TB_JOB_ORDER COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

ANALYZE TABLE TB_OPEN_REQ COMPUTE STATISTICS FOR TABLE
    FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

ANALYZE TABLE TB_DISABLE_REQ COMPUTE STATISTICS FOR TABLE
    FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

아우터 조인으로 테이블 스캔을 최소화하여 성능 개선하기

튜닝 전

SELECT A.JOB_NO, A.WORKER_ID, A.JOB_STATUS_CD, A.REQ_NO
FROM TB_JOB_ORDER A,
     TB_OPEN_REQ B
WHERE A.VISIT_PRE_DT = '20151102'
  AND A.JOB_GUBUN = '1'
  AND A.REQ_NO = B.OPEN_REQ_NO
UNION ALL
SELECT A.JOB_NO, A.WORKER_ID, A.JOB_STATUS_CD, A.REQ_NO
FROM TB_JOB_ORDER A,
     TB_DISABLE_REQ B
WHERE A.VISIT_PRE_DT = '20151102'
  AND A.JOB_GUBUN = '2'
  AND A.REQ_NO = B.DISABLE_REQ_NO;

-- [2021-07-29 17:39:05] 239 rows retrieved starting from 1 in 173 ms (execution: 30 ms, fetching: 143 ms)

• SQL 분석

  • TB_JOB_ORDER 테이블과 TB_OPEN_REQ 테이블을 조인

  • TB_JOB_ORDER 테이블과 TB_DISABLE_REQ 테이블을 조인

  • 각각의 SELECT 문을 UNION ALL 하여 결과 집합을 도출

• SQL 문제점

  • 가장 큰 용량의 테이블인 TB_JOB_ORDER 테이블을 두 번이나 스캔하는 문제

  • 해당 테이블을 단 한 번만 스캔하여 결과 집합을 도출해야함

• 실행 계획

  • TB_JOB_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL)

  • TB_OPEN_REQ_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • 위 동작을 중첩 루프 조인(NESTED LOOPS)

  • TB_JOB_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL)

  • TB_DISABLE_REQ_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • 바로 위 작업을 중첩 루프 조인(NESTED LOOPS)

  • UNION ALL

  • SELECT문을 종료

아우터 조인 테이블 스캔 최소화 튜닝 전

튜닝 후

SELECT /*+ LEADING(A) USE_NL(B) USE_NL(C) */
    A.JOB_NO,
    A.WORKER_ID,
    A.JOB_STATUS_CD,
    A.REQ_NO
FROM TB_JOB_ORDER A,
     TB_OPEN_REQ B,
     TB_DISABLE_REQ C
WHERE A.VISIT_PRE_DT = '20151102'
  AND DECODE(A.JOB_GUBUN, '1', A.REQ_NO) = B.OPEN_REQ_NO(+)
  AND DECODE(A.JOB_GUBUN, '2', A.REQ_NO) = C.DISABLE_REQ_NO(+);

-- [2021-07-29 17:39:24] 239 rows retrieved starting from 1 in 122 ms (execution: 49 ms, fetching: 73 ms)

• SQL 분석

  • LEADING 힌트를 사용하여 TB_JOB_ORDER 테이블을 Outer테이블로 지정, USE_NL 힌트를 이용하여 TB_OPEN_REQ 테이블을 Inner 테이블로 지정

  • TB_ORD_ORDER 테이블과 TB_OPEN_REQ 테이블의 중첩 루프 조인 결과가 나오면 해당 결과를 Outer 테이블로 하고, TB_DISABLE_REQ 테이블을 Inner 테이블로 하여 중첩 루프 조인

  • TB_ORD_ORDER 테이블의 JOB_GUBUN의 값이 '1'인 경우 TB_OPEN_REQ 테이블과 LEFT OUTER JOIN

  • TB_ORD_ORDER 테이블의 JOB_GUBUN의 값이 '2'인 경우 TB_DISABLE_REQ 테이블과 LEFT OUTER JOIN

• 실행 계획

  • TB_JOB_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL)

  • TB_OPEN_REQ_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • 위 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER)

  • TB_DISABLE_REQ_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • 바로 위 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER)

  • SELECT 절의 연산 수행

아우터 조인으로 테이블 스캔을 최소화하여 성능 개선

아우터 조인을 스칼라 서브쿼리 방식으로 변환하여 성능 극대화하기

• 테이블 설계

CREATE TABLE TB_TRD_DAY
(
    TRD_DT         VARCHAR2(8), --거래일자
    INSU_CD        VARCHAR2(4), --인수코드
    INSU_DETAIL_CD VARCHAR2(6), --인수상세코드
    TRD_CNT        NUMBER,      --거래건수
    CNCL_CNT       NUMBER,      --취소건수
    EXPORTER_NO    VARCHAR2(10) --수출자번호
);

CREATE TABLE TB_EXPORTER
(
    EXPORTER_NO VARCHAR2(10), --수출자번호
    EXPORTER_NM VARCHAR2(50)  --수출자명
);

• 데이터 입력

INSERT INTO TB_EXPORTER
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       DBMS_RANDOM.STRING('U', 50)
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

ALTER TABLE TB_TRD_DAY
    NOLOGGING;

INSERT /*+ APPEND */ INTO TB_TRD_DAY A -- APPEND 힌트 사용
SELECT TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(0, 3650)), 'YYYYMMDD'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 9999))), 4, '0'),
       LPAD(TO_CHAR(ROWNUM), 6, '0'),
       TRUNC(DBMS_RANDOM.VALUE(1, 999)),
       TRUNC(DBMS_RANDOM.VALUE(1, 999)),
       B.EXPORTER_NO
FROM TB_EXPORTER B,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10);

COMMIT;

• 테이블 제약 조건 추가

ALTER TABLE TB_TRD_DAY
    ADD CONSTRAINT TB_TRD_DAY_PK
        PRIMARY KEY (TRD_DT, INSU_CD, INSU_DETAIL_CD);

ALTER TABLE TB_EXPORTER
    ADD CONSTRAINT TB_EXPORTER_PK
        PRIMARY KEY (EXPORTER_NO);

• 통계 정보 생성

ANALYZE TABLE TB_EXPORTER COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

ANALYZE TABLE TB_TRD_DAY COMPUTE STATISTICS
    FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

튜닝 전

SELECT /*+ LEADING(A) USE_NL(B) INDEX(A TB_TRD_DAY_PK) */
    A.EXPORTER_NO,
    B.EXPORTER_NM,
    SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNT
FROM TB_TRD_DAY A
         LEFT OUTER JOIN TB_EXPORTER B
                         ON (A.EXPORTER_NO = B.EXPORTER_NO)
                             AND A.TRD_DT BETWEEN '20100101' AND '20151231'
GROUP BY A.EXPORTER_NO, B.EXPORTER_NM
ORDER BY A.EXPORTER_NO;

-- [2021-07-29 18:18:15] 199,678 rows retrieved starting from 1 in 12 s 522 ms (execution: 7 s 29 ms, fetching: 5 s 493 ms)

• SQL 분석

  • LEADING 힌트를 이용하여 TB_TRD_DAY 테이블 Outer 테이블로 지정, USE_NL 힌트를 이용하여 TB_EXPORTER 테이블을 Inner 테이블로 지정하고 중첩 루프 조인 처리가 되도록 유도

  • INDEX 힌트를 이용하여 TB_DRD_DAY_PK 인덱스를 사용

  • TB_TRD_DAY 테이블을 기준으로 LEFT 아우터 조인 처리

  • TB_EXPORTER 테이블은 Outer 테이블로 설정

• SQL 문제점

  • Outer 테이블인 TB_EXPORTER 테이블 내에 EXPORTER_NM 컬럼만 SELECT절에 있다.

  • 이런 경우 TB_EXPORTER 테이블을 반드시 아우터 조인할 필요가 없으므로 스칼라 서브쿼리로 변환하면 성능 향상을 기대할 수 있다.

• 실행 계획

  • TB_TRD_DAY_PK 인덱스를 인덱스 풀 스캔(TABLE ACCESS FULL)

  • 위 작업을 통해 나온 ROWID를 이용하여 TB_TRD_DAY 테이블을 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID)

  • TB_EXPORTER_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • 위 작업의 ROWID를 이용하여 TB_EXPORTER 테이블을 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID)

  • 테이블 랜덤 액세스한 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER)

  • SORT와 GROUP BY 연산을 수행

  • SELECT 연산을 수행

스칼라 서브쿼리 방식으로 변환 전 쿼리

튜닝 후

SELECT X.EXPORTER_NO,
       (SELECT B.EXPORTER_NM
        FROM TB_EXPORTER B
        WHERE B.EXPORTER_NO = X.EXPORTER_NO),
       TOT_CNT
FROM (SELECT A.EXPORTER_NO,
             SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNT
      FROM TB_TRD_DAY A
      WHERE A.TRD_DT BETWEEN '20100101' AND '20151231'
      GROUP BY A.EXPORTER_NO
      ORDER BY A.EXPORTER_NO
     ) X;
-- [2021-07-29 18:17:43] 99,704 rows retrieved starting from 1 in 4 s 313 ms (execution: 392 ms, fetching: 3 s 921 ms)

• SQL 분석

  • TB_TRD_DAY 테이블을 인라인 뷰를 이용하여 원하는 데이터를 조회

  • 스칼라 서브쿼리를 이용하여 TB_EXPORTER 테이블을 스캔하여 EXPORTER_NM을 조회, 스칼라 서브쿼리의 캐싱 효과로 인하여 성능 향상

• 실행 계획

  • TB_TRD_DAY 테이블을 테이블 풀 스캔(옵티마이저의 판단)

  • SORT와 GROUP BY 연산을 수행

  • 해당 결과를 인라인 뷰(View)로 생성

  • TB_EXPORTER_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN)

  • EXPORTER_NM 컬럼의 값을 가져오기 위해 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) 수행

  • SELECT 연산 수행

스칼라 서브쿼리 방식으로 변환하여 튜닝