seoyyyy-dev
5회차 소트 튜닝 본문
1. 소트 연산에 대한 이해
1.1 소트 수행 과정
- 소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어지며 메모리 공간인 Sort Area가 다 차면 디스크 Temp 테이블스페이스를 활용한다.
😎 소트 연산의 두가지 유형
▶️ 메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것. 'Internal Sort'라고도 함
▶️ 디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용함. 'External Sort' 라고도 함
🙂 디스크 소트 과정
1️⃣ 소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, Sort Area에서 정렬을 시도한다.
2️⃣ Sort Area만으로는 양이 많아 공간이 부족할 때 정렬된 중간집합을 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장한다. (Sort Run)
3️⃣ 정렬된 최종 결과 집합을 얻기 위해 다시 Merge 한다. 각 Sort Run 내에서 이미 정렬된 상태이다.
4️⃣ 오름차순 정렬이라면 각각에서 가장 작은 값부터 PGA로 읽다가 PGA가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에게 전송한다.
소트연산은 메모리 집약적(Memory-intensive)일 뿐 아니라 CPU 집약적(CPU-intensive)이며, 처리할 데이터량이 많을 땐 디스크 I/O(디스크 소트 시)까지 발생하므로 쿼리 성능을 좌우하는 매우 중요한 요소이다.
또한 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이기 때문에 될 수 있으면 소트가 발생하지 않도록 SQL을 작성하고, 소트가 불가피하면 메모리 내에서 수행을 완료할 수 있게 해야한다.
1.2 소트 오퍼레이션
🤗 Sort Aggregate
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 |
--------------------------------------------------------------------------------------------
Sort Aggregate는 전체 로우를 대상으로 집계를 수행할 때 나타난다.
'Sort' 라는 표현으로 정렬이 될 것 같지만 실제로는 Sort Area를 사용한다는 의미이며 데이터를 정렬하지는 않는다.
집계를 수행하는 절차는 다음과 같다.
 |
1️⃣ Sort Area에 SUM, MAX, MIN, COUNT값을 위한 변수를 하나씩 할당한다. 2️⃣ EMP 테이블 첫 번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN변수에 저장하고, COUNT 변수에는 1을 저장한다. |
 |
3️⃣ EMP 테이블의 레코드를 읽어 내려가며 SUM 변수는 값을 누적, MAX 변수에는 기존보다 큰 값이 나타날 때마다 값을 대체, MIN 변수에는 기존보다 작은 값이 나타나면 값을 대체한다. COUNT 변수에는 SAL 값이 NULL이 아닌 레코드를 만날 때마다 1씩 증가시킨다. 4️⃣ EMP 레코드를 다 읽고나서 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 Order By는 데이터를 정렬할 때 나타난다.
🤩 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 |
--------------------------------------------------------------------------------------------
Sort Group By는 소팅 알고리즘을 사용해 그룹별 집계함수를 수행할 때 나타난다.
부서코드가 각각 10, 20, 30, 40이 존재할 때 10부터 40까지 적은 메모지 네개가 있다고 생각하자.
각 메모지에 SUM, MAX, MIN, COUNT를 적을 수 있도록 입력란을 두고 메모지를 부서번호 순으로 정렬해 놓는다.
그리고 각 사원의 급여정보를 읽으며 각 사원의 부서번호에 해당하는 메모지에 Sort Aggregate에서 사용했던 방식 처럼 SUM, MAX, MIN, COUNT 값을 갱신한다.만일 그룹의 개수를 미리 알 수 없다면, 급여 대장을 읽다가 새로운 부서가 나타날 때마다 새로 준비한 메모지를 정렬 순서에 맞추어 중간에 끼워넣는 방식을 사용한다.
그룹의 개수가 많지 않다면 Sort Area가 클 필요가 없으며 집계할 대상 레코드가 아무리 많더라도 Temp 테이블 스페이스를 쓰지 않을 수 있다.
⭐오라클 10gR2 버전부터는 Group By 절 뒤에 Order By 절을 명시하지 않는다면 Hash Group By 방식으로 처리된다.
select deptno, sum(sal), max(sal), min(sal), avg(sal)
from emp
group by deptno;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25) | 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 165 | 4 (25) | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------
Sort Group By에서 소트 알고리즘을 사용한다면 Hash Group By는 해싱 알고리즘을 사용한다.
⭐참고로 Sort Group By와 Hash Group By는 정렬 순서를 보장하지 않으므로 정렬된 그룹핑 결과를 얻고자 한다면, 반드시 Order By를 명시해야 한다. (Sort, Hash 는 각각 Sort Area와 Hash Area를 사용한다는 의미로 생각하자.)
😉 Sort Unique
옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 '서브쿼리 Unnesting'을 할 때 메인 쿼리와 조인하기 전에 중복 레코드를 제거해야 한다. 이때 Sort Unique 오퍼레이션이 나타난다. (Unnesting된 서브쿼리가 M쪽 집합이거나 1쪽 집합이여도 Unique 인덱스가 아닐 때 발생)
select /*+ ordered use_nl(dept) */ * from dept
where deptno in (select /*+ unnest */ deptno from emp where jop = 'CLERK');
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 4 (25) |
| 1 | NESTED LOOP | | 3 | 87 | 4 (25) |
| 2 | SORT UNIQUE | | 3 | 33 | 2 (0) |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | 3 | 33 | 2 (0) |
| 4 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0) |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 18 | 1 (0) |
| 6 | INDEX RANGE SCAN | DEPT_PK | 1 | | 0 (0) |
--------------------------------------------------------------------------------------------
만일 PK/Unique 제약 또는 Unique 인덱스를 통해 Unnesting 된 서브쿼리의 유일성이 보장된다면 Sort Unique 오퍼레이션은 생략된다.
참고로 Union, Minus, Intersect같은 집합(Set) 연산자를 이용할 때도 Sort Unique 오퍼레이션이 나타나며 Distinct 연산자 사용시에도 Sort Unique 오퍼레이션이 나타난다.
⭐ 오라클 10gR2 부터 Distinct 연산에 Hash Unique 방식을 사용한다. Group By와 마찬가지로 Order By를 생략할 때 그렇다.
select distinct deptno from emp;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 4 (25) | 00:00:01 |
| 1 | HASH UNIQUE | | 3 | 9 | 4 (25) | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------
🙃 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 |
--------------------------------------------------------------------------------------------
Sort Join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.
🤨 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 |
--------------------------------------------------------------------------------------------
Window Sort는 윈도우 함수(=분석함수)를 수행할 때 나타난다.
2. 소트가 발생하지 않도록 SQL 작성
2.1 Union VS Union All
- Union: 사용 시 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하기 위해 소트 작업이 수행된다.
- Union All: 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다.
▶️ 될 수 있으면 Union All을 사용하는 것이 좋으며 Union을 Union All로 변경하는 것에는 결과 집합이 달라질 수 있기 때문에 주의해야한다.
결제번호가 PK인 테이블에 아래와 같은 데이터가 존재할 때 Union/Union All 사용 예시를 살펴보자
| 결제번호 | 결제수단코드 | 주문번호 | 결제금액 | 결제일자 | 주문일자 |
| 1 | M | 2153 | 24000 | … | … |
| 2 | M | 3525 | 30000 | … | … |
| 3 | M | 5486 | 5000 | … | … |
| 4 | C | 5486 | 15000 | … | … |
| 5 | C | 8216 | 12000 | … | … |
| 6 | C | 8783 | 26000 | … | … |
1. 상호 배타적 집합에 Union 사용
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 결제수단코드 = 'M' and 결제일자 = '20180316'
union
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 결제수단코드 = 'C' and 결제일자 = '20180316'
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=106)
1 0 SORT (UNIQUE) (Cost=4 Card=2 Bytes=106)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=1 ...)
4 3 INDEX (RANGE SCAN) OF '결제_N1' (INDEX) (Cost=1 Card=1)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=1 ...)
6 5 INDEX (RANGE SCAN) OF '결제_N1' (INDEX) (Cost=1 Card=1)
위 SQL은 결제 수단코드 조건이 상단과 하단이 각각 'M'과 'C'로 서로 달라 상호배타적인 집합이므로 중복 가능성이 없는데도 불구하고 Union을 사용하여 소트연산이 발생하고 있다. 이 경우 Union 대신 Union All을 사용하는것이 좋다.
2. 인스턴스 중복 가능성이 있는 집합
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 결제일자 = '20180316'
union
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 주문일자 = '20180316'
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=106)
1 0 SORT (UNIQUE) (Cost=2 Card=2 Bytes=106)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 ...)
4 3 INDEX (RANGE SCAN) OF '결제_N2' (INDEX) (Cost=0 Card=1)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 ...)
6 5 INDEX (RANGE SCAN) OF '결제_N3' (INDEX) (Cost=0 Card=1)
위 SQL은 상단과 하단이 각각 결제일자와 주문일자 조건으로 조회해 상호배타적 조건이 아니다. 그러므로 Union을 Union All로 변경하면 결제일자와 주문일자가 같은 데이터의 중복이 발생할 수 있다.
이 때 소트 연산과 중복을 피하기 위해 아래와 같이 작성하면 된다.
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 결제일자 = '20180316'
union
select 결제번호, 주문번호, 결제금액, 주문일자 ...
from 결제
where 주문일자 = '20180316'
and 결제일자 <> '20180316'
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=106)
1 0 UNION-ALL
2 1 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 Card=1...)
3 2 INDEX (RANGE SCAN) OF '결제_N2' (INDEX) (Cost=0 Card=1)
4 1 TABLE ACCESS (BY INDEX ROWID) OF '결제' (TABLE) (Cost=0 Card=1...)
5 4 INDEX (RANGE SCAN) OF '결제_N3' (INDEX) (Cost=0 Card=1)⭐ 결제일자가 Null 허용 컬럼일 때는 하단 조건절을 다음과 같이 변경하면 된다.
▶️ and (결제일자 <> '20180316' or 결제일자 is null)
▶️ LNNVL(결제일자 = '20180316')
2.2 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
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=80)
1 0 HASH (UNIQUE) (Cost=3 Card=1 Bytes=80)
2 1 FILTER
3 2 NESTED LOOPS
4 3 NESTED LOOPS (Cost=2 Card=1 Bytes=80)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1...)
6 5 INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=1 Card=1)
7 4 INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=1 Card=1)
8 3 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE) (Cost=1...)
이 때 가능하다면 존재 여부만 확인하고 조건절을 만족하는 데이터를 모두 읽지 않는 Exists 서브쿼리를 사용하면 좋다.
Exists 를 사용하면 Distinct 연산자를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능하다.
Distinct, Minus 연산자를 사용한 쿼리는 대부분 Exists 서브쿼리로 변환 가능하다.
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
)
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=80)
1 0 FILTER
2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=80)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1 Card=1...)
4 3 INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=1 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE) (Cost=1 Card=1...)
6 5 INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=1 Card=1)2.3 조인 방식 변경
조인문의 경우 인덱스를 이용해 소트연산을 생략하기 위해서 조인 방식도 잘 선택해 주어야 한다.
예를들어 아래 SQL 문에서 계약_X01 인덱스가 [지점ID + 계약일시] 순이면 소트연산이 생략되지만 해시 조인이기 때문에 Sort Order By가 나타났다.
select c.계약번호, c.상품코드, p.상품명, p.상품구분코드, c.계약일시, c.계약금액
from 계약 c, 상품 p
where c.지점ID = :brch_id
and p.상품코드 = c.상품코드
order by c.계약일시 desc
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 SORT (ORDER BY)
2 1 HASH JOIN
3 2 TABLE ACCESS (FULL) OF '상품' (TABLE)
4 2 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE)
5 4 INDEX (RANGE SCAN) OF '계약_X01' (INDEX)
이때 계약 테이블 기준으로 상품 테이블과 NL 조인하도록 조인 방식을 변경하면 소트 연산이 생략 가능하게 되어 지점ID 조건을 만족하는 데이터가 많고 부분범위 처리가 가능한 상황에서 큰 성능 개선 효과를 얻을 수 있다.
또한 정렬 기준이 조인 키 컬럼이라면 소트 머지 조인도 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
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE)
4 3 INDEX (RANGE SCAN DESCENDING) OF '계약_X01' (INDEX)
5 2 INDEX (UNIQUE SCAN) OF '상품_PK' (INDEX (UNIQUE))
6 1 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE)5.3 인덱스를 이용한 소트 연산 생략
3.1 Sort Order By 생략
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
order by 거래일시
---------------------------------------------------------------------------------------
| ID | Operation | Name | Rows | Bytes | Cost (%CPU) |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 3515K| 2041 (1)|
| 1 | SORT ORDER BY | | 40000 | 3515K| 2041 (1)|
| 2 | TABLE ACCESS BY INDEX ROWID | 종목 | 40000 | 3515K| 1210 (1)|
| * 3 | INDEX RANGE SCAN | 종목거래_N1 | 40000 | 3515K| 96 (2)|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
2 - access("종목코드"='KR123456')
위의 SQL에서 인덱스가 [종목코드 + 거래일시] 순이 아니라면 SORT ORDER BY 가 생략되지 않아 종목코드 = 'KR123456' 조건을 만족하는 모든 데이터를 다 읽어 거래일시 순으로 정렬을 마치고서야 출력을 시작하므로 OLTP 같은 환경에서 빠른 응답속도를 내기 어렵다.
만약 인덱스 선두컬럼을 [종목코드 + 거래일시] 순으로 구성한다면 소트연산 생략이 가능하다.
-----------------------------------------------------------------------------------------
| ID | Operation | Name | Rows | Bytes | Cost (%CPU) |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 3515K| 1372 (1) |
| 1 | TABLE ACCESS BY INDEX ROWID | 종목 | 40000 | 3515K| 1372 (1) |
| * 2 | INDEX RANGE SCAN | 종목거래_PK | 40000 | | 258 (2) |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
2 - access("종목코드"='KR123456')
소트 연산을 생략함으로써 전체 레코드를 읽지 않고도 결과집합을 바로 출력하는 부분범위 처리가 가능해진다.
⭐ 부분범위 처리는 쿼리 수행 결과 중 앞쪽 일부를 우선 전송하고 멈추었다가 클라이언트가 추가 전송을 요청할 때마다 남은 데이터를 조금씩 나눠 전송하는 방식으로 클라이언트와 DB 서버 사이에 WAS, AP 서버 등이 존재하고 수많은 클라이언트가 해당 서버 리소스(DB 커넥션)을 공유하는 환경인 3-Tier 아키텍처에서는 쿼리 집합을 조금씩 나눠서 전송하는 방식을 사용할 수 없다. 대신 Top N 쿼리를 사용하면 부분범위 처리가 가능해진다.
3.2 Top N 쿼리
Top N 쿼리: 전체 결과 집합 중 상위 N 레코드만 선택하는 쿼리이다.
/* SQL Server 또는 Sysbase Top N 방식 */
select TOP 10 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시;
/* IBM DB2 Top N 방식 */
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시
FETCH FIRST 10 ROWS ONLY;
/* 오라클 Top N 방식 */
select *
from (
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시
)
where rownum <= 10
위처럼 인라인 뷰로 정렬한 중간 집합을 만들었을 때 소트를 생략할 수 있도록 인덱스를 [종목코드 + 거래일시] 순으로 구성해준다면 옵티마이저는 소트를 생략하며 인덱스를 스캔하다 열 개 레코드를 읽는 순간 바로 멈춘다.
Execution Plan
----------------------------------------------------------------------------------------
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))
Top N 쿼리의 실행계획을 보면 Sort Order By 오퍼레이션이 생략된 것을 확인할 수 있으며 대신 COUNT (STOPKEY) 존재하는데 이는 조건절에 부합하는 레코드가 많아도 그중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 멈춘다는 뜻이다. ➡️ Top N Stopkey 알고리즘
3Tier 환경에서는 이 Top N 쿼리를 활용해 페이징 처리에 사용한다.
select *
from (
select rownum no, a.*
from (
/* SQL Body */
order by ...
) a
where rownum <= (:page * 10)
)
where no >= (:page-1) * 10 + 1
Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=756 Bytes=126K)
1 0 FILTER
2 1 VIEW (Cost=16 Card=756 Byte=126K)
3 2 COUNT (STOPKEY) -> NO SORT + STOPKEY
4 3 VIEW (Cost=16 Card=756 Bytes=117K)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '종목거래' (TABLE) (Cost=16 ...)
6 5 INDEX (RANGE SCAN) OF '종목거래_PK' (INDEX) (Cost=4 Card=303)
중요한 점은 부분 범위 처리 가능하도록 SQL을 작성하기 위해서는 1️⃣인덱스를 사용 가능하도록 조건절을 구사하고, 2️⃣조인은 NL 조인 위주로 처리하고, 3️⃣Order By 절이 있어도 소트 연산을 생략할 수 있도록 인덱스를 구성해 주어야 한다.
⭐ 페이징 처리 ANTI 패턴
쿼리를 간결하게 표현하고 싶다고 아래처럼 order by 바로 아래의 rownum 을 제거하고 가장 바깥의 조건절에서 다음과같이 페이징 처리를 하게 된다면 인덱스로 인해 소트 연산은 생략 되었겠지만 'Top N Stopkey' 알고리즘이 작동하지 않게 되므로 주의해야한다.
select *
from (
select rownum no, a.*
from (
/* SQL Body */
order by ...
) a
)
where no between (:page-1) * 10 + 1 and (:page * 10)
Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=756 Bytes=126K)
1 0 FILTER
2 1 VIEW (Cost=16 Card=756 Byte=126K)
3 2 COUNT -> NO SORT + NO STOP
4 3 VIEW (Cost=16 Card=756 Bytes=117K)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '종목거래' (TABLE) (Cost=16 ...)
6 5 INDEX (RANGE SCAN) OF '종목거래_PK' (INDEX) (Cost=4 Card=303)
3.3 최소값/최대값 구하기
최소값(MIN)/최대값(MAX)을 구하는 SQL 은 전체 데이터를 정렬하지는 않지만 전체 데이터를 읽으면서 값을 비교하기 때문에 실행계획에서 Sort Aggregate 오퍼레이션이 나타난다.
SELECT MAX(SAL) FROM EMP;
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
1 0 SORT (AGGREGATE) (Card=1 Bytes=4)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)
인덱스는 정렬되어 있으므로 전체 데이터를 읽지 않고도 맨 왼쪽 또는 맨 오른쪽의 첫번째 값을 읽어 최소/최대값을 쉽게 찾을 수 있다.
아래 SQL을 보면 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함되어 있고, 인덱스 선두 컬럼이 모두 '=' 조건이면 이 조건을 모두 만족하는 범위(Range) 가장 오른쪽에 읽는 값 하나를 찾았을 때 멈춘다. ➡️ First Row Stopkey 알고리즘
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 (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE) (Card=1 Bytes=8)
2 1 FIRST ROW (Cost=1 Card=1 Bytes=8)
3 2 INDEX (RANGE SCAN (MIN/MAX)) OF 'EMP_X1' (INDEX) (Cost=1 Card=1 ...)
위 SQL에서 인덱스를 다시 [DEPTNO + SAL + MGR] 순으로 구성하게 되면 DEPTNO = 30 조건을 만족하는 범위 가장 오른쪽으로 내려가면서 가장 큰 SAL 값을 읽어나가고 거기서부터 MGR = 7698 조건을 만족하는 레코드 하나를 찾았을 때 멈추게 된다. 즉 DEPTNO는 액세스 조건, MGR은 필터조건이다. 이 경우도 조건절 컬럼과 MAX 컬럼이 모두 인덱스므로 'First Row Stopkey' 알고리즘 이다.
또 위 SQL에서 또 다시 인덱스를 [SAL + DEPTNO + MGR] 순으로 구성하게 된다면 조건절 컬럼이 둘다 인덱스 선두컬럼이 아니므로 Index Range Scan은 불가능하다. Index Full Scan 방식으로 인덱스 전체 레코드 중 가장 오른쪽에서 스캔을 시작해 DEPT_NO = 30 이며 MGR = 7698인 조건을 만족하는 레코드 하나를 찾았을 때 멈춘다. DEPT_NO, MGR 둘다 필터조건이며, 조건절 컬럼과 MAX 컬럼이 모두 인덱스이므로 역시 'First Row Stopkey' 알고리즘이 작동한다.
CREATE INDEX EMP_X1 ON EMP(SAL, DEPTNO, MGR);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE) (Card=1 Bytes=8)
2 1 FIRST ROW (Cost=1 Card=1 Bytes=8)
3 2 INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_X1' (INDEX) (Cost=1 Card=1 ...)
만약 조건절 컬럼과 MAX 컬럼 중 어느 하나가 인덱스에 포함되어있지 않은 경우엔 'First Row Stopkey' 알고리즘은 작동하지 않는다.
CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=8)
1 0 SORT (AGGREGATE) (Card=1 Bytes=8)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=8)
3 2 INDEX (RANGE SCAN (MIN/MAX)) OF 'EMP_X1' (INDEX) (Cost=1 Card=5)
⭐ Top N 쿼리를 이용한 최소/최대값 구하기
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;
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=2 Card=1 Bytes=13)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 ...)
4 3 INDEX (RANGE SCAN DESENDING) OF 'EMP_X1' (INDEX) (Cost=1 Card=5)
Top N 쿼리에 작동하는 'Top N Stopkey'알고리즘은 모든 컬럼이 인덱스에 포함되어있지 않아도 잘 작동하며 deptno = 30 조건을 만족하는 가장 오른쪽부터 역순으로 스캔하면서 테이블을 액세스하다 MGR = 7698 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다.
인라인 뷰 사용시 쿼리가 좀 더 복잡해보일 수 있지만, 성능 측면에서는 MIN/MAX 쿼리보다 낫다.
3.4 이력조회
▶️ 가장 단순한 이력조회
이력 데이터 조회 시 'First Row Stopkey' 또는 'Top N Stopkey' 알고리즘이 작동할 수 있도록 인덱스를 설계 및 SQL을 구현해야 한다.
아래 SQL 문에서는 상태변경이력_PK 인덱스가 [장비번호+ 변경일자 + 변경순번] 순으로 구성되어 있어 'First Row Stopkey' 알고리즘이 작동했다.
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 AGGREGATE | | 10 | 10 | 22 |
| 2 | FIRST ROW | | 10 | 10 | 22 |
| 3 | INDEX RANGE SCAN (MIN/MAX) | 상태변경이력_PK | 10 | 10 | 22 |
| 4 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 | 10 | 4 |
| 5 | INDEX RANGE SCAN | 장비_N1 | 1 | 10 | 2 |
--------------------------------------------------------------------------------------
▶️ 점점 복잡해지는 이력 조회
만약 최종 변경 순번까지 이력테이블에서 읽는다면 보통 아래와 같이 쿼리를 작성한다.
SELECT 장비번호, 장비명, 상태코드, SUBSTR(최종이력, 1, 8) 최종변경일자,
TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
FROM (
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(H.변경일자 || LPAD(H.변경순번, 4))
FROM 상태변경이력 H
WHERE H.장비번호 = P.장비번호) 최종변경일자
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 |
| 1 | SORT AGGREGATE | | 10 | 10 | 6380 |
| 2 | INDEX RANGE SCAN | 상태변경이력_PK | 10 | 1825K | 6380 |
| 3 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 | 10 | 4 |
| 4 | INDEX RANGE SCAN | 장비_N1 | 1 | 10 | 2 |
--------------------------------------------------------------------------------------
하지만 위 SQL은 인덱스를 가공하여 'First Row Stopkey' 알고리즘이 작동하지 않으며 장비별 상태변경이력이 많아지면 성능에 문제가 될 수 있어 쿼리가 복잡해지는 단점을 감수한다면 다음과 같이 쿼리하기도 한다.
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(H.변경일자) FROM 상태변경이력 H WHERE 장비번호 = P.장비번호) 최종변경일자
, (SELECT MAX(H.변경순번)
FROM 상태변경이력 H
WHERE 장비번호 = P.장비번호
AND 변경일자 = (SELECT MAX(H.변경일자)
FROM 상태변경이력 H
WHERE 장비번호 = P.장비번호)) 최종변경순번
FROM 장비 P
WHERE 장비구분코드 = 'A001'
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 |
| 1 | SORT AGGREGATE | | 10 | 10 | 22 |
| 2 | FIRST ROW | | 10 | 10 | 22 |
| 3 | INDEX RANGE SCAN (MIN/MAX) | 상태변경이력_PK | 10 | 10 | 22 |
| 4 | SORT AGGREGATE | | 10 | 10 | 47 |
| 5 | INDEX RANGE SCAN | 상태변경이력_PK | 10 | 1000 | 47 |
| 6 | SORT AGGREGATE | | 10 | 10 | 22 |
| 7 | FIRST ROW | | 10 | 10 | 22 |
| 8 | INDEX RANGE SCAN (MIN/MAX) | 상태변경이력_PK | 10 | 10 | 22 |
| 9 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 | 10 | 4 |
| 10 | INDEX RANGE SCAN | 장비_N1 | 1 | 10 | 2 |
--------------------------------------------------------------------------------------
하지만 이것도 이력테이블에서 읽어야 할 컬럼이 많아지면 위 쿼리보다 배는 더 복잡해지게된다..
▶️INDEX_DESC 힌트 활용
단순하면서 성능을 높이기 위해 아래와 같이 인덱스를 역순으로 읽도록 index_desc 힌트를 사용하며, 첫 번째 레코드에서 바로 멈추도록 rownum <= 1 조건절을 사용하기도 한다.
SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비, 장비명
, (SELECT /*+ INDEX_DESC(X 상태변경이력_PK) */
변경일자|| LPAD(변경순번, 4) || 상태코드
FROM 상태변경이력 X
WHERE 장비번호 = P.장비번호
AND ROWNUM <= 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 |
| 1 | COUNT STOPKEY | | 10 | 10 | 41 |
| 2 | TABLE ACCESS BY INDEX ROWID | 상태변경이력_PK | 10 | 10 | 41 |
| 3 | INDEX RANGE SCAN DESENDING | 상태변경이력_PK | 10 | 10 | 30 |
| 4 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 | 10 | 4 |
| 5 | INDEX RANGE SCAN | 장비_N1 | 1 | 10 | 2 |
--------------------------------------------------------------------------------------
이 방식이 성능은 좋지만 인덱스 구성이 완벽해야만 쿼리가 잘 작동하므로 다른 대안이 있다면 그 방법을 사용하는 것이 바람직하다.
▶️ 11g/12c 신기능 활용
예전에는 다음과 같이 메인 쿼리 컬럼을 서브쿼리 인라인 뷰에서 참조하게되면 ORA-00904(부적합한 식별자) 오류가 발생했지만 11g 부터는 인라인뷰에서 메인 쿼리 컬럼을 참조할 수 있게 되었다.
SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
, (SELECT 변경일자|| LPAD(변경순번, 4) || 상태코드
FROM (SELECT 장비번호, 변경일자, 변경순번, 상태코드
FROM 상태변경이력
ORDER BY 변경일자 DESC, 변경순번 DESC)
WHERE 장비번호 = P.장비번호
AND ROWNUM <= 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 |
| 1 | COUNT STOPKEY | | 10 | 10 | 40 |
| 2 | VIEW | | 10 | 10 | 40 |
| 2 | TABLE ACCESS BY INDEX ROWID | 상태변경이력_PK | 10 | 10 | 40 |
| 3 | INDEX RANGE SCAN DESENDING | 상태변경이력_PK | 10 | 10 | 30 |
| 4 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 | 10 | 4 |
| 5 | INDEX RANGE SCAN | 장비_N1 | 1 | 10 | 2 |
--------------------------------------------------------------------------------------
위 쿼리는 [장비번호 = P.장비번호] 조건절이 인라인 뷰 안으로 파고들어 'Predicate Pushing' 이라고 불리는 쿼리 변환이 작동한다. 이 방식 사용 시 인덱스 구성이 변경되었을 때 'Top N Stopkey' 알고리즘이 작동하지 않아 성능이 느려질 수 있지만 쿼리 결과집합은 보장된다.
오라클 12c 버전부터는 아래와 같은 패턴도 SQL 파싱 오류 없이 'Top N Stopkey'알고리즘이 작동한다.
SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
, (SELECT 변경일자|| LPAD(변경순번, 4) || 상태코드
FROM (SELECT 변경일자, 변경순번, 상태코드
FROM 상태변경이력
WHERE 장비번호 = P.장비번호
ORDER BY 변경일자 DESC, 변경순번 DESC)
WHERE ROWNUM <= 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
▶️ 윈도우 함수와 Row Limiting 절
(1) 이력조회
이력조회 서브쿼리에 아래와 같이 윈도우 함수를 사용할 수 있지만 'Top N Stopkey' 알고리즘이 작동하지 않는다. 따라서 인덱스로 소트를 생략할 수 있을 때 사용해선 안된다.
SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
, (SELECT 변경일자|| LPAD(변경순번, 4) || 상태코드
FROM (SELECT 변경일자, 변경순번, 상태코드
, ROW_NUMBER() OVER (ORDER BY 변경일자 DESC, 변경순번 DESC) NO
FROM 상태변경이력
WHERE 장비번호 = P.장비번호)
WHERE NO = 1) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
오라클 12c부터 Row Limiting 절을 이용해 아래와 같이 구현할 수 있게 되었지만 역시나 'Top N Stopkey' 알고리즘이 작동하지 않는다. 참고로 Row Limiting 절을 사용하면 위의 sql 와 똑같이 윈도우 함수를 사용하는 형태로 옵티마이저가 쿼리를 변환한다.
SELECT 장비번호, 장비명, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번, SUBSTR(최종이력, 13) 최종상태코드
FROM (
SELECT 장비번호, 장비명
, (SELECT 변경일자|| LPAD(변경순번, 4) || 상태코드
FROM 상태변경이력
WHERE 장비번호 = P.장비번호
ORDER BY 변경일자 DESC, 변경순번 DESC
FETCH FIRST 1 ROWS ONLY
) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
(2) 페이징 처리
아래와 같이 윈도우 함수를 이용해 페이징 처리를 활용하면 'Top N Stopkey' 알고리즘이 작동할 수도 있지만 카디널리티와 비용 계산이 불완전함으로 인해 소트를 생략할 수 있는데도 인덱스를 사용하지 않는 경우가 자주 발생해 index/index_desc 힌트를 써야 할 일이 자주 생길 수 있다.
SELECT 변경일자, 변경순번, 상태코드
FROM (
SELECT 변경일자, 변경순번, 상태코드
, ROW_NUMBER() OVER (ORDER BY 변경일자, 변경순번) NO
FROM 상태변경이력
WHERE 장비번호 = :eqp_no)
WHERE NO BETWEEN 1 AND 10
소트 생략 가능한 인덱스가없어 'Top N 소트' 알고리즘이 작동할 때 기존 Top N 쿼리보다 윈도우 함수가 소트 공간(Sort Area, Temp 세그먼트)을 더 많이 사용하는 단점이 있다.
오라클 12c 이후 아래와 같이 Row Limiting 절을 사용할 수 있지만 옵티마이저가 쿼리를 변환해 윈도우 함수를 사용하는 것과 같아진다.
SELECT 변경일자, 변경순번, 상태코드
FROM (
SELECT ROWNUM NO, 변경일자, 변경순번, 상태코드
FROM (
SELECT 변경일자, 변경순번, 상태코드
FROM 상태변경이력
WHERE 장비번호 = :eqp_no
ORDER BY 변경일자, 변경순번
FETCH FIRST 10 ROWS ONLY)
)
WHERE NO >= 1
▶️ 상황에 따라 달라져야 하는 이력 조회 패턴
전체 또는 상당히 많은 장비의 이력을 조회할 때는 인덱스를 활용해 Stopkey 기능을 작동하게 하더라도 랜덤 I/O 발생량만큼 성능도 비례해서 느려지므로 대량 데이터 조회 시 결코 좋은 솔루션이 되지 못한다.
이 때 아래와 같이 윈도우 함수를 이용하면 효과적일 수 있다.
SELECT P.장비번호, P.장비명, H.변경일자 AS 최종변경일자
, H.변경순번 AS 최종변경순번, H.상태코드 AS 최종상태코드
FROM 장비 P, (SELECT 장비번호, 변경일자, 변경순번, 상태코드,
ROW_NUMBER() OVER (PARTITION BY 장비번호 ORDER BY 변경일자 DESC, 변경순번 DESC) RNUM
FROM 상태변경이력) H
WHERE H.장비번호 = P.장비번호
AND H.RNUM = 1;
위 쿼리로 조회 시 상태변경이력 테이블을 Full Scan과 해시 조인을 이용하므로 오랜 과거 이력까지 모두 읽지만 인덱스를 이용한 방식보다 빠르다. 아래와 같이 KEEP 절을 활용할 수도 있다.
SELECT P.장비번호, P.장비명, H.변경일자 AS 최종변경일자
, H.변경순번 AS 최종변경순번, H.상태코드 AS 최종상태코드
FROM 장비 P, (SELECT 장비번호, MAX(변경일자) 변경일자
, MAX(변경순번) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 변경순번
, MAX(상태코드) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 상태코드
FROM 상태변경이력
GROUP BY 장비번호) H
WHERE H.장비번호 = P.장비번호
▶️ 선분 이력 맛보기
업무 특성에 따라 선분이력 모델도 고려할 만하다. 선분이력 모델 채택 시 쿼리가 간단해지고, 성능 측면에서도 이점이 생긴다.
SELECT P.장비번호, P.장비명, H.상태코드, H.유효시작일자, H.유효종료일자, H.변경순번
FROM 장비 P, 상태변경이력 H
WHERE P.장비구분코드 = 'A001'
AND H.장비번호 = P.장비번호
AND H.유효종료일자 = '99991231'
-- 또는 --
SELECT P.장비번호, P.장비명, H.상태코드, H.유효시작일자, H.유효종료일자, H.변경순번
FROM 장비 P, 상태변경이력 H
WHERE P.장비구분코드 = 'A001'
AND H.장비번호 = P.장비번호
AND :BASE_DT BTWEEN H.유효시작일자 AND H.유효종료일자
3.5 Sort Group By 생략
그룹핑 연산에서도 인덱스를 활용하면 Sort Group By 연산을 생략할 수 있다. 실행계획에는 Sort Group By Nosort 라고 표시된다.
인덱스를 활용해 NoSort 방식으로 Group By를 처리하면 부분범위 처리도 가능해진다.
SELECT REGION, AVG(AGE), COUNT(*)
FROM CUSTOMER
GROUP BY REGION
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Buffers |
----------------------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------------------
4 Sort Area를 적게 사용하도록 SQL 작성
소트 연산이 불가피할 때 메모리 내에서 처리를 완료할 수 있도록 해야하며, Sort Area를 적게 사용할 방법을 찾아야한다.
4.1 소트 데이터 줄이기
예시 1️⃣
[1]
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]
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 상품번호
)
위위 두개의 SQL 중 어느쪽이 Sort Area를 더 적게 사용하는지 보면 1번 SQL은 레코드당 107(=30+30+10+20+17) 바이트로 가공한 결과집합을 Sort Area에 담고, 2번은 SQL은 가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력 시 가공하므로 2번 SQL이 Sort Area를 더 적게 사용한다.
예시 2️⃣
[1]
select *
from 예수금원장
order by 총예수금 desc
[2]
select 계좌번호, 총예수금
from 예수금원장
order by 총예수금 desc
두개 SQL 중 어느쪽이 Sort Area를 더 적게사용하는지 보면 이번도 2번이 적게사용한다. 1번 SQL은 모든 컬럼을 Sort Area에 저장하는 반면, 2번 SQL은 계좌번호, 총예수금만 저장하기 때문이다.
4.2 Top N 쿼리의 소트 부하 경감 원리
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
Call Count CPU Time Elapsed Time Disk Query Current Rows
-------- ------- ---------- -------------- ------ ------- --------- ------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.083 0 690 0 10
-------- ------- ---------- -------------- ------ ------- --------- ------
Total 4 0.078 0.084 0 690 0 10
Rows Row Source Operation
---- ---------------------------------------------------------------------
0 STATEMENT
10 COUNT STOPKEY (cr=690 pr=0 pw=0 time=83318 us)
10 VIEW (cr=690 pr=0 pw=0 time=83290 us)
10 SORT ORDER BY STOPKEY (cr=690 pr=0 pw=0 time=83264 us)
49857 TABLE ACCESS FULL 종목거래(cr=690 pr=0 pw=0 time=299191 us)
위의 페이징 쿼리를 실행 할 때 인덱스로 소트 연산을 생략할 수 없어 Table Full Scan 방식으로 처리된다면 실행계획에는 Sort Order By 오퍼레이션이 나타난다. 여기서 Sort Order By 옆에 'Stopkey' 가 표시되는데 Sort Order By 오퍼레이션은 피할 수 없지만 'Top N 소트' 알고리즘이 작동한다는 표시로 소트 연산(= 값 비교) 횟수와 Sort Area 사용량을 최소해 준다. 예를 들어 위 쿼리에서 :page 변수에 1을 입력하면 열 개 원소를 담을 배열(Array)공간만 있으면 된다. (더보기 참고)
1️⃣ 처음 읽은 열 개의 레코드를 거래일시 오름차순(ASC)으로 정렬해서 배열에 담는다.
2️⃣ 이후 읽는 레코드에 대해서는 배열 맨 끝(큰 쪽 끝)에 있는 값과 비교해서 그보다 작은 값이 나타날 때만 배열내에서 다시 정렬하고, 기존 맨 끝에 있던 값은 버린다. (반복)
이 방식으로 처리 시 대상 집합이 아무리 커도 많은 메모리 공간을 필요로 하지 않으며 이것이 'Top N 소트' 알고리즘이 소트 연산 횟수와 Sort Area 사용량을 줄여주는 원리이다.
이때 AutoTrace 결과를 살펴보면 Physical Read(=pr)과 Physical Write(=pw)가 전혀 발생하지 않은 사실을 알 수 있다.
Statistics
---------------------------
0 recursive call
0 db block gets
690 consistent gets
0 physical reads
... ...
1 sorts (memory)
0 sorts (disk)
4.3 Top N 쿼리가 아닐 때 발생하는 소트 부하
select *
from (
select rownum no, a.*
from (
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시
) a
)
where no between (:page-1) * 10 + 1 and (:page * 10)
Call Count CPU Time Elapsed Time Disk Query Current Rows
-------- ------- ---------- -------------- ------ ------- --------- ------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.281 0.858 698 690 14 10
-------- ------- ---------- -------------- ------ ------- --------- ------
Total 4 0.281 0.858 698 690 14 10
Rows Row Source Operation
---- ---------------------------------------------------------------------
0 STATEMENT
10 VIEW (cr=690 pr=698 pw=698 time=357962 us)
49857 COUNT (cr=690 pr=698 pw=698 time=1604327 us)
49857 VIEW (cr=690 pr=698 pw=698 time=1205452 us)
49857 SORT ORDER BY (cr=690 pr=698 pw=698 time=756723 us) => Stopkey 사라짐
49857 TABLE ACCESS FULL 종목거래(cr=690 pr=0 pw=0 time=249345 us)
SQL을 더 간결하게 표현하겠다고 위와 같이 Order By 아래쪽 ROWNUM 조건절을 제거하면 실행계획에서 Sort Order By 오퍼레이션에 Stopkey가 사라지며 'Top N 소트' 알고리즘이 작동하지 않는다.
또한 Physical Read(pr=698)와 Physical Write(pw=698)이 발생했다.(디스크 이용)
아래는 AutoTrace 결과로 sorts (disk) 항목이 1이므로 정렬과정에 Temp 테이블스페이스를 이용했다는 사실을 알 수 있다.
Statistics
---------------------------
6 recursive call
14 db block gets
690 consistent gets
698 physical reads
... ...
0 sorts (memory)
1 sorts (disk)4.4 분석함수에서의 Top N 소트
윈도우 함수중 rank나 row_number 함수는 Top N 소트 알고리즘이 작동하므로 max 함수보다 소트 부하가 적다.
/* MAX 함수를 사용 */
select 장비번호, 변경일자, 변경순번, 상태코드, 메모
from (select 장비번호, 변경일자, 변경순번, 상태코드, 메모
, max(변경순번) over(partition by 장비번호) 최종변경순번
from 상태변경이력
where 변경일자 = :upd_dt)
where 변경순번 = 최종변경순번
Call Count CPU Time Elapsed Time Disk Query Current Rows
-------- ------- ---------- -------------- ------ ------- --------- ------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 2.750 9.175 13456 4536 9 10
-------- ------- ---------- -------------- ------ ------- --------- ------
Total 4 2.750 9.175 13456 4536 9 10
Rows Row Source Operation
---- ---------------------------------------------------------------------
0 STATEMENT
10 VIEW (cr=4536 pr=13456 pw=8960 time=4437847 us)
498570 WINDOW SORT (cr=4536 pr=13456 pw=8960 time=9120662 us)
498570 TABLE ACCESS FULL 상태변경이력 (cr=4536 pr=0 pw=0 time=1994341 us)
/* RANK 함수를 사용 */
select 장비번호, 변경일자, 변경순번, 상태코드, 메모
from (select 장비번호, 변경일자, 변경순번, 상태코드, 메모
, rank() over(partition by 장비번호 order by 변경순번 desc) rnum
from 상태변경이력
where 변경일자 = :upd_dt)
where rnum = 1
Call Count CPU Time Elapsed Time Disk Query Current Rows
-------- ------- ---------- -------------- ------ ------- --------- ------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.969 1.062 40 4536 42 10
-------- ------- ---------- -------------- ------ ------- --------- ------
Total 4 0.969 1.062 40 4536 42 10
Rows Row Source Operation
---- ---------------------------------------------------------------------
0 STATEMENT
10 VIEW (cr=4536 pr=40 pw=40 time=1061996 us)
498570 WINDOW SORT PUSHED RANK (cr=4536 pr=40 pw=40 time=1061971 us)
498570 TABLE ACCESS FULL 상태변경이력 (cr=4536 pr=0 pw=0 time=1495760 us)
max 함수 사용시 Window Sort 단계에서 Physical Read가 13,456개 Physical Write가 8,960개 발생했지만 rank 함수 사용 시 Physical Read가 40개, Physical Write가 40개로 max 함수를 쓸 때보다 훨씬 줄었다. 시간도 8초가량 덜 소요된 것을 확인할 수 있다.
'개발 독서 스터디 > 친절한 SQL 튜닝' 카테고리의 다른 글
| 7회차 DML 튜닝 - 2 (1) | 2025.04.20 |
|---|---|
| 6회차 DML 튜닝 - 1 (0) | 2025.03.30 |
| 4회차 조인 튜닝 (0) | 2025.02.25 |
| 3회차 인덱스 튜닝 - 3 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 2 (0) | 2025.02.08 |
