INDEX

Intro

• 인덱스

• 인덱스 스캔

  • 인덱스 풀 스캔

  • 인덱스 패스트 풀 스캔

• 테이블 풀 스캔

인덱스(B*tree)

• 인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스할 목적으로 사용하는 오브젝트이다.

• 인덱스를 사용하는 것과 사용하지 않은 것의 비교

  • 테이블은 처음부터 끝까지 모든 레코드를 읽어야 완전한 결과집합을 얻을 수 있다.

  • 인덱스는 키 컬럼 순으로 정렬되어 있기 때문에 특정 위치에서 스캔을 시작해 검색 조건에 일치하지 않는 값을 만나는 순간 멈출 수 있다.

    • 이를 범위 스캔(Range Scan)이라 한다.

인덱스의 구조

• 루트를 포함하는 브랜치 블록에 저장된 엔트리에는 하위 노드 블록을 찾아가기 위한 DBA(Data Block Address) 정보를 갖고 있다.

• 최말단 리프 블록에는 인덱스 키 컬럼과 함께 해당 테이블 레코드를 찾아가기 위한 주소정보를 갖는다.

• 리프 블록은 항상 키 컬럼 순으로 정렬되어 있기 때문에 범위 스캔이 가능하다.

B*tree 인덱스 구조

인덱스 효율의 지표

• 기수성(Cardinality) 가 높을 것

  • 즉, 값이 평균치에서 많이 흩어져있을수록 좋은 인덱스 후보

• 선택률(Selectivity) 이 낮을 것

  • 한 번의 선택으로 레코드가 조금만 선택되는 것이 좋은 후보

  • 대체로 5 ~ 10% 이하가 기준

    • 5% 미만이라면 해당 필드 집합은 인덱스를 작성할 가치가 높다.

    • 선택률이 10%보다 높다면 테이블 풀 스캔을 하는 편이 더 빠를 가능성이 높다.

  • 대규모 테이블에서 5%의 레코드를 선택하고 싶은 경우 인덱스를 사용하는 것이 풀 스캔하는 것보다 적은 I/O를 가져올 것이다.

인덱스 생성 방식

• 한 개(1) 또는 여러 개(N)의 컬럼으로 구성된 인덱스를 생성할 수 있는데, 한 개의 컬럼으로만 구성된 인덱스를 단일 컬럼 인덱스 라 한다.

• 2개 이상의 컬럼으로 구성된 인덱스를 복합 컬럼 인덱스 라 한다.

• 인덱스 마다 정렬되는 순서를 오름차순(ASC) 또는 내림차순(DESC) 로 설정할 수 있다.

정리

• 인덱스와 테이블은 각각의 객체로 논리적 / 물리적으로 완전하게 분리되어 있다.

• 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 갖고 있다.

• 인덱스 스캔이 성공하면 데이터의 주소 값을 이용하여 테이블 액세스를 하게 되는데, 이러한 연산을 테이블 랜덤 액세스(Table Random Access) 라 한다.

• 테이블 랜덤 액세스가 많아지면 시스템에 많은 부하를 주게 된다.

  • 테이블 랜덤 액세스를 줄이는 것이 인덱스 스캔 튜닝의 핵심이 된다.

인덱스 스캔

• DBMS는 디스크 소트와 테이블 풀 스캔을 회피하기 위해서 인덱스를 이용한다.

• 디스크 소트

  • 정렬 작업을 메모리 내에서 완료하지 못할 정도로 용량이 커서 디스크 공간까지 활용한 정렬 연산을 의미한다.

• 테이블 풀 스캔

  • 테이블 스페이스에 저장된 테이블을 처음부터 끝까지 전체 검색하는 것을 뜻한다.

인덱스 스캔 튜닝 전

• 조건을 걸고도 테이블 풀 스캔으로 검색하는 경우를 확인

SELECT /*+ FULL(A) */
    COUNT(*)
FROM TB_CUST A
WHERE A.CUST_NM LIKE 'AB%'
  AND EXISTS
    (
        SELECT '1'
        FROM TB_ORD C
        WHERE C.CUST_ID = A.CUST_ID
          AND C.PRDT_CD LIKE 'AB%'
    );

# [2021-07-23 15:07:03] 1 row retrieved starting from 1 in 2 s 211 ms (execution: 2 s 159 ms, fetching: 52 ms)

• 쿼리 분석

  • 특정 고객의 주문 정보를 확인하기 위한 쿼리

  • 고객 명에 대한 조건(AB로 시작하는 행 검색)

  • 상품 코드에 대한 조건(AB로 시작하는 행 검색)

  • 고객 테이블에 대한 검색 방식을 FULL 힌트를 이용하여 테이블 풀 스캔을 유도

• 쿼리의 문제점

  • CUST_NM 컬럼, CUST_ID 컬럼 두 컬럼 모두 레코드를 선택하기 위한 변별력있는 컬럼임에도 인덱스를 생성하지 않은 문제

  • OLTP(Online Transaction Processing)환경에서 빈번하게 사용하는 SQL문이라면 반복적인 테이블 풀 스캔은 전체 시스템에 큰 부하를 주게 된다.

• 실행 계획

  • 고객 테이블을 테이블 풀 스캔 검색

  • 주문 테이블을 테이블 풀 스캔 검색

  • 고객 테이블과 주문 테이블을 옵티마이저가 해시 세미 조인(Hash Semi Join) 방식을 채택

  • 집계 함수 연산

  • 조회

실행 계획

해시 세미 조인 용어 미리 보기

• 해시 조인

  • 두 개의 테이블을 조인 하는 경우 작은 집합을 빠르게 읽어서 해시 테이블을 생성, 해시 영역에 저장

  • 큰 테이블을 순차적으로 읽으면서 해시 함수에 입력 값을 주어 해시 영역에 있는 해시 테이블 내에 해당 값이 존재하면 조인 집합에 저장하여 원하는 조인 결과를 도출하는 방식

• 해시 세미 조인

  • EXISTS를 사용하는 서브쿼리의 형태로 나타내며 이러한 경우 서브 쿼리에 인덱스가 존재하지 않는다면 상당히 비효율적이다.

  • 이러한 서브 쿼리에 인덱스가 없는 경우 Semi join이 일어나도록 유도한다면 성능 향상을 도모할 수 있다.

• 일반적으로 대용량 테이블의 조인 연산에는 해시 조인 방식이 중첩 루프 조인이나 소트 머지 조인 방식보다 훨씬 효율적이다.

• 하지만 해시 조인은 대용량 테이블 조인 시에 메모리가 많이 필요하기 때문에 메모리 영역에 대한 관리가 필요할 수도 있다.

  • 여기서 말하는 메모리 영역이란 작은 집합에 대한 해시 영역 메모리

인덱스 스캔 튜닝 후

• 인덱스 스캔을 위한 준비

  • 레코드의 필터를 위한 조건 컬럼에 인덱스를 생성

  • 통계 정보 생성

  • 인덱스 스캔

인덱스 생성

• 고객정보 테이블에 고객명에 대한 단일 컬럼 인덱스를 생성

• 주문정보 테이블에 고객 아이디와 상품 코드에 대한 복합 인덱스를 생성

CREATE INDEX TB_CUST_IDX01 ON TB_CUST (CUST_NM);
CREATE INDEX TB_ORD_IDX01 ON TB_ORD (CUST_ID, PRDT_CD);

통계 정보 생성

• 신규로 생성된 인덱스에 대한 통계정보를 생성

ANALYZE
INDEX TB_CUST_IDX01 COMPUTE STATISTICS;
ANALYZE
INDEX TB_ORD_IDX01 COMPUTE STATISTICS;

튜닝 후 SQL문

SELECT /*+ INDEX(A TB_CUST_IDX01) */
    COUNT(*)
FROM TB_CUST A
WHERE A.CUST_NM LIKE 'AB%'
  AND EXISTS
    (
        SELECT /*+ INDEX(C TB_ORD_IDX01) NL_SJ */
            '1'
        FROM TB_ORD C
        WHERE C.CUST_ID = A.CUST_ID
          AND C.PRDT_CD LIKE 'AB%'
    );

# [2021-07-23 15:37:37] 1 row retrieved starting from 1 in 199 ms (execution: 147 ms, fetching: 52 ms)

SQL 분석

• 고객 테이블의 TB_CUST_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 합니다.

• 고객 테이블의 TB_CUST_IDX01 인덱스는 고객명으로 이루어진 인덱스

  • 해당 인덱스 스캔 후 CUST_ID를 가져오기 위해 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) 합니다.

  • 고객 테이블의 기본키(PK)가 CUST_ID긴 하지만, 여기서는 CUST_NM 컬럼으로 이루어진 인덱스를 스캔하였기 때문에 테이블 랜덤 액세스는 불가피하다.

  • TB_CUST_IDX01 인덱스의 구성을 CUST_NM + CUST_ID 컬럼으로 한다면 해당 테이블 랜덤 액세스 부하를 줄일 수 있다.

• 고객 테이블의 CUST_ID 컬럼을 바탕으로 주문 테이블의 TB_ORD_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 을 하였다.

  • TB_ORD_IDX01 인덱스는 CUST_ID + PRDT_CD로 구성되어서 별도로 테이블 랜덤 액세스하지 않았다.

• 고객 테이블을 기준으로 주문 테이블과 중첩 세미 조인(NESTED LOOPS SEMI) 를 한다.

  • 고객 테이블을 기준으로 주문 테이블과의 조인 조건이 성립되면 해당 조인 대상 행에 대해 더는 스캔하지 않고 멈추게 된다.

• 집계 함수 연산인 COUNT 연산을 수행한다.

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

실행 계획

인덱스 범위 스캔

• 인덱스 범위 스캔이란 수직적 탐색을 통해 인덱스 리프 블록의 특정 위치로 이동한 후 지정된 범위를 스캔하는 방식

• 인덱스의 스캔 방식 중 가장 일반적인 방식

중첩 루프 세미 조인(Nested Loop Semi Join)

• NL_SJ 힌트를 사용하여 중첩 루프 세미 조인을 유도

• 조건 만족 시 해당 조인 대상 행은 더 이상 스캔을 수행하지 않고 다음 행으로 넘어가는 조인 방식

인덱스 스캔 튜닝 추가

인덱스 삭제 및 재생성

• 테이블 랜덤 액세스를 없애고 인덱스 스캔만으로 데이터를 조회하기 위한 튜닝

DROP INDEX TB_CUST_IDX01;
CREATE INDEX TB_CUST_IDX01 ON TB_CUST (CUST_NM, CUST_ID);
CREATE INDEX TB_ORD_IDX01 ON TB_ORD (CUST_ID, PRDT_CD);

수정된 인덱스에 대한 통계 정보 생성

ANALYZE
INDEX TB_CUST_IDX01 COMPUTE STATISTICS;
ANALYZE
INDEX TB_ORD_IDX01 COMPUTE STATISTICS;

• SQL 분석

  • 고객 테이블의 TB_CUST_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 을 한다.

  • 주문 테이블의 TB_ORD_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 을 한다.

    • TB_CUST_IDX01 인덱스를 기준으로 TB_ORD_IDX01 인덱스와 중첩 루프 세미 조인(NESTED LOOPS SEMI) 한다.

  • 집합 함수 연산인 COUNT 연산을 수행한다.

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

실행 계획

인덱스 구성 컬럼을 추가한 인덱스 스캔 튜닝

• 복합 컬럼 인덱스를 통한 테이블 랜덤 액세스 제거

주문 테이블 생성

• 주문 정보 500만 건 등록

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 INDEX TB_ORD_IDX01 ON TB_ORD (ORD_DT, ORD_NM, ORD_AMT);

통계정보 생성

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

복합 컬럼 인덱스 튜닝 전 SQL

SELECT ORD_DT,
       SALE_GB,
       PAY_GB,
       COUNT(*)               AS 주문건수,
       SUM(ORD_AMT)           AS 총주문금액,
       ROUND(AVG(ORD_AMT), 2) AS 평균주문금액
FROM TB_ORD
WHERE ORD_DT BETWEEN '20210101' AND '20211231'
  AND ORD_NM LIKE 'A%'
  AND ORD_AMT >= 1000
GROUP BY ORD_DT, SALE_GB, PAY_GB
ORDER BY ORD_DT, SALE_GB, PAY_GB;

# [2021-07-23 23:56:53] 8,331 rows retrieved starting from 1 in 1 s 981 ms (execution: 1 s 263 ms, fetching: 718 ms)

• SQL 분석

  • 주문일자 컬럼을 조건으로 2021년의 주문 건을 검색

  • ORD_DT(주문일자) + ORD_NM(주문명) + ORD_AMT(주문금액) 로 구성된 TB_ORD_IDX01 인덱스를 이용

• SQL의 문제점

  • 주문 테이블의 TB_ORD_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 을 유도

  • SELECT 절에 인덱스 컬럼이 아닌 다른 컬럼을 조회하고 있어 인덱스 스캔에 이은 테이블 랜덤 액세스 부하를 발생

  • 넓은 범위를 주로 조회하고 결과 건수가 많아지는 경우 DBMS에 큰 부하를 주게 된다.

실행 계획

복합 컬럼 인덱스 튜닝 후 SQL

• 인덱스 추가 및 통계 정보 생성

CREATE INDEX TB_ORD_IDX02 ON TB_ORD (ORD_DT, ORD_NM, ORD_AMT, SALE_GB, PAY_GB);
# 2021-07-24 00:01:46] completed in 35 s 732 ms
ANALYZE
INDEX TB_ORD_IDX02 COMPUTE STATISTICS;

SELECT /*+ INDEX(TB_ORD TB_ORD_IDX02) */
    ORD_DT,
    SALE_GB,
    PAY_GB,
    COUNT(*)               AS 주문건수,
    SUM(ORD_AMT)           AS 총주문금액,
    ROUND(AVG(ORD_AMT), 2) AS 평균주문금액
FROM TB_ORD
WHERE ORD_DT BETWEEN '20210101' AND '20211231'
  AND ORD_NM LIKE 'A%'
  AND ORD_AMT >= 1000
GROUP BY ORD_DT, SALE_GB, PAY_GB
ORDER BY ORD_DT, SALE_GB, PAY_GB;

# [2021-07-24 00:02:44] 8,331 rows retrieved starting from 1 in 423 ms (execution: 71 ms, fetching: 352 ms)

• SQL 분석

  • 주문 테이블의 TB_ORD_IDX02 인덱스를 사용하도록 INDEX 힌트를 사용

  • 해당 인덱스는 판매구분(SALE_GB) 컬럼과 결제구분(PAY_GB) 컬럼이 존재하기 때문에 테이블 랜덤 액세스를 생략할 수 있다.

  • 테이블 랜덤 액세스가 사라졌다는 것은 인덱스 구성 컬럼만으로 SQL문의 요청 내용을 검색한 것을 뜻한다.

• 실행 계획

  • 주문 테이블의 TB_ORD_IDX02 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 을 한다.

  • 집계 함수 연산인 ORDER BY와 GROUP BY 연산을 수행한다.

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

실행 계획

인덱스 풀 스캔 튜닝

인덱스 풀 스캔

인덱스 풀 스캔

• 인덱스 스캔 후 인덱스 구성 컬럼만으로 결과 집합을 도출할 수 없는 경우 항상 테이블 랜덤 액세스가 일어나게 된다.

• 인덱스 풀 스캔은 인덱스라는 객체만을 풀 스캔하여 원하는 데이터를 가져오는 것

• 인덱스 풀 스캔의 종류

  • 인덱스 풀 스캔

  • 인덱스 패스트 풀 스캔

• 데이터를 가져오는 방식

  • 데이터를 가져올 때(Fetching) 항상 블록(block) 단위로 가져오게 된다.

  • 단 한건의 행만 가져온다고 해도 해당 행이 속해 있는 블록 전체를 가져오게 된다.

    (오라클 한 블록당 8KB의 디스크 공간을 차지한다.)

  • 이를 블록 단위 I/O라 한다.

블록 단위 I/O의 2가지 유형

• 싱글 블록 I/O 읽기(Single Block I/O Read)

  • 인덱스 스캔 시, 한 번의 읽기를 통해 한 개의 블록을 읽는 방식

  • 사용자가 요청한 데이터 집합을 출력하기 위해 읽어야 하는 블록이 64개라면 64번의 I/O 읽기가 발생한다.

  • 한 번 읽은 블록들은 데이터 버퍼 캐시의 맨 앞쪽에 위치하여 비교적 긴 시간 동안 데이터 버퍼 캐시에 해당 결과가 남게 된다.

  • 그래서 일정시간 동안은 동일한 SQL문 호출 시 빠른 속도로 사용자에게 데이터를 전달한다.

  • 자주 수행되는 SQL문에 유리하다.

• 멀티 블록 I/O 읽기(Multi Block I/O Read)

  • 테이블 풀 스캔 시, 한 번의 읽기를 통해 여러 개의 블록을 읽는 방식

  • 한번의 읽기로 읽는 블록의 개수가 64개라면 단 한 번의 I/O 읽기로 64개 블록을 모두 읽게 된다.

  • 효율적인 인덱스 스캔이 아니라면 오히려 테이블 풀 스캔이 성능이 좋을 수 있는 이유가 여기에 있다.

  • 한 번 읽은 블록들은 데이터 버퍼 캐시의 맨 뒷쪽에 위치하여 잠시 후면 데이터 버퍼 캐시에서 사라져 동일한 SQL문 호출 시 같은 연산을 반복한다.

  • 빈번하게 호출되는 SQL문에는 부적합한 방식이다.

• 멀티 블록 I/O 읽기 방식을 인덱스 스캔에서도 사용할 수 있는 방법

  • 인덱스 패스트 풀 스캔(Index Fast Full Scan)

  • 인덱스는 정렬된 상태를 유지하는 객체

  • 인덱스 패스트 풀 스캔을 통한 결과 집합은 인덱스에 기정렬되어 있는 데이터의 정렬을 보장하지 않는다.

  • 기정렬된 데이터를 검색하는 부분 범위 처리 방식등에는 부적합하며 통계나 집계를 도출할 경우에는 매우 유용하게 사용할 수 있다.

비교대상	인덱스 풀 스캔	인덱스 패스트 풀 스캔
I/O발생 횟수	많음	적음
정렬된 순서	보장	보장하지 않음
데이터 버퍼 캐싱	데이터 버퍼 앞쪽에 위치 (빈번한 SQL 호출에 유리)	데이터 버퍼 뒤쪽에 위치 (빈번한 SQL 호출에 부적합)
스캔 속도	느림	빠름
용도	부분 범위 처리	집계성 결과

인덱스 풀 스캔 튜닝

• 인덱스 풀 스캔 튜닝은 인덱스를 풀 스캔하여 원하는 결과를 빠르게 가져오는 튜닝 기법

• 적용가능한 상황

  • 인덱스 구성 컬럼만으로 결과를 도출할 수 있는 경우

  • 인덱스 구성 컬럼을 추가하여 결과를 도출할 수 있는 경우 인덱스 컬럼 추가

  • 부분 범위 처리 시 인덱스만 부분적으로 읽어서 성능 극대화(인덱스 풀 스캔인 경우에만)

멀티 블록 I/O 읽기 사용 시 주의점

• 싱글 블록 I/O 읽기로 읽은 블록은 데이터 버퍼 캐시의 LRU(Least Recently Used) 리스트 상 MRU(Most Recently Used)쪽 End 위치에 존재한다.

  • 즉, 데이터 버퍼 캐시의 맨 앞쪽에 위치하므로 데이터 버퍼 캐시에 머무르는 시간이 길다.

  • 이러한 방식은 실시간으로 반복적인 요청이 많을 경우 유리하다.

• 멀티 블록 I/O 읽기로 읽은 블록은 데이터 버퍼 캐시의 LRU 리스트상 LRU End에 위치하게 된다.

  • 즉, 데이터 버퍼 캐시의 맨 뒷쪽에 위치하므로 얼마 지나지 않아 데이터 버퍼 캐시에서 밀려나게 된다.

• 따라서 멀티 블록 I/O 읽기 방식은 실시간으로 반복적인 요청이 있는 경우, 싱글 블록 I/O 읽기보다 훨씬 더 많은 부하를 주게 된다.

• 정리

  • 멀티 블록 I/O 읽기 기반인 인덱스 패스트 풀 스캔은 집계 또는 통계성 쿼리에 적합하여 OLTP 환경에는 부적합하다.

인덱스 패스트 풀 스캔을 활용한 집계 결과 검색

테이블 생성

• 1000만건 등록

CREATE TABLE TB_SUPP
(
    SUPP_NO VARCHAR2 (10), -- 공급업체번호
    SUPP_NM VARCHAR2 (50), -- 공급업체명
    INST_DT VARCHAR2 (8),  -- 가입일자
    INST_TM VARCHAR2 (6),  -- 가입시간
    INST_ID VARCHAR2 (50)  -- 입력자ID
);

• 인덱스 생성

CREATE INDEX TB_SUPP_IDX01 ON TB_SUPP (INST_DT);

• 통계 정보 생성

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

인덱스 패스트 풀 스캔 튜닝 전 SQL

SELECT /*+ INDEX(TB_SUPP TB_SUPP_IDX01) */
    SUBSTR(INST_DT, 1, 6),
    COUNT(*)
FROM TB_SUPP
WHERE INST_DT BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD') AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND SUPP_NM LIKE '%A%'
GROUP BY SUBSTR(INST_DT, 1, 6);

# [2021-07-24 00:23:15] 13 rows retrieved starting from 1 in 6 s 314 ms (execution: 6 s 294 ms, fetching: 20 ms)

• SQL 분석

  • INDEX 힌트를 사용하여 TB_SUPP_IDX01 인덱스 스캔을 한다.

  • 가입일자를 조건으로 최근 1년간 가입된 공급업체를 검색한다.

  • 공급업체 컬럼에 A라는 문자가 포함된 모든 공급업체를 검색한다.

• SQL의 문제점

  • SQL은 인덱스 범위 스캔을 통한 테이블 랜덤 엑세스 부하가 존재한다.

• 실행 계획

  • TB_SUPP_IDX01 인덱스를 인덱스 범위 스캔(INDEX RANGE SCAN) 한다.

  • TB_SUPP_IDX01 인덱스의 리프 블록에 있는 데이터의 주소 값을 이용하여 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) 를 수행한다.

  • 해당 테이블에서 공급업체 명 컬럼 조건에 대해서 필터링한다.

  • 집계 함수 연산인 GROUP BY 연산을 수행한다.

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

실행 계획

인덱스 패스트 풀 스캔 튜닝 후 SQL

• 인덱스 추가

CREATE INDEX TB_SUPP_X02 ON TB_SUPP (INST_DT, SUPP_NM);

• 통계정보 생성

ANALYZE
INDEX TB_SUPP_X02 COMPUTE STATISTICS;

SELECT /*+ INDEX_FFS(TB_SUPP TB_SUPP_IDX02) */
    SUBSTR(INST_DT, 1, 6),
    COUNT(*)
FROM TB_SUPP
WHERE INST_DT
    BETWEEN TO_CHAR(SYSDATE - 365, 'YYYYMMDD')
    AND TO_CHAR(SYSDATE, 'YYYYMMDD')
  AND INSTR(SUPP_NM, 'A') > 0
GROUP BY SUBSTR(INST_DT, 1, 6);

# [2021-07-24 00:21:17] 13 rows retrieved starting from 1 in 6 s 301 ms (execution: 6 s 286 ms, fetching: 15 ms)

• SQL 분석

  • INDEX_FFS 힌트를 이용하여 TB_SUPP_IDX02 인덱스를 인덱스 패스트 풀 스캔합니다.

  • 즉, TB_SUPP_IDX02 인덱스만 멀티 블록 I/O 읽기로 빠르게 스캔한다.

  • 결국, DBMS 성능 부하의 주범인 테이블 랜덤 액세스가 사라졌다.

• 실행 계획

  • TB_ORD_IDX02 인덱스를 이용하여 인덱스 패스트 풀 스캔(INDEX FAST FULL SCAN) 을 합니다.

  • TB_ORD_IDX02 인덱스에서 SUPP_NM 컬럼 조건에 대한 조건 값을 필터링합니다.

  • GROUP BY 연산을 수행한다.

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

실행 계획

테이블 풀 스캔 튜닝

테이블 풀 스캔 유도로 비효율적인 인덱스 스캔 예방

테이블 생성

• 주문 정보 테이블에 1000만 건에 대한 데이터를 입력

• SALE_GB 는 '00', '01', '02' 3개 중 하나의 값으로 구성되어 선택도는 33.3%가 된다.

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

• 선택도 계산 방법

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

# [2021-07-24 01:28:50] 6,665,150 rows retrieved starting from 1 in 7 m 41 s 680 ms (execution: 81 ms, fetching: 7 m 41 s 599 ms)

• SQL 분석

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

• SQL 문제점

  • SALE_GB 컬럼은 33.33%로 선택도가 높은 컬럼임에도 인덱스 스캔을 유도

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

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

• 실행계획

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

# [2021-07-24 01:40:31] 6,665,150 rows retrieved starting from 1 in 8 m 7 s 170 ms (execution: 144 ms, fetching: 8 m 7 s 26 ms)

• SQL 분석

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

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

• 실행계획

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

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

실행계획

정리

• B+-tree 알고리즘에 대한 기본적인 정보

  • 구성 및 특성

• 테이블 랜덤 액세스

  • 데이터 집합의 조회

• 인덱스 스캔

  • 데이터의 부분 집합을 효과적으로 조회하기 위한 기법

  • 디스크 소트, 테이블 풀 스캔을 회피하기 위한 기법

  • 인덱스 풀 스캔

  • 인덱스 패스트 스캔

• 테이블 풀 스캔

  • 멀티 블록 I/O 읽기

  • 인덱스의 손익 분기점

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

  • 선택도(Selectivity)가 높은 컬럼의 인덱스를 사용

다루지 못한 내용

• B*tree 인덱스

• 테이블 랜덤 액세스

• 인덱스 스캔 방식

  • Index Range Scan

  • Index Unique Scan

  • Index Skip Scan

• 인덱스 스캔의 효율

  • Sequential 액세스의 선택도를 높이는 방법

  • Random 액세스의 발생량을 최소화 하는 방법

• 인덱스의 성능을 결정하는 요인

  • 클러스터링 팩터

• 인덱스로 성능 향상을 기대하기 어려운 경우

  • 레코드에 대한 압축조건이 없는 경우(WHERE 구가 없는 경우)

  • 레코드를 제대로 압축하지 못하는 경우(WHERE 구의 검색 조건이 테이블 레코드 절반 이상을 선택하는 경우)

    • 입력 매개변수에 따라 선택률이 변동하는 경우

  • 인덱스를 사용하지 않는 검색 조건인 경우

    • LIKE 연산자를 통한 조건 검색인 경우

    • 색인 필드로 연산하는 경우(필드에 연산 식을 작성하는 경우)

    • IS NULL을 사용하는 경우

    • 부정형을 사용하는 경우

• MySQL