테이블 풀 스캔 튜닝

인덱스 스캔

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

선택도

• 인덱스 스캔이 테이블 풀 스캔에 비해 반드시 유리하진 않는다.

• 선택도가 낮은 컬럼(SEX)을 인덱스로 생성하여 인덱스 스캔을 수행하는 경우, 테이블 전체 행들의 50%에서 인덱스 스캔을 통한 테이블 랜덤 액세스가 발생하게 된다.

• 일반적인 인덱스 스캔은 싱글 블록 I/O 읽기 인데, 테이블 풀 스캔은 멀티 블록 I/O 읽기이다.

• 두 가지 이유로 SEX라는 컬럼을 조건절에 조건을 주어 스캔할 경우 인덱스 스캔보다 테이블 풀 스캔이 더 유리하다.

선택도(Selectivity) 구하는 공식

선택도(%) = ( 1 / Distinct Value ) * 100

인덱스 손익 분기점

• 인덱스 손익 분기점은 인덱스 스캔보다 테이블 풀 스캔이 유리한 선택도의 수치를 의미한다.

  • 일반적으로 선택도가 15%보다 크다면 테이블 풀 스캔이 유리하고, 그 이하라면 인덱스 스캔이 더 유리하다.

테이블 풀 스캔 튜닝

• 테이블 풀 스캔 튜닝은 선택도가 높은 컬럼의 인덱스를 사용하여 인덱스 스캔을 하는 SQL문을 강제로 테이블 풀 스캔으로 처리하도록 하는 기법

• 즉, 손익 분기점을 넘어 인덱스 스캔하기에는 비효율적인 SQL을 강제로 테이블 풀 스캔을 하도록 처리하는 것이다.

실습

테이블 풀 스캔을 유도하여 비효율적인 인덱스 스캔 예방하기

• 테이블 생성

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
);

• 데이터 입력

CREATE TABLE DUAL_1000
(
    DUMMY CHAR(1)
);

INSERT INTO DUAL_1000
SELECT DUMMY
FROM DUAL CONNECT BY LEVEL <= 1000;

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'), -- SALE_GB
       LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 100000))), 10, '0'),
       SYSDATE,
       'DBMSEXPERT',
       NULL,
       NULL
FROM DUAL_1000,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10000);

COMMIT;

• 기본키 생성

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

• 선택도 확인

SELECT ROUND(1 / COUNT(DISTINCT SALE_GB) * 100, 2) 선택도
FROM TB_ORD;

• 인덱스 생성

CREATE INDEX TB_ORD_IDX01 ON TB_ORD (SALE_GB);

• 통계정보 생성

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

튜닝 전 상황

• 튜닝 전 SQL

SELECT /*+ INDEX(TB_ORD TB_ORD_IDX01) */
    *
FROM TB_ORD
WHERE SALE_GB IN ('01', '02');

• SQL 분석

  • INDEX 힌트를 사용하여 TB_ORD_IDX01 인덱스 스캔을 하도록 유도한다.

• SQL 문제점

  • SALE_GB 컬럼은 33.3%로 선택도가 높은 컬럼임에도 인덱스 스캔을 하였다.

  • 테이블 풀 스캔은 테이블 랜덤 액세스를 하지 않으므로 이런 경우에는 오히려 테이블 풀 스캔이 더 성능이 좋다.

  • 인덱스 스캔 시 싱글 블록 I/O 읽기를 하게 되면 테이블 랜덤 액세스를 하므로 성능 부하가 있는 SQL문이 된다.

  • 이런 경우 인덱스 스캔을 한다고 하더라도 반드시 효율적으로 SQL문이라고 볼 수 없다.

• Execute Plan

  • TB_ORD 테이블을 인덱스 범위 스캔(INDEX RANGE SCAN) 한다.

  • TB_ORD_IDX01 인덱스 스캔 후 나온 ROWID를 이용하여 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID)를 수행한다.

  • IN 조건 안에 있는 SALE_GB 컬럼의 값을 기준으로 ID 3번과 2번을 반복하게 된다.

  • IN 조건을 사용할 경우 오라클은 내부적으로 IN절 안에 있는 값을 기준으로 반복해서 비교하게 된다.

  • 즉, 처음에는 SALE_GB = '01'로 비교하고 다음에는 SALE_GB = '02'로 비교하게 된다.

  • '=' 연산으로 N번 비교하는 것이 특징, 이런 연산을 인 리스트 반복자(INLIST ITERATOR)라고 한다.

튜닝 후 상황

• 튜닝 후 SQL

SELECT /*+ FULL(TB_ORD) */
    *
FROM TB_ORD
WHERE SALE_GB IN ('01', '02');

• SQL 분석

  • TB_ORD 테이블을 풀 스캔하기 위해서 FULL 힌트사용

  • 테이블 전체를 멀티 블록 I/O 읽기 방식으로 읽었으며, 테이블 랜덤 액세스도 발생하지 않아 TB_ORD_IDX01 인덱스를 사용한 인덱스 스캔보다 성능이 빨라졌다.

• Execute Plan

  • TB_ORD 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.

  • SELECT 절의 연산을 수행한다.