TIL
  • Contents
  • Book
    • 도메인 주도 설계
      • 1. 동작하는 도메인 모델 만들기
    • 오브젝트
      • 데이터 중심 설계
      • 책임 중심 설계
      • 책임 할당을 위한 GRASP 패턴
      • 메시지와 인터페이스
      • 객체 분해
    • Effective Java
      • Item 7 - 다 쓴 객체 참조를 해제하라
      • Item 7 발표 내용
      • Item 13 - clone 재정의는 주의해서 진행하라
      • Item 13 발표 내용
      • Item 16 - public 클래스에서는 public 필드가 아닌 접근자 메서드를 사용하라
      • Item 16 발표 내용
      • Item 26 - 로 타입은 사용하지 말라
      • Item 28 - 배열보다는 리스트를 사용하라
      • Item 28 발표 내용
      • Item 29 - 이왕이면 제네릭 타입으로 만들라
      • Item 30 - 이왕이면 제네릭 메서드로 만들라
      • Item 31 - 한정적 와일드 카드를 사용해 API 유연성을 높이라
      • Item 35 - ordinal 메서드 대신 인스턴스 필드를 사용하라
      • Item 37 - ordinal 인덱싱 대신 EnumMap을 사용하라
      • Item 37 발표 내용
      • Item 43 - 람다보다는 메서드 참조를 사용하라
      • Item 43 발표 정리
      • Item 56 - 공개된 API 요소에는 항상 문서화 주석을 작성하라
      • Item 56 발표 정리
      • Item 62 - 다른 타입이 적절하다면 문자열 사용을 피하라
      • Item 62 발표 정리
      • Item 73 - 추상화 수준에 맞는 예외를 던지라
      • Item 83 - 지연 초기화는 신중히 사용하라
      • Item 83 발표 내용
      • Item 89 - 인스턴스 수를 통제해야 한다면 readResolve보다는 열거 타입을 사용하라
      • Item 89 발표 내용
    • 개발자를 위한 SQL 튜닝
      • SQL 쿼리 실습을 위한 DB 서버 구축
      • 인덱스 튜닝
      • 인덱스 스캔 튜닝
      • 인덱스 스캔 튜닝 실습
      • 인덱스 패스트 풀 스캔
      • 테이블 풀 스캔 튜닝
      • 조인 튜닝
      • 중첩 루프 조인 튜닝
      • 중첩 루프 조인 튜닝 실습
      • 해시 조인 튜닝
      • 해시 조인 튜닝 실습
      • 세미 조인 튜닝
      • 세미 조인 튜닝 실습
      • 아우터 조인
      • 함수 튜닝
      • 부분 범위 처리 튜닝
      • 파티셔닝 튜닝
      • 파티션 인덱스 튜닝
      • 병렬 처리 튜닝
  • Java
    • Design Pattern
      • Intro
      • Types of Design Patterns
      • Creational
        • Builder Pattern
        • Singleton Pattern
        • Prototype Pattern
        • Factory Pattern
        • Abstract Factory Pattern
      • Structural
        • Adapter Pattern
        • Bridge Pattern
        • Composite Pattern
        • Decorator Pattern
        • Facade Pattern
        • Flyweight Pattern
        • Proxy Pattern
      • Behavioural
        • Chain of Responsibility Pattern
        • Command Pattern
        • Interpreter Pattern
        • Iterator Pattern
        • Mediator Pattern
        • Memento Pattern
        • Observer Pattern
        • State Pattern
        • Strategy Pattern
        • Template Method Pattern
        • Visitor Pattern
    • Java
      • Cracking the Coding Interview
      • TDD, Clean Code with Java 11기
        • 자동차 레이싱
        • 로또
        • 사다리 타기
        • 볼링 게임 점수판
    • 궁금증
      • 자바 8 버전의 인터페이스와 추상클래스
      • 자바의 제네릭은 어떻게 이전 버전과 호환되는 걸까?
      • 스프링 MVC 기본 구조
      • 마샬링과 직렬화
      • 인터뷰 질문 모음
      • Code Coverage
  • Database
    • Database
      • SQL 레벨업
      • DB 스터디
        • DBMS
          • MySQL
        • INDEX
        • Join(Nested Loop, Hash)
        • Join(Semi, Outer)
        • Partial Range Processing
        • Function
        • Partitioning
        • Parallel Processing
  • Network
  • Architecture
    • Issue
      • Git Push Error
      • SonarLint Warning - assertThatExceptionOfType()
  • Infra
  • Spring
    • Spring JPA
      • 1. 데이터 모델링 및 연관관계 설정
      • 2. 최적화 내용
      • 3. Spring-Data-Jpa
      • 4. Query DSL
    • Spring Security
      • Intro
    • Spring Batch
      • 배치용 디비 설치
      • 배치 데이터 분석하기
      • 배치 프로세스 구상하기 및 성능 차이 확인하기
  • Issue
  • Tistory
    • Tistory Blog
  • Design High Performing Architectures
  • Design Resilient Architectures
  • Design Secure Applications And Architectures
  • Design Cost-Optimized Architectures
Powered by GitBook
On this page
  • Intro
  • 부분 범위 처리(Partial Range Processing)
  • 최댓값/최솟값 스캔 튜닝
  • 부분 범위 처리 기법을 이용하여 최댓값/최솟값 검색하기
  • 튜닝 전 상황
  • 튜닝 후 상황
  • 페이징 처리 튜닝
  • 부분 범위 처리 기법을 이용한 페이징 처리
  • 부분 범위 처리 기법을 이용한 페이징 처리 - 튜닝 전
  • 부분 범위 처리 기법을 이용한 페이징 처리 - 튜닝 후

Was this helpful?

  1. Book
  2. 개발자를 위한 SQL 튜닝

부분 범위 처리 튜닝

부분 범위 처리 튜닝

Previous함수 튜닝Next파티셔닝 튜닝

Last updated 3 years ago

Was this helpful?

내용에서 참고한 내용입니다.

Intro

  • 조건에 만족하는 모든 데이터를 한 화면에 보여줄 수 없는 경우가 빈번하다.

  • 그렇기 때문에 모든 데이터를 스캔하게 되면 DBMS에 많은 부하를 주게 된다.

  • 부분 범위 처리(Partial Range Processing)를 통해 조건에 만족하는 데이터 중 특정 건수의 데이터만을 스캔하여 처리하면 부하를 줄일 수 있다.

  • 부분 범위 처리 기법을 이용하여 최소값/최대값을 찾는 튜닝 기법과 페이징 처리를 하는 튜닝 기법에 대해 알아보기

부분 범위 처리(Partial Range Processing)

  • 대용량 테이블을 스캔할 때 가장 먼저 나오는 한 건 또는 한 건이상(N개)의 행만 가져오는 처리 기법이다.

  • 대용량의 테이블을 스캔한다고 해도 부분 범위만 가져온다면 성능은 극단적으로 빨라지게 된다.

부분 범위 처리의 기초

  • WHERE 절에 ROWNUM 조건으로 원하는 건수를 조회한다.

  • 테이블 전체를 스캔하지 않고 원하는 범위의 데이터를 읽으면 곧바로 결과를 출력할 수 있다.

SELECT *
FROM EMP
WHERE ROWNUM <= 3
ORDER BY EMPNO;
  • 위 쿼리는 EMP 테이블을 읽다가 3건을 모두 읽으면 테이블 스캔을 멈추고 그 후 조회된 3건으로 정렬을 하게 된다.

  • ORDER BY 연산이 마지막에 진행되기 때문이다.

  • 사용자의 의도가 테이블 전체 데이터를 EMPNO 기준으로 정렬한 상태에서 3건의 데이터를 뽑는 것이었다면 데이터 정합성이 틀린 SQL문이 된다.

  • 이런 문제를 해결하기 위해서 아래 쿼리로 수정한다.

-- 부분 범위 처리 + 소트 연산 + ROWNUM 조건
SELECT *
FROM (
         SELECT *
         FROM EMP
         ORDER BY EMPNO
     )
WHERE ROWNUM <= 3;
  • 위 쿼리는 인라인 뷰와 WHERE 절에 ROWNUM 조건을 결합하면 EMP 테이블을 EMPNO 기준으로 오름차순 정렬한 값 중에서 3건을 가져오게 된다.

  • SQL문은 인덱스 구성 여부에 따라 부분 범위 처리를 하기도 하고 전체 범위 처리를 하기도 한다.

  • 부분 범위 처리가 되기 위해서는 EMPNO를 선두 컬럼으로 갖는 인덱스가 존재해야 하고, 해당 인덱스의 앞에서부터 3건만 읽고 스캔을 종료할 수 있다.

  • 이는 인덱스가 정렬된 상태를 항상 유지한다는 특성을 이용한 것이다.

부분 범위 처리의 구현

CREATE TABLE TB_CUST
(
    CUST_ID VARCHAR2 (10), -- 고객 아이디
    IN_DTM DATE,           -- 가입일자
    CONSTRAINT TB_CUST_PK PRIMARY KEY (CUST_ID)
)
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       SYSDATE - (TRUNC(DBMS_RANDOM.VALUE(1, 999999)) / 24 / 60 / 60)
FROM DUAL CONNECT BY LEVEL <= 100000;
  • 15년 10월 01 이후 데이터 중 가장 최근에 들어간 100건을 출력하는 SQL

  • TB_CUST 테이블을 풀 스캔한 후 IN_DTM을 기준으로 내림차순 정렬한 값 중에서 100건을 출력하는 쿼리

  • 결과 집합은 사용자가 의도한 대로 나오지만 IN_DTM으로 구성된 인덱스가 없다면 전체 범위 처리를 하게 된다.

CREATE INDEX TB_CUST_IDX01 ON TB_CUST (IN_DTM);
  • IN_DTM으로 구성된 인덱스를 생성

  • 인덱스 생성으로 인해 SQL은 부분 범위 처리를 하게 된다.

  • 즉, 부분 범위 처리가 작동하는 데 가장 필수적인 요소는 인덱스 존재 여부이므로 인덱스 구성에 따라 극적인 성능을 가져올 수 있다.

최댓값/최솟값 스캔 튜닝

최댓값/최솟값 스캔 튜닝

  • 인덱스의 주요 특징은 바로 데이터가 정렬된 상태로 저장되어 있다라는 것이다.

  • 인덱스의 특징과 부분 범위 처리의 원리가 결합되면 최댓값과 최솟값을 가져올 때 극적인 성능 향상을 이루어 낼 수 있다.

  • 이처럼 인덱스와 부분 범위 처리 원리를 결합시켜 최댓값과 최솟값을 가장 빠르게 가져오는 것을 최댓값/최솟값 스캔 튜닝이라 한다.

부분 범위 처리 기법을 이용하여 최댓값/최솟값 검색하기

CREATE TABLE TB_ORD_DAY
(
    ORD_DT VARCHAR2 (8), -- 주문일자
    ORD_NO VARCHAR2 (10) -- 주문번호
);
ALTER TABLE TB_ORD_DAY
    NOLOGGING;
DECLARE
    TYPE ORD_DT_TABLE IS TABLE OF VARCHAR2(8)
        INDEX BY BINARY_INTEGER;

ORD_DT_TAB ORD_DT_TABLE;
i          BINARY_INTEGER := 0;

BEGIN FOR ORD_DT_LIST
        IN
        (
        SELECT TO_CHAR(SYSDATE - ROWNUM, 'YYYYMMDD') ORD_DT
        FROM DUAL
        CONNECT BY LEVEL <= 3650
        ORDER BY 1
        )
        LOOP
            i := i + 1;
ORD_DT_TAB
    (i)
    := ORD_DT_LIST.ORD_DT;
INSERT INTO TB_ORD_DAY
SELECT ORD_DT_TAB(i),
       LPAD(TO_CHAR(ROWNUM), 10, '0')
FROM DUAL CONNECT BY LEVEL <= 10000;
COMMIT;
END LOOP;
END;
/
-- 36_500_000 건 등록
ALTER TABLE TB_ORD_DAY
    ADD CONSTRAINT TB_ORD_DAY_PK
        PRIMARY KEY (ORD_DT, ORD_NO);

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

튜닝 전 상황

SELECT MAX(ORD_NO) MAX_ORD_NO,
       MIN(ORD_NO) MIN_ORD_NO
FROM TB_ORD_DAY
WHERE ORD_DT = TO_CHAR(SYSDATE - 30, 'YYYYMMDD')
  • SQL 분석

    • 현재 일자로부터 30일 전 일자를 기준으로 MAX와 MIN 함수를 이용하여 최댓값과 최솟값 조회

  • SQL 문제점

    • 인덱스 범위 스캔을 하지 않고 인덱스 양 끝의 최댓값과 최솟값만 스캔하면 의도한 대로 결과를 도출할 수 있다.

    • 해당 SQL문은 TB_ORD_DAY_PK 인덱스를 인덱스 범위 스캔하게 된다.

튜닝 후 상황

SELECT *
FROM (
         SELECT ORD_NO MAX_ORD_NO, '1' DUMMY
         FROM (
                  SELECT ORD_NO
                  FROM TB_ORD_DAY
                  WHERE ORD_DT = TO_CHAR(SYSDATE - 30, 'YYYYMMDD')
                  ORDER BY ORD_NO DESC
              )
         WHERE ROWNUM <= 1
     ) A,
     (
         SELECT ORD_NO MIN_ORD_NO, '1' DUMMY
         FROM (
                  SELECT ORD_NO
                  FROM TB_ORD_DAY
                  WHERE ORD_DT = TO_CHAR(SYSDATE - 30, 'YYYYMMDD')
                  ORDER BY ORD_NO ASC
              )
         WHERE ROWNUM <= 1
     ) B
WHERE A.DUMMY = B.DUMMY;
  • SQL 분석

    • WHERE 절에 ORD_DT 조건을 주어져 있다.

    • PK 구성이 ORD_DT + ORD_NO 순으로 되어 있으므로 ORD_DT 조건이 '=' 조건으로 들어오는 순간 ORD_NO를 기준으로 부분 범위 처리가 가능해진다.

    • 최댓값을 가져오기 위해 ORD_NO를 DESC로 정렬한다.

    • ROWNUM 조건을 주어 ORD_NO의 최대값 한 건만 가져오게 한다.

    • WHERE 절에 ORD_DT 조건을 주었다.

    • PK 구성이 'ORD_DT + ORD_NO' 순으로 되어 있으므로 ORD_DT 조건이 '=' 조건으로 들어오는 순간 ORD_NO를 기준으로 부분 범위 처리가 가능해진다.

    • 최솟값을 가져오기 위해 ORD_NO를 ASC로 정렬한다.

    • ROWNUM 조건을 주어 ORD_NO의 최솟값 한 건만 가져오도록 한다.

페이징 처리 튜닝

페이징 처리

  • 일반적인 게시판 또는 정보 조회 화면을 생각해보면 100만건의 데이터가 저장된 테이블이 있다고 가정했을 때, 한 화면 안에서 100만 건의 모든 데이터를 보여줄 수 없고 해당 화면에 나타낼 수 있는 범위만 보여주면 된다.

  • 이러한 경우 부분 범위 처리를 이용한 페이징 처리(Paging Processing) 를 하게 된다.

페이징 처리 튜닝

  • 페이징 처리 튜닝(Paging Processing Tuning) 이란 해당 페이지에서 보여주고 싶은 범위만 인덱스 범위 스캔하여 특정 데이터만 가져오는 일련의 모든 활동을 의미한다.

  • 인덱스를 이용하여 해당 범위의 시작으로 간 후(인덱스 수직 탐색) 원하는 데이터만 가져오는 기법(인덱스 범위 스캔)이다.

  • 부분 범위 처리를 이용한 페이징 처리가 되지 않고 모든 범위를 스캔한 후 특정 데이터만 가져오게 된다면 시스템은 걷잡을 수 없이 큰 부하를 일으키게 된다.

  • 이러한 이유로 페이징 처리 튜닝은 실무에서 매우 중요하고 빈번하게 쓰이는 튜닝 기법이다.

부분 범위 처리 기법을 이용한 페이징 처리

CREATE TABLE TB_STOCK_TRD
(
    TRD_NO VARCHAR2 (10), -
    -
    거래번호
    STOCK_CD
    VARCHAR2
(
    6
), --종목코드
    TRD_DTM  VARCHAR2(14), --거래일시
    TRD_CNT  NUMBER(10), --거래건수
    TRD_AMT  NUMBER(10), --거래금액
    INST_DTM DATE, --입력일시
    INST_ID  VARCHAR2(50)  --입력자
);
ALTER TABLE TB_STOCK_TRD
    NOLOGGING;
INSERT /*+ APPEND */ INTO TB_STOCK_TRD --APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'),
       LPAD(TO_CHAR(RNUM), 6, '0'),
       TRD_DTM,
       TRUNC(DBMS_RANDOM.VALUE(100, 10000)),
       TRUNC(DBMS_RANDOM.VALUE(10000, 100000)),
       SYSDATE,
       'DBMSEXPERT'
FROM (
         SELECT ROWNUM RNUM
         FROM DUAL CONNECT BY LEVEL <= 2000
     ),
     (
         SELECT TO_CHAR(SYSDATE - (ROWNUM * 1000000) / 24 / 60 / 60, 'YYYYMMDDHH24MISS') TRD_DTM
         FROM DUAL CONNECT BY LEVEL <= 5000
     );

COMMIT;
ALTER TABLE TB_STOCK_TRD
    ADD CONSTRAINT TB_STOCK_TRD_PK
        PRIMARY KEY (TRD_NO);

ANALYZE TABLE TB_STOCK_TRD COMPUTE STATISTICS
    FOR ALL INDEXED COLUMNS SIZE 254;

부분 범위 처리 기법을 이용한 페이징 처리 - 튜닝 전

SELECT *
FROM (
         SELECT ROWNUM           RNUM,
                TRD_DTM,
                TRD_CNT,
                TRD_AMT,
                COUNT(*) OVER () CNT
         FROM (
                  SELECT TRD_DTM,
                         TRD_CNT,
                         TRD_AMT
                  FROM TB_STOCK_TRD A
                  WHERE STOCK_CD = '000001'
                    AND TRD_DTM >= TO_CHAR(SYSDATE - 365, 'YYYYMMDDHH24MISS')
                  ORDER BY TRD_DTM
              )
     )
WHERE RNUM BETWEEN 21 AND 30;
  • SQL 분석

    • ROWNUM을 이용하여 데이터의 순번을 매긴다.

    • 분석 함수인 COUNT 함수를 사용하여 결과 집합의 전체 건수를 가져온다.

    • STOCK_CD 조건을 주어 특정 종목의 데이터를 가져온다.

    • 최근 일년간의 거래 내역을 출력하기 위한 조건을 준다.

    • 21 ~ 30 번째 데이터의 결과만을 가져온다.

  • SQL의 문제점

    • STOCK_CD의 TRD_DTM 기준 최근의 데이터 중에서 21번째부터 30번째의 데이터만 가져오는 SQL

    • 30건 중 10건의 데이터만 가져오는데도 불구하고 적절한 인덱스가 존재하지 않아서 테이블 전체를 테이블 풀스캔하고 정렬 작업까지 수행한 후에야 그 중에서 10건만을 가져오게 된다.

    • 즉, 전체 범위 처리를 하고 페이징 처리한 매우 비효율적인 SQL이다.

    • 이러한 SQL문이 OLTP 환경에서 빈번하게 수행되는 경우 DBMS 전체 성능에 매우 큰 지장을 주게 된다.

부분 범위 처리 기법을 이용한 페이징 처리 - 튜닝 후

  • 부분 범위 처리를 위한 STOCK_CD와 TRD_DTM으로 구성된 복합 인덱스를 생성

CREATE INDEX
    TB_STOCK_TRD_IDX01
    ON TB_STOCK_TRD (STOCK_CD, TRD_DTM DESC);

ANALYZE
INDEX TB_STOCK_TRD_IDX01 COMPUTE STATISTICS;
SELECT *
FROM (
         SELECT ROWNUM           RNUM,
                TRD_DTM,
                TRD_CNT,
                TRD_AMT,
                COUNT(*) OVER () CNT
         FROM (
                  SELECT TRD_DTM,
                         TRD_CNT,
                         TRD_AMT
                  FROM TB_STOCK_TRD
                  WHERE STOCK_CD = '000001'
                    AND TRD_DTM >= TO_CHAR(SYSDATE - 365, 'YYYYMMDDHH24MISS')
                  ORDER BY TRD_DTM
              )
         WHERE ROWNUM <= 30
     )
WHERE RNUM >= 21
  • SQL 분석

    • STOCK_CD와 TRD_DTM으로 구성된 TB_STOCK_TRD_IDX01 인덱스를 인덱스 범위 스캔한다.

    • 인덱스 선두 컬럼인 STOCK_CD가 '=' 조건으로 들어가 있으므로 2번째 컬럼인 TRD_DTM의 일부분만 인덱스 스캔 후 멈출 수 있다.

    • 즉, 인덱스를 이용한 효율적인 부분 범위 처리가 가능하다.

    • ROWNUM 조건으로 인라인 뷰 내에서 30건만 가져오도록한다. TB_STOCK_TRD의 결과 중 30건만 부분 범위 처리로 가져오게 된다.

개발자를 위한 오라클 SQL 튜닝
최댓값/최솟값 검색
최댓값/최솟값 검색 튜닝
부분 범위 처리 기법을 이용한 페이징 처리 튜닝
부분 범위 처리 기법을 이용한 페이징 처리 튜닝