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 0SQL만 보면, 인라인 뷰로 모두 읽고 거래일시 순으로 정렬한 중간 집합 생성 후 상위 열 개 레코드 추출한 방식이다.
소트를 생략할 수 있도록 인덱스를 구성해도 중간집합을 만들어야 해서 부분범위 처리는 안될 것 같다.
그러나 [종목코드 + 거래일시] 인덱스를 이용하면, 옵티마이저는 소트 연산을 생략하며, 인덱스를 스캔하다가 열 개 레코드를 읽는 순간 멈춘다.
페이징 처리
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 | 2Sort 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)