중첩 루프 조인 튜닝 실습

중첩 루프 조인 튜닝의 실습

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

Intro

• 기반 데이터

  • 고객 정보 10만건 생성

  • 주문 정보가 100만건 생성

• 주문일자 + 주문이름에 대한 복합 인덱스를 생성

• 쿼리 테스트

  • LEADING 힌트로 고객 테이블을 Driving 테이블로 설정

  • USE_NL 힌트로 주문정보 테이블을 Driven 테이블로 설정

  • 조인 조건에 대한 인덱스가 없는 경우에 대한 테이블 풀 스캔 동작 테스트

• 튜닝 쿼리 테스트

  • Outer 테이블에 CUST_NM 컬럼으로 구성된 인덱스 추가

    • LEADING 힌트를 통해 고객 정보 테이블을 Driving 테이블로 설정

    • INDEX 힌트를 통해 고객이름에 대한 인덱스 스캔하도록 설정

  • Inner 테이블에 CUST_ID + ORD_DT 컬럼으로 구성된 복합 인덱스 생성

    • USE_NL 힌트를 통해 중첩 루프 조인을 유도, 주문정보 테이블을 Inner 테이블로 지정

    • INDEX 힌트를 통해 고객 ID + 주문일자 인덱스를 스캔하도록 설정

  • Outer 테이블인 고객정보 테이블의 스캔 건수만큼 Inner 테이블의 주문정보 테이블을 스캔 시 효율적인 인덱스 스캔을 하도록 유도하여 성능 향상을 확인

실습 테이블 생성 및 데이터 등록

CREATE TABLE TB_CUST
(
    CUST_ID  VARCHAR2(10), -- 고객ID
    CUST_NM  VARCHAR2(50), -- 고객명
    BIRTH_DT VARCHAR2(8),  -- 생일
    SEX      VARCHAR2(2),  -- 성별
    PHONE_NO VARCHAR2(11), -- 폰번호
    JOIN_DT  VARCHAR2(8),  -- 가입일자
    INST_DTM DATE,         -- 입력일시
    INST_ID  VARCHAR2(50), -- 입력자ID
    UPDT_DTM DATE,         -- 수정일시
    UPDT_ID  VARCHAR2(50)  -- 수정자ID
);

CREATE TABLE TB_ORD
(
    ORD_NO   VARCHAR2(10),  -- 주문번호
    ORD_DT   VARCHAR2(8),   -- 주문일자
    ORD_NM   VARCHAR2(150), -- 주문이름
    ORD_AMT  NUMBER(15),    -- 주문금액
    PRDT_CD  VARCHAR2(6),   -- 상품코드
    SALE_GB  VARCHAR2(2),   -- 판매구분
    PAY_GB   VARCHAR2(2),   -- 결제구분
    CUST_ID  VARCHAR2(10),  -- 고객ID
    INST_DTM DATE,          -- 입력시간
    INST_ID  VARCHAR2(50),  -- 입력자ID
    UPDT_DTM DATE,          -- 수정시간
    UPDT_ID  VARCHAR2(5)    -- 수정자ID
);

INSERT INTO TB_CUST
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       DBMS_RANDOM.STRING('U', 50),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(3650, 36500)), 'YYYYMMDD'),
       LPAD(MOD(ROWNUM, 2), 2, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(3650, 36500))), 11, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365 * 3)), 'YYYYMMDD'),
       SYSDATE,
       'DBMSEXPERT',
       NULL,
       NULL
FROM DUAL
CONNECT BY LEVEL <= 100000;

-- [2021-07-13 16:14:10] 100,000 rows affected in 14 s 249 ms

COMMIT;

CREATE TABLE DUAL_10
(
    DUMMY VARCHAR2(1)
)
;
INSERT INTO DUAL_10
SELECT DUMMY
FROM DUAL
CONNECT BY LEVEL <= 10;

COMMIT;

ALTER TABLE TB_ORD
    NOLOGGING;

INSERT /*+ APPEND */ INTO TB_ORD --APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),
       DBMS_RANDOM.STRING('U', 150),
       TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
       LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 50)), 6, '0'),
       LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 3)), 2, '0'),
       LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
       CUST_ID,
       SYSDATE,
       'DBMSEXPERT',
       NULL,
       NULL
FROM TB_CUST,
     DUAL_10;

-- [2021-07-13 16:24:15] 1,000,000 rows affected in 4 m 18 s 672 ms

COMMIT;

ALTER TABLE TB_ORD
    ADD CONSTRAINT TB_ORD_PK
        PRIMARY KEY (ORD_NO);

ALTER TABLE TB_CUST
    ADD CONSTRAINT TB_CUST_PK
        PRIMARY KEY (CUST_ID);

ALTER TABLE TB_ORD
    ADD CONSTRAINT TB_ORD_FK
        FOREIGN KEY (CUST_ID) REFERENCES TB_CUST (CUST_ID);

• 인덱스 구성

  • 주문 정보 테이블에 '주문일자 + 주문이름'으로 구성된 복합 인덱스를 생성

CREATE INDEX TB_ORD_IDX01 ON TB_ORD (ORD_DT, ORD_NM);

• 통계 정보

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

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

중첩 루프 조인 (테이블 풀 스캔)

SELECT /*+ LEADING(A) USE_NL(B) */
    *
FROM TB_CUST A,
     TB_ORD B
WHERE A.CUST_NM LIKE 'L%'
  AND A.CUST_ID = B.CUST_ID
  AND B.ORD_DT
    BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD')
    AND TO_CHAR(SYSDATE, 'YYYYMMDD');

-- [2021-07-29 15:55:39] 3,678 rows retrieved starting from 1 in 9 m 40 s 31 ms (execution: 15 s 947 ms, fetching: 9 m 24 s 84 ms)

• SQL 분석

  • LEADING 힌트를 사용하여 고객 테이블을 Outer 테이블로 설정

  • USE_NL 힌트를 사용하여 주문정보 테이블과 중첩 루프 조인이 이루어지도록 설정

  • 고객테이블과 주문정보 테이블을 고객아이디 컬럼을 기준으로 '=' 조인

  • 주문정보 테이블은 고객아이디를 선두 컬럼으로 한 인덱스가 존재하지 않으므로 주문정보 테이블은 테이블 풀 스캔으로 처리된다.

• SQL의 문제점

  • 주문정보 테이블에 고객 아이디를 선두 컬럼으로 구성된 인덱스가 존재하지 않는다.

  • 고객 정보 테이블의 결과 집합의 건수만큼 주문정보 테이블에 테이블 풀 스캔을 하게 된다.

• Execute plan

  • TB_CUST 테이블을 테이블 풀 스캔한다.

  • TB_ORD 테이블을 테이블 풀 스캔한다.

  • TB_CUST 테이블과 TB_ORD 테이블을 중첩 루프 조인(NESTED LOOPS)한다.

    • 즉, TB_CUST 결과 집합의 건수만큼 TB_ORD 테이블을 테이블 풀 스캔하게 된다.

  • 조건 절에 조건에 따른 필터링 처리를 한다.

  • SELECT 절의 연산을 수행

튜닝 전 테이블 풀 스캔 실행 계획

중첩 루프 조인 (인덱스 스캔 튜닝)

CREATE INDEX TB_CUST_IDX01 ON TB_CUST (CUST_NM);
CREATE INDEX TB_ORD_IDX02 ON TB_ORD (CUST_ID, ORD_DT);

ANALYZE INDEX TB_CUST_IDX01 COMPUTE STATISTICS;
ANALYZE INDEX TB_ORD_IDX02 COMPUTE STATISTICS;

SELECT/*+ LEADING(A) INDEX(A TB_CUST_IDX01) 
        USE_NL(B) INDEX(B TB_ORD_IDX02)  */
    *
FROM TB_CUST A,
     TB_ORD B
WHERE A.CUST_NM LIKE 'L%'
  AND A.CUST_ID = B.CUST_ID
  AND B.ORD_DT
    BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD')
    AND TO_CHAR(SYSDATE, 'YYYYMMDD');

-- [2021-07-29 15:56:37] 3,678 rows retrieved starting from 1 in 384 ms (execution: 19 ms, fetching: 365 ms)

• SQL 분석

  • LEADING 힌트를 사용하여 고객 테이블을 Outer테이블로 지정

  • INDEX 힌트를 사용하여 고객명에 대한 인덱스를 스캔하도록 지정

  • USE_NL 힌트를 이용하여 중첩 루프 조인을 유도, 주문 테이블을 Inner 테이블로 지정

  • INDEX 힌트를 사용하여 고객아이디 + 주문일자 인덱스를 스캔

  • 고객 테이블의 스캔 건수만큼 Inner 테이블인 주문정보 테이블을 스캔 시 효율적인 인덱스 스캔으로 성능 향상을 예상할 수 있다.

중첩 루프 조인 인덱스 스캔 실행 계획