[친절한 SQL 튜닝 스터디] 소트 튜닝

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

인덱스 구조와 탐색 방법에 대해 알아보자! B*Tree와 인덱스 루트, 브랜치 블록, 리프 블록. 그리고 인덱스 수직적 탐색, 인덱스 수평적 탐색, 결합 인덱스 등 2022년 5월 8일

velog.io

소트 연산에 대한 이해

SQL 수행 도중 가공된 데이터 집합이 필요할때, 오라클은 PGA나 Temp Tablespace를 사용.

그 대표적인 예가 소트머지 조인, 해시조인, 데이터 소트와 그룹핑이다.

소트 수행 과정

소트는 기본적으로 PGA에 할당된 Sort Area에서 이루어짐.
그 공간이 다차면 Temp 테이블 스페이스 사용.

소트의 2가지 유형

메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말함. (=Internal Sort)
디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것을 말함 (=External Sort)

디스크 소트 과정

소트할 대상을 SGA 버퍼캐시를 통해 읽고 Sort Area에서 정렬 시도.
양이 많으면 중간 집합을 Temp 테이블스페이스에 임시 세그먼트 만들어 저장.
(Temp 영역에 저장해둔 중간 단계 집합을 Sort Run 이라고 한다.)
정렬된 최종 결과 집합을 얻으려면 다시 Merge 해야함.

소트연산은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 함.

처리할 데이터량이 많으면 디스크 I/O까지 발생해 쿼리 성능을 좌우한다.

많은 서버 리소스를 사용하고 디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가하게 만들어 OLTP 환경에서 애플리케이션 성능을 저하시키는 주 요인이다.

그래서 소트가 발생하지 않도록 하고, 소트가 불가피하면 메모리 내에서 수행을 완료하게 하자!

소트 오퍼레이션

Sort Aggregate

전체 로우를 대상으로 집계를 수행할 때 나타난다. 하지만 실제로 데이터를 정렬하지 않고 Sort Area만 사용함.

select sum(sal), max(sal), min(sal), avg(sal) from emp;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

데이터를 정렬하지 않고 SUM, MAX, MIN, AVG 값을 구하는 절차

Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수를 각각 하나씩 할당함.
EMP 테이블 첫번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고 COUNT 변수는 1을 저장.
EMP 테이블에서 레코드를 하나씩 읽어나가며 아래 동작을 수행
1. SUM 변수에 값을 누적
2. MAX 변수는 기존보다 큰 값이 나타날때 값 기록
3. MIN 변수는 기존보다 작은 값이 나타날때 값 기록
4. COUNT 변수는 SAL 값이 NULL이 아닐때마다 1씩 증가.
EMP 레코드를 다 읽으면 SUM, MAX, MIN, COUNT 변수에 각 값이 저장되어 있음.
SUM, MAX, MIN는 그대로 출력. AVG는 SUM / COUNT 하여 출력

Sort Order By

데이터를 정렬할때 사용. 정렬과정은 디스크 소트로 설명함.

SELECT * FROM EMP ORDER BY SAL DESC;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Sort Group By

소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 사용.

SELECT DEPTNO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
FROM   EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |   165 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |    11 |   165 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   210 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

예시

💡 수천명의 사원이 근무하는 회사를 가정.
부서는 4개. 부서코드는 10, 20, 30, 40을 사용한다.
컴퓨터의 도움을 받지 않고 SAL을 집계하라고 해보자.

각 4개의 부서에 SUM, MAX, MIN, COUNT을 기록할 수 있도록 환경을 만들어준다.

DBMS는 Sort Group by를 처리 할 때, Sort Aggregate에서 사용한 방식을 각 부서에 사용한다.

그래서 부서의 수가 많지 않다면 Sort Area가 클 필요도 없고, Temp 테이블 스페이스를 사용하지 않는다.

오라클 10gR2 버전에선 Hash Group by를 사용한다고 함. 책 참고.
~~(얘네는 버전도 특이해)~~

Sort Unique

옵티마이저가 서브쿼리를 풀어 일반 조인으로 변환하는게 서브쿼리 Unnesting인데,
Unnesting된 서브쿼리가 M쪽 집합이면 메인쿼리와 조인하기전에 중복 레코드부터 제거해야 함.

이때 Sort Unique 오퍼레이션 두둥등장!

SELECT /*+ ORDERED USE_NL(DEPT) */ * FROM DEPT
WHERE  DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO
                  FROM   EMP
                  WHERE  JOB = 'CLERK');

---------------------------------------------------------------------------------
| Id | Operation                     | Name        | Rows | Bytes | Cost (%CPU) |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |             |    3 |    87 |     4  (25) |
|  1 |  NESTED LOOPS                 |             |    3 |    87 |     4  (25) |
|  2 |   **SORT UNIQUE**                 |             |    3 |    33 |     2   (0) |
|  3 |   TABLE ACCESS BY INDEX ROWID | EMP         |    3 |    33 |     2   (0) |
|  4 |    TABLE RANGE SCAN           | EMP_JOB_IDX |    3 |       |     1   (0) |
|  5 |   TABLE ACCESS BY INDEX ROWID | DEPT        |    1 |    18 |     1   (0) |
|  6 |    INDEX UNIQUE SCAN          | DEPT_PK     |    1 |       |     0   (0) |
---------------------------------------------------------------------------------

만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 SORT UNIQUE 오퍼레이션은 생략
Union, Minus, Intersect 같은 집합(Set) 연산자를 사용할때도 Sort Unique 두둥등장
Distinct 연산자를 사용해도 Sort Unique 두둥등장

Sort Join

소트 머지 조인을 수행할때 사용한다.

SELECT /*+ ORDERED USE_MERGE(E) */ *
FROM   DEPT D, EMP  E
WHERE  D.DEPTNO = E.DEPTNO;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   770 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |   770 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    72 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    72 |     3   (0)| 00:00:01 |
|   4 |   SORT JOIN         |      |    14 |   518 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Window Sort

윈도우 함수(=분석 함수)를 사용할 때 나타난다.

SELECT EMPNO, ENAME, JOB, MGR, SAL,
       AVG(SAL) OVER(PARTITION BY DEPTNO)
FROM   EMP;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   406 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    14 |   406 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   406 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

소트가 발생하지 않도록 SQL 작성

Union vs Union All

Union : 옵티마이저는 상단과 하단 두 집합간 중복을 제거하려고 소트 작업을 수행한다.
Union All : 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다.

성능을 위해서 Union All을 주로 사용하자.

그러나 Union All을 사용하려면 데이터 모델에 대한 이해 및 집학적 사고가 필요하다. 잘못하면 데이터 중복용 연산자를 잘못 사용할 수 있다.

SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
  FROM 결제
 WHERE 결제수단코드 = 'M' AND 결제일자 = '20180316'
 UNION
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
  FROM 결제
 WHERE 결제수단코드 = 'C' AND 결제일자 = '20180316'

위의 쿼리처럼 두 집합이 서로 겹치는 내용이 없이 상호배타적이면 Union All을 사용해도 된다!

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE **결제일자** = '20180316'
 UNION
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
 FROM 결제
 WHERE **주문일자** = '20180316'

그런데 이 쿼리처럼 두 집합은 상호배타적(겹치지 않는) 조건이 아니기 때문에 Union All을 쓰면 결제일자와 주문일자가 같은 결제 데이터가 중복되어 출력!

Union All을 사용하려면 아래 쿼리처럼 중복을 피하고 사용하자.

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE 결제일자 = '20180316'
 UNION ALL
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE 주문일자 = '20180316'
   AND 결제일자 <> '20180316'

오라클은 LNNVL 이라는 함수를 사용하여 NULL과 중복을 피할 수 있음.

Exists 활용

Distinct 연산자는 조건에 해당하는 데이터를 모두 읽어 중복을 제거해야해 성능에 안좋음. (I/O 많음, 부분범위 처리 불가)

SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P, 계약 C
 WHERE P.상품유형코드 = :pclscd
   AND C.상품번호 = P.상품번호
   AND C.계약일자 BETWEEN :DT1 AND :DT2
   AND C.계약구분코드 = :CTPCD

SELECT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P
 WHERE P.상품유형코드 = :pclscd
   AND EXISTS ( SELECT 'X' FROM 계약 C
                 WHERE C.상품번호 = P.상품번호
                   AND C.계약일자 BETWEEN :DT1 AND :DT2
                   AND C.계약구분코드 = :CTPCD
                            )

위의 쿼리는 Distinct를 Exists로 바꿔본 쿼리이다.

Exists 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건절에 해당하는 데이터를 모두 읽지 않음.

Distinct 뿐만 아니라 Minus 연산자를 사용한 쿼리도 Exists 서브쿼리로 변환이 가능하다.

조인 방식 변경

인덱스를 사용해 소트 연산을 생략할 수 있지만, 조인문이면 조인 방식도 잘 선택해야 한다.

두 테이블이 NL 조인 하도록 조인 방식을 변경하면 소트 연산을 생략해 데이터가 많고 부분 범위처리가 가능한 상황에서 큰 성능 개선을 할 수 있다.

예시 : 계약_X01 인덱스 ⇒ [지점 ID + 계약일시]

SELECT C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

위의 쿼리 같은 경우는 인덱스를 사용한다고 해도 해시 조인이라 Sort Order by가 발생한다.

SELECT **/*+ leading(C) use_nl(P) */**
       C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

하지만 이 쿼리의 경우 힌트로 NL 조인을 유도했다. 이로서 인덱스를 사용하여 sort 연산을 생략했다.

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

Sort Order By 생략

아래 쿼리에서 인덱스 선두컬럼을 [종목코드 + 거래일시] 순으로 구성하면 소트 연산을 생략할 수 있다.

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

소트 연산을 생략하면 실행계획에서 Sort Order By를 생략.

이 원리를 활용하면 소트해야 할 대상 레코드가 무수히 많은 상황에서 극적인 성능 개선 효과를 낼 수 있다.

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

요즘 DB에서는 3티어 환경에서 작동해 부분 범위 처리가 의미가 없다는 말이 많다.

부분범위 처리 : 쿼리 수행 결과 중 앞쪽 일부를 우선 전송하고 멈췄다가 클라이언트 요청에 조금씩 나누어 전송하는 방식.

AWS, AP 서버가 존재하는 3티어 아키텍처는 서버 리소스를 수많은 클라이언트가 공유해 DB 커넥션을 독점할 수 없다.

부분 범위 처리 활용의 핵심

결과 집합 출력을 바로 시작할 수 있느냐
앞쪽 일부만 출력하고 멈출 수 있느냐

Top N 쿼리

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

-- SQL Server
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
   AND 거래일시 >= '20180304'
 ORDER BY 거래일시

-- IBM DB2
SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
   AND 거래일시 >= '20180304'
 ORDER BY 거래일시
 FETCH RISRT 10 ROWS ONLY

-- 오라클
SELECT *
  FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
           FROM 종목거래
          WHERE 종목코드 = 'KR123456'
            AND 거래일시 >= '20180304'
          ORDER BY 거래일시 )
 WHERE ROWNUM <= 10

-- PostgreSQL
SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
   AND 거래일시 >= '20180304'
 ORDER BY 거래일시
 LIMIT 10 OFFSET 0

SQL만 보면, 인라인 뷰로 모두 읽고 거래일시 순으로 정렬한 중간 집합 생성 후 상위 열 개 레코드 추출한 방식이다.

소트를 생략할 수 있도록 인덱스를 구성해도 중간집합을 만들어야 해서 부분범위 처리는 안될 것 같다.

그러나 [종목코드 + 거래일시] 인덱스를 이용하면, 옵티마이저는 소트 연산을 생략하며, 인덱스를 스캔하다가 열 개 레코드를 읽는 순간 멈춘다.

페이징 처리

3티어 환경에서 부분범위 처리를 활용하려면 아래와 같이 만든다.

부분범위 처리 가능하도록 SQL을 작성. 부분 범위 처리가 잘 동작하는지 쿼리툴로 테스트.
작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣는다.

이때 주의점

인덱스 사용 가능하도록 조건절을 구사
조인은 NL 조인 위주로 처리
Order by 절이 있어도 소트 연산을 생략할 수 있도록 구성

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

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

페이징 처리 ANTI 패턴

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

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

이렇게 쿼리 짜면 Stopkey가 없어져서 전체 범위를 처리해버림

최소값/최대값 구하기

MIN 도는 MAX를 구하는 SQL 실행계획은 Sort Aggregate 오퍼레이션이 나타난다.

SELECT MAX(SAL) FROM EMP;
---------------------------
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;
-------------------------------------
0 |    SELECT STATEMENT Optimizer==ALL_ROWS
1 | 0 | SORT (AGGREGATE)
2 | 1 |   INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_X1' (INDEX)

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

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

테이블 액세스가 발생하지 않아야 함.

처음부터 만족하는 레코드 찾고 바로 멈추는 알고리즘을 Fisrt Row Stopkey 라고 함.

-- 인덱스 [DEPTNO + MGR + SAL]
-- 조건절 MAX 컬럼이 모두 인덱스에 포함, 인덱스 선두컬럼 : DEPTNO, MGR이 '=' 조건
-- FIRST ROW가 표시됨 (Fisrt Row Stopkey)
CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
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)

-- [DEPTNO, SAL, MGR]
-- DEPTNO: 액세스 조건, MGR: 필터조건, MAX 조건이 인덱스에 포함됨
-- FIRST ROW가 표시됨 (Fisrt Row Stopkey)
CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL, MGR);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
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)

-- [SAL, DEPTNO, MGR]
-- DEPTNO, MGR: 필터조건, MAX 조건이 인덱스에 포함됨
-- INDEX FULL SCAN 하지만 FIRST ROW가 표시됨 (Fisrt Row Stopkey)
CREATE INDEX EMP_X1 ON EMP(SAL, DEPTNO, MGR);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 |    SELECT STATEMENT Optimizer==ALL_ROWS
1 | 0 | SORT (AGGREGATE)
2 | 1 |   FIRST ROW
3 | 2 |    INDEX (FULL SCAN(MIN/MAX)) OF 'EMP_X1' (INDEX)

-- [DEPTNO, SAL]
-- 조건절 컬럼과 MAX 컬럼중 어느 하나가 인덱스에 포함되지 않는 경우.
-- MGR 컬럼이 인덱스에 없어 MGR = 7698 조건은 테이블에서 필터링 해야함.
-- (First Row Stopkey 알고리즘 작동 X)
CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 |    SELECT STATEMENT Optimizer==ALL_ROWS
1 | 0 | SORT (AGGREGATE)
2 | 1 |   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
3 | 2 |    INDEX (RANGE SCAN) OF 'EMP_X1' (INDEX)

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

Top N 쿼리를 통해서도 최소 또는 최대값을 쉽게 구할 수 있다.

ROWNUM <=1 조건을 이용해 Top 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;
--------------------------------------------------------------------------
0 |    SELECT STATEMENT Optimizer==ALL_ROWS
1 | 0 | COUNT (STOPKEY)
2 | 1 |   VIEW
3 | 2 |    TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
4 | 3 |     INDEX (RANGE SCAN DESCENDING) OF 'EMP_X1' (INDEX))

/*
MGR 컬럼이 인덱스에 없지만 가장 큰 SAL 값을 찾기 위해
DEPTNO = 30 조건을 만족하는 '전체' 레코드를 읽지 않는다.

DEPTNO = 30 조건을 만족하느 가장 오른쪽부터 역순으로 스캔해 테이블 엑세스하다가
MGR = 7698 조건을 만족하는 레코드 하나 찾으면 바로 멈춘다.
*/

Top N Stopkey 알고리즘은 모든 컬럼이 인덱스에 포함되지 않아도 잘 작동한다.

인라인 뷰를 사용해 쿼리가 복잡해지나, 성능은 MIN/MAX 보다 좋다.

이력 조회

일반 테이블은 각 컬럼의 현재값만 저장한다. 그래서 이전 값을 알 수 없는데, 과거 이력을 조회할 필요가 있다면 이력 테이블을 따로 관리해야 한다.

과거 이력 테이블은 일반적으로 현재 데이터도 저장한다. 그래야 변경이력을 완전 재생이 가능하기 때문이다.

가장 단순한 이력 조회

고급 SQL 튜너가 되려면, 이력 데이터 조회할 때 ‘First Row Stopkey’ 또는 ‘Top N Stopkey’ 알고리즘이 작동할 수 있게 인덱스 설계 및 SQL 구현할 수 있어야 함.

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

ID | Operation                     | Name           | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------
0  | SELECT STATEMENT              |                | 1      | 10     | 4
1  | SORT AGGREMENT                |                | 10     | 10     | 22
2  |   FIRST ROW                   |                | 10     | 10     | 22
3  |    INDEX RANGE SCAN (MIN/MAX) | 상태변경이력_PX | 10     | 10     | 22
4  | TABLE ACCESS BY INDEX ROWID   | 장비           | 1      | 10     | 4
5  | INDEX RANGE SCAN              | 장비_N1        | 1      | 10     | 2

Sort Group By 생략

그룹핑 연산에도 인덱스를 활용할 수 있다.

아래 SQL에 region이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략가능.

실행계획에 ‘Sort Group By Nosort’ 확인.

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

--------------------------------------------------------------------------
Id | Operation                     | Name         | Rows  | Bytes | Cost(%CPU)
---------------------------------------------------------------------------------
0  | SELECT STATEMENT              |              | 25    | 725   | 30142(1)
1  | SORT GROUP BY NOSORT          |              | 25    | 725   | 30142(1)
2  |   TABLE ACCESS BY INDEX ROWID | CUSTOMER     | 1000K | 27M   | 30142(1)
3  |    INDEX FULL SCAN            | CUSTOMER_X01 | 1000K |       | 2337(2)

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

소트 데이터 줄이기

소트 연산이 불가피 할 경우, 메모리 내에서 처리 완료해야 한다.
sort area 적게 사용하는 방법
1. 소트를 완료하고 나서 데이터 가공
2. Top-N쿼리 : 소트 연산(=값 비교) 횟수 최소화 및 sort area 사용량 감소

예시1

💡 아래 두 SQL 중 어느것이 Sort Area를 적게 사용할까?

-- 1번 쿼리
-- 레코드당 107(30+30+10+20+17) 바이트로 가공한 결과집합을 Sort Area에 담는다.
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객id, 10)
       || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from   주문상품
where  주문일시 between :start and :end
order  by 상품번호

---------------------------------------------------------------------------
-- 2번 쿼리
-- 가공하지 않은 상태로 정렬을 완료 한 후 최종 출력할 때, 가공한다.
-- (2번 쿼리가 Sort Area를 훨씬 적게 사용한다.)
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객id, 10)
       || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from   (
  select 상품번호, 상품명, 고객id, 고객명, 주문일시
  from 주문상품
  where  주문일시 between :start and :end
  order  by 상품번호
)

예시2