5.3 인덱스를 이용한 소트 연산 생략

5.3.1. Sort Order By 생략

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = ‘KR123456’
 ORDER BY 거래일시

• 인덱스 선두 컬럼을 종목 코드 + 거래일시 순으로 구성하지 않으면 소트 연산을 생략할 수 없다.
• 종목 코드 + 거래일시 인덱스를 사용한 경우 옵티마이저는 Sort Order By 오퍼레이션을 생략하게 된다.
• 전체 레코드를 읽을 필요가 없으므로 부분범위 처리 상황에서 위와같은 인덱스 활용은 매우 중요하다

부분범위 처리를 활용한 튜닝 기법, 아직도 유효한가?

• 부분범위 처리는 쿼리 수행 결과 중 앞쪽 일부를 우선 전송하고 멈추었다가 클라이언트가 추가 전송을 요청(Fetch Call) 할 때마다 남은 데이터를 조금씩 나눠 전송하는 방식을 말한다.
• 클라이언트가 DB 서버에 직접 접속하는 2-Tier 환경에서는 이특징을 활용한 튜닝 기법을 많이 활용하였다.

하지만 3-Tire 아키텍처는 서버 리소스를 수많은 클라이언트가 공유하는 구조이므로 클라이언트가 특정 DB 커넥션을 독점할 수 없다.

• 단위 작업을 마치면 DB 커넥션을 커넥션 풀에 반환해야 하므로 쿼리 조회 결과를 클라이언트에게 '모두' 전송하고 커서(Cursor) 를 닫아야만 한다. - 따라서 쿼리 결과 집합을 조금씩 나눠서 전송하는 방식을 사용할 수 없다
• 부분범위 처리 원리가 3-Tier 에서 여전히 유효한 이유는 Top N 쿼리에 있다.

5.3.2 Top N 쿼리

• Top N 쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리이다.

SQL Server

• TOP 10 을 사용한다.

SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목거래 = ‘KR123456’
   AND 거래일시 >= ‘20180304’
 ORDER BY 거래일시

IBM DB2

• FETCH FIRST 10 ROWS ONLY; 를 사용한다.

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목거래 = ‘KR123456’
   AND 거래일시 >= ‘20180304’
 ORDER BY 거래일시
 FETCH RISRT 10 ROWS ONLY

Oracle

• 인라인 뷰로 한 번 감싼다.

SELECT *
  FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
           FROM 종목거래
          WHERE 종목거래 = ‘KR123456’
            AND 거래일시 >= ‘20180304’
          ORDER BY 거래일시 )
 WHERE ROWNUM <= 10

• 정의한 집합을 모두 읽어 거래일시 순으로 정렬한 집합을 만들고 그 중 상위 10개레코드를 취한다.
• 소트를 생략하게 인덱스를 구성해도 중간집합을 만들어야 하므로 부분범위 처리가 불가능해 보인다
• 하지만 종목코드 + 거래일시 순으로 구성된 인덱스 이용시, 소트 연산 생략후 인덱스를 스캔하다가 10개 레코드를 읽는 순간 멈춘다.

실행계획 확인

• COUNT(STOPKEY) = (TOP N Stopkey 알고리즘) : 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM 으로 지전한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다는 뜻

0     SELECT STATEMENT Optimizer=ALL_ROWS
1  0    COUNT(STOPKEY)
2  1      VIEW
3  2        TABLE ACCESS (BY INDEX ROWID) OF ‘종목거래’ (TABLE)
4  3          INDEX (RANGE SCAN) OF ‘종목거래_PK’ (INDEX (UNIQUE))

페이징 처리

• 3 Tier 환경에서 부분범위 처리는 페이진 처리에서 활용한다.
• 3 Tier 환경에서 대량의 결과집합을 조회할 때 페이징 처리 기법을 활용한다.

SELECT *
  FROM ( SELECT ROWNUM NO, A.*
          FROM (
                /* SQL BODY */
                )
          WHERE ROWNUM <= (:page * 10)
        )
 WHERE NO >= (:page-1) * 10 + 1

• Top N 쿼리이므로 지정한 건수만큼 레코드를 업ㄷ으면 멈춘다
• 뒤쪽 페이지로 이동할수록 데이터량이 많아지지만 보통 앞쪽 일부 데이터만 확인하므로 문제되지 않는다.
• 3-Tier 에서 부분범위 처리를 활용하기 위해 우리가 할일
  1. 부분범위 처리 가능하도록 SQL 작성한다.
     • 인덱스 사용 가능하도록 조건절을 구사하고, 조인은 NL 조인 위주로 처리(룩업을 위한 작은 테이블을 해시 조인 Build Input 으로 처리해도 됨)하고, Order By 절이 있어도 소트 연산을 생략할 수 있도록 인덱스 구성 의미
  2. 작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣는다.

SQL 및 실행계획 확인

SELECT *
  FROM ( SELECT ROWNUM NO, A.*
           FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
                    FROM 종목거래
                   WHERE 종목거래 = ‘KR123456’
                     AND 거래일시 >= ‘20180304’
                   ORDER BY 거래일시
                )
          WHERE ROWNUM <= (:page * 10)
        )
 WHERE NO >= (:page-1) * 10 + 1

0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0   VIEW
2  1     COUNT(STOPKEY) -> NO STOP + STOPKEY
3  2       VIEW
4  3         TABLE ACCESS (BY INDEX ROWID) OF ‘종목거래’ (TABLE)
5  4           INDEX (RANGE SCAN) OF ‘종목거래_PK’ (INDEX)

• COUNT(SROPKEY)를 확인할 수 있다.

페이징 처리 ANTI 패턴

• 위 쿼리에서 WHERE ROWNUM <= (:page * 10) 는 불필요해 보인다.
• 하지만 ROWNUM 은 단순한 조건절이 아닌, TOP N Stopkey 알고리즘을 작동하는 열쇠이다.
• 실제로 ROWNUM 조건절을 제거하면 COUNT(STOPKEY) 가 COUNT 로 변경된다.

COUNT    -> NO SORT + NO STOP

• 위 와 같은 쿼리의 문제는 조회 조건에 해당하는 데이터가 다 출력이 되었어도 그 조건을 만족하는 데이터가 더 존재하는지 계속 I/O 작업이 일어나게 된다. 따라서 ROWNUM 을 빼먹지 말자

5.3.3. 최소값/최대값 구하기

• 최소값(MIN), 최대값(MAX)를 구하는 SQL 실행계획을 보면 Sort Aggregate 오퍼레이션이 나타난다.
• Sort Aggregate 는 전체 데이터를 정렬하진 않지만, 전체 데이터를 읽으면서 값을 비교한다.

SELECT MAX(SAL) FROM EMP;

Execution Plan
-----------------------------------------------
0  SELECT STATEMENT Optimizer==ALL_ROWS
1  0  SORT (AGGREGATE)
2  1    TABLE ACCESS (FULL) OF ‘EMP’ (TABLE)

• 인덱스는 정렬돼있으므로 전체 데이터를 읽지 않고 최소 최대를 쉽게 찾는다.

CREATE INDEX EMP_X1 ON EMP(SAL);
 
SELECT MAX(SAL) FROM EMP;

Execution Plan
-----------------------------------------------
0  SELECT STATEMENT Optimizer==ALL_ROWS
1  0  SORT (AGGREGATE)
2  1    INDEX (FULL SCAN (MIN/MAX)) OF ‘EMP_X1’ (INDEX)

인덱스를 이용해 최소/최대값 구하기 위한 조건

• 전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함돼 있어야 한다.

CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);
 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;

Execution Plan
-----------------------------------------------
0  SELECT STATEMENT Optimizer==ALL_ROWS
1  0  SORT (AGGREGATE)
2  1    FIRST ROW
3  2      INDEX (RANGE SCAN(MIN/MAX)) OF ‘EMP_X1’ (INDEX)

• 조건절 컬럼과 MAX 컬럼이 모두 인덱스포함 + 인덱스 선두 컬럼 DEPTNO, MGR 이 모두 = 조건이다.
• 실행계획의 FIRST ROW 는 조건을 만족하는 레코드 1개를 찾을때 바로 멈춘다는 의미 = First Row Stopkey 알고리즘

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL, MGR);
 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;

• DEPTNO = 30 조건을 만족하는 범위에서 가장 오른쪽 가장 큰 SAL 값을 읽는다.(액세스 조건)
• 거기서 스캔을 시작해 MGR = 7698 조건을 만족하는 레코드를 1개 찾을 떄 멈춘다.(필터 조건)

CREATE INDEX EMP_X1 ON EMP(SAL, DEPTNO, MGR);
 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;

• DEPTNO, MGR 모두 필터 조건이다.
• 조건절 컬럼과 MAX 컬럼이 모두 인덱스 포함이므로 First Row Stopkey알고리즘은 작동한다.

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL
 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;

• DEPTNO = 30 조건을 만족하는 전체 레코드를 읽어 MGR =7698 조건을 필터링한 후 MAX(SAL) 값을 구한다.
• First Row Stopkey 알고리즘이 작동하지 않는다.

Top N 쿼리 이용해 최소/최대값 구하기

• ROWNUM ,= 1 조건을 이용해 최소 최대를 쉽게 구한다.
• Top N 쿼리에 작동하는 Top N Stopkey 알고리즘은 모든 컬럼이 인덱스에 포함되어 있지 않아도 잘 작동한다.
• DEPTNO = 30 조건을 만족하는 가장 오른쪽부터 역순으로 스캔하면서 MGR=7698 조건을 만족하는 레코드 1개를 찾을 때 바로 멈춘다.

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);
 
SELECT *
  FROM ( 
         SELECT SAL
           FROM EMP
          WHERE DEPTNO = 30
            AND MGR = 7698
          ORDER BY SAL DESC 
        )
 WHERE ROWNUM <= 1;

5.3.4 이력 조회

• 일반 테이블은 각 컬럼의 현재값만 저장하므로 변경되기 이전 값을 알 수 없다.
• 값이 어떻게 변경되는지 과거이력을 조회할 필요성이 존재하면 이력 테이블을 따로 관리해야 한다.(1:다 테이블관리)

• 일반적으로 상태변경이력 테이블에는 현재 데이터도 저장한다. 그래야 변경 이력을 완벽히 재생할 수 있다.
• 이력 데이터 조회시 First Row Stopkey 또는 Top N Stopkey 알고리즘이 작동하게 구현하는 것이 고급 SQL 튜너에게 필요하다 - 그래서 내용이 어려운거 같다....

가장 단순한 이력 조회

SELECT 장비번호, 장비명, 상태코드
     , ( SELECT MAX(변경일자)
           FROM 상태변경이력
           WHERE 장비번호 = P.장비번호 ) 최종변경일자
  FROM 장비 P
 WHERE 장비구분코드 = ‘A001’

• 위 SQL 문에서 이력 조회하는 스칼라 서브쿼리 부분에 ‘First Row Stopkey’ 알고리즘이 작동하고 있다.
• 상태변경이력_PK 인덱스가 장비번호 + 변경일자 + 변경순 순으로 구성돼 있기 때문에 가능한 일이다.
• MAX 의 변경일자가 인덱스에 포함

점점 복잡해지는 이력조회

책 p368

• 인덱스 컬럼을 가공하였으므로 First Row Stopkey 알고리즘이 작동하지 않는다.
• 장비별 상태변경이력이 많아 성능에 문제가 발생한다면 그냥 3번 조회하는 것이좋다.
• 3번 조회하는 비효율은 존재하지만, First Row Stopkey 알고리즘이 작동하므로 성능은 비교적 좋다.
• 점점 읽어야 할 컬럼이 많아진다면..?

INDEX_DESC 힌트 활용

• 인덱스를 역순으로 읽도록 index_desc 힌트를 사용하고, 첫번째 레코드에서 바로 멈추도록 rownum <= 1 조건절을 사용한다.
• 이 방식이 성능은 확실하지만, 인덱스 구성이 완벽해야만 쿼리가 잘 작동한다. 인덱스 구성이 바뀌면 결과집합에 문제가 발생할 수 있다.
• PK 구성을 변경하는 일은 거의 없지만 다른 대안도 알아보자

11g/12c 신기능

• Predicate Pushing 이라는 쿼리 변환을 적용
• Top N Stopkey 알고리즘이 작동하지 않아 성능은 느려질 수 있지만 쿼리 결과집합은 보장된다.
• 12c 에서는 Top N Stopkey 알고리즘도 작동한다.

Row Limiting 절 도 12c 부터 지원한다. 추가적인 공부를 해보지

상황에 따라 달라져야 한느 이력 조회 패턴

• 이력 조회 업무 패턴은 굉장히 다양하다.
• First Row Stopkey 와 Top N Stopkey 가 작동하게 SQL 를 작성하는 방식을 위주로 살펴보았지만 상황에따라 패턴이 달라질 수 있다.
• 인덱스 손익분기점을 파악한 후 다양한 방식을 고려해 보자 Hash + Full Scan 이 더 효과적일수있다.
• 혹은 윈도우 함수도 하나의 방식이다
• KEEP 절도 활용가능하다

선분 이력 맛보기

• 선분이력 모델도 고려 가능하다
• 어떤 상황에서든 간단한 쿼리로 쉽게 이력을 조회할 수 있고, 성능 측명에 이점도 생긴다
• 선분이력 모델도 공부해보자

5.3.5 Sort Group By 생략

• 그룹핑 연산에도 인덱스를 활용할 수 있다.
• 아래 SQL 에 region 이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략할 수 있다. -> Sort Group By Nosort 로 표시된다.
• 책 379 페이지 그림을 보면 쉽게 이해가 된다.

SELECT REGION, AVG(AGE), COUNT(*)
  FROM CUSTOMER
 GROUP BY REGION;

5.4 Sort Area 를 적게 사용하도록 SQL 작성

5.4.1 소트 데이터 줄이기

SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, ‘yyyymmdd hh24:mi:ss’)
  FROM 주문상품
 WHERE 주문일시 BETWEEN :START AND :END
 ORDER BY 상품번호


SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, ‘yyyymmdd hh24:mi:ss’)
  FROM (
         SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시 
           FROM 주문상품
          WHERE 주문일시 BETWEEN :START AND :END
         ORDER BY 상품번호
        )

• 1번은 lpad 로 문자길이를 30 으로 가공한 정보를 결과집합 Sort Area 에 담아야 되고 2번은 가공하지 않는다. 따라서 2번이 더 Sort Area 를 적게 사용한다.

SELECT *
  FROM 예수금원장
 ORDER BY 총예수금 DESC
 
 SELECT 계좌번호, 총예수금
  FROM 예수금원장
 ORDER BY 총예수금 DESC
 

• 2번이 저장 컬럼이 더 적으므로 효율적이다.
• 2개다 Full Scan 을 하더라도 소트한 데이터량이 다르게 되므로 성능차이가 발생한다.

5.4.2 Top N 쿼리의 소트 부하 경감 원리

• Top N Stopkey 알고리즘 방법
  • 1000명의 학생중 10명의 키큰 학생을 뽑는다.
  • 맨 앞부터 10명을 뽑는다.
  • 990 명이 1명씩 나오면서 10명과 비교하여 키가 크면 그 학생과 자리를 바꾸고 들어간다.
  • 이런경우 1000명이 존재하지만 10자리만 가지고 있으면 된다.
• 대상 집합이 아무리 커도 많은 메모리 공간이 필요하지 않는다.
• 전체 레코드를 다 정렬하지 않고도 오름차순으로 최소값을 갖는 열개의 레코드를 정확히 찾아낼 수 있다.
• 이것이 Top N 소트 알고리즘이 소트 연산 횟수와 Sort Area 사용량을 줄여주는 원리이다.
• 인덱스로 소트 연산을 생략할 수 없어 Table Full Scan 방식 시 SORT ORDER BY STOPKEY 를 사용한다.
• 예제는 책 p384 참고

5.4.3 Top N 쿼리가 아닌 경우

• 위 예제에서 where rownum 조건을 제거하면 Top N 쿼리가 아니게 된다.
• Top N 소트 알고리즘이 작동하지 않는다.
• 정렬과정에서 Temp 테이블 스페이스를 사용할 수도 있다.

5.4.4 분석함수에서의 Top N 소트

• 윈도우 함수 중 rank, row_number 함수는 max 함수보다 소트 부하가 적다
• Top N 소트 알고리즘이 작동하기 때문이다.
• sql 쿼리는 책 388쪽을 참고