분석함수 — MAX 집계를 RANK OVER 함수로 변환
같은 테이블을 MAX 집계용으로 한 번, 상세 조회용으로 한 번 두 번 access 하는 패턴은 분석함수
RANK() OVER로 한 번의 access 로 풀 수 있습니다. 사용하기에 앞서 PGA Sorting 부하 등 Trade off 에 대한 검토가 필요합니다.
핵심 정리
- 변환의 본질:
WHERE (key, max_col) IN (SELECT key, MAX(col) FROM same_table GROUP BY key)패턴은 같은 테이블을 두 번 access 합니다.RANK() OVER (PARTITION BY key ORDER BY col DESC)로 한 번의 풀 스캔에 RANK 매기고WHERE RNK = 1으로 필터하면 access 가 1 회로 줄어듭니다. - Trade-off: I/O (Buffers) 는 절감되지만 PGA SORT 사용량이 증가합니다. 데이터 볼륨이 클수록 SORT 부하가 커지므로 I/O 절감 vs PGA 부하 비교 후 판단해야 합니다.
- plan signature:
WINDOW SORT PUSHED RANK가 등장하면 옵티마이저가RNK = 1같은 외부 필터를 SORT 안쪽으로 push 하여 정렬 메모리를 일부 절감한 신호입니다.
RANK 함수의 3 가지 종류
분석함수 RANK 계열은 같은 ORDER BY 값에 대한 동률 처리 방식이 다릅니다.
| 함수 | 동률 처리 | 다음 등수 |
|---|---|---|
RANK | 같은 값은 같은 등수 | 동률 N 명 → 다음은 N + 1 등 (건너뜀) |
DENSE_RANK | 같은 값은 같은 등수 | 동률이어도 다음은 +1 등 (안 건너뜀) |
ROW_NUMBER | 동률 무시 | 항상 단일 등수 (1, 2, 3, …) |
예시: [100, 100, 90, 80] 에 대해
| 1번 row | 2번 row | 3번 row | 4번 row | |
|---|---|---|---|---|
RANK | 1 | 1 | 3 | 4 |
DENSE_RANK | 1 | 1 | 2 | 3 |
ROW_NUMBER | 1 | 2 | 3 | 4 |
ROW_NUMBER 는 동률이라도 임의 순서로 1 명만 반환하므로, 정확한 결정성이 필요하면 ORDER BY 에 tie-breaker 컬럼을 추가합니다.
본 글의 시나리오는 “고객별 가장 최근 주문” 을 찾는 것이므로 동률 (같은 날 여러 주문) 도 모두 포함하는 의미라면 RANK, 한 명만 임의로 뽑는다면 ROW_NUMBER. 본 케이스에서는 RANK 사용.
시나리오
ORDERS (3,000,000 건, 19,791 블록) 에서 고객별 가장 최근 주문 (2012 년 한 해) 을 추출하는 쿼리입니다.
| 테이블 | 건수 | 인덱스 |
|---|---|---|
ORDERS | 3,000,000 (2007 ~ 2012) | IX_ORDERS_N1 (ORDER_DATE) |
2012 년 한 해의 데이터 = 약 500K 건. 고객 수 (distinct CUSTOMER_ID) = 약 50K. 각 고객별 가장 최근 1 건 = 49,999 건이 결과.
1) 원본 — MAX 집계 + IN 서브쿼리 (테이블 2 회 access)
1
2
3
4
5
6
7
8
9
10
11
SELECT ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID,
ORDER_MODE, ORDER_STATUS, ORDER_TOTAL
FROM ORDERS
WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND ORDER_DATE < TO_DATE('20130101', 'YYYYMMDD')
AND (CUSTOMER_ID, ORDER_DATE) IN (
SELECT CUSTOMER_ID, MAX(ORDER_DATE) ORDER_DATE
FROM ORDERS A
WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND ORDER_DATE < TO_DATE('20130101', 'YYYYMMDD')
GROUP BY CUSTOMER_ID);
서브쿼리에서 ORDERS 를 풀 스캔하여 고객별 MAX(ORDER_DATE) 를 구하고, 그 결과를 메인 쿼리와 다시 join 하면서 같은 ORDERS 의 같은 구간을 인덱스로 다시 access 합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49999 | 171K | |
| 1 | NESTED LOOPS | | 1 | 49999 | 171K | |
| 2 | NESTED LOOPS | | 1 | 50818 | 120K | |
| 3 | VIEW | VW_NSO_1 | 1 | 49999 | 19612 | |
|* 4 | FILTER | | 1 | 49999 | 19612 | |
| 5 | HASH GROUP BY | | 1 | 49999 | 19612 | 9670K(0) |
|* 6 | TABLE ACCESS FULL | ORDERS | 1 | 500K | 19612 | | ← 서브쿼리: ORDERS 풀 스캔
|* 7 | INDEX RANGE SCAN | IX_ORDERS_N1 | 49999 | 50818 | 101K | | ← 메인: 같은 구간 반복 access
|* 8 | TABLE ACCESS BY INDEX ROWID | ORDERS | 50818 | 49999 | 50688 | |
--------------------------------------------------------------------------------------------
핵심 비효율:
Id 6의 ORDERS 풀 스캔 (Buffers 19,612) 이 서브쿼리 부분입니다.Id 7의IX_ORDERS_N1RANGE SCAN 이 49,999 starts (Buffers 101K) 으로, 같은 구간을 인덱스로 49K 회 두드립니다.Id 8의 ROWID lookup 이 50,688 buffers 로 메인 테이블을 access 합니다.- 합계 171K Buffers — 같은 데이터를 두 번 access 하는 명백한 중복.
2) 수정 — RANK() OVER 분석함수 (1 회 access + WINDOW SORT)
1
2
3
4
5
6
7
8
9
SELECT ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID,
ORDER_MODE, ORDER_STATUS, ORDER_TOTAL
FROM (SELECT ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID,
ORDER_MODE, ORDER_STATUS, ORDER_TOTAL,
RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE DESC) RNK
FROM ORDERS A
WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND ORDER_DATE < TO_DATE('20130101', 'YYYYMMDD'))
WHERE RNK = 1;
ORDERS 를 한 번만 풀 스캔하면서 고객별로 ORDER_DATE 내림차순 RANK 를 매기고, inline view 바깥에서 RNK = 1 만 필터.
1
2
3
4
5
6
7
8
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49999 | 19620 | |
|* 1 | VIEW | | 1 | 49999 | 19620 | |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 500K | 19620 | 34M(0) | ← RANK 매기면서 RNK=1 필터를 SORT 안쪽으로 push
|* 3 | TABLE ACCESS FULL | ORDERS | 1 | 500K | 19620 | | ← ORDERS 풀 스캔 1 회
-----------------------------------------------------------------------------------
Id 3의 ORDERS 풀 스캔이 1 회만 발생 (Buffers 19,620).Id 2의WINDOW SORT PUSHED RANK가 본 변환의 signature 입니다. 외부의RNK = 1필터를 SORT 안쪽으로 push 하여 SORT 메모리를 일부 절감.- PGA SORT 사용량 34M 으로, 원본의
HASH GROUP BY 9670K보다 약 3.5 배 큰 메모리를 사용합니다. - Buffers
171K → 19,620(약 8.7 배 절감).
분석
WINDOW SORT PUSHED RANK 의 의미
일반 WINDOW SORT 는 모든 row 에 대해 RANK 를 계산한 뒤 outer 필터 (RNK = 1) 를 적용합니다. PUSHED RANK 가 추가되면 옵티마이저가 외부의 RNK = 1 조건을 SORT 안쪽에 일찍 적용하여, RANK 가 1 이 아닌 것으로 확정되는 row 는 더 이상 정렬 / 메모리에 유지하지 않습니다.
이 push down 은 옵티마이저가 자동 수행하며, RANK / DENSE_RANK 와 RNK <= N 또는 RNK = N 같은 단순 비교 조건이 함께 있을 때 적용됩니다.
I/O 절감 vs PGA 부하 — Trade-off
| 자원 | 원본 (MAX 서브쿼리) | 수정 (RANK OVER) |
|---|---|---|
| Buffers (I/O) | 171K | 19,620 (약 8.7 배 절감) |
| PGA SORT | HASH GROUP BY 9.7M | WINDOW SORT 34M (약 3.5 배 증가) |
| 결과 row | 49,999 | 49,999 |
본 시나리오에서는 I/O 절감이 PGA 증가를 충분히 상쇄하지만, 데이터 볼륨이 더 커지면 PGA 가 부족해 SORT 가 OnePass / MultiPass 로 떨어져 temp 사용으로 이어질 수 있습니다. 대용량 환경에서는 적용 전 V$SQL_WORKAREA 또는 Used-Mem 컬럼의 괄호 ((0) = Optimal, (1) = OnePass) 를 확인해야 합니다.
언제 어떤 RANK 함수를 써야 하나
| 상황 | 권장 함수 |
|---|---|
| 같은 점수 동률을 모두 가져옴 (포함) | RANK 또는 DENSE_RANK |
| 동률 무시하고 N 등 한 명만 | ROW_NUMBER (ORDER BY 에 tie-breaker 추가) |
| 등수 사이 gap 이 의미 있음 (실제 등수 표시) | RANK |
| 등수 사이 gap 없이 연속 (1, 2, 3, …) | DENSE_RANK |
| 페이징 / row 번호 매기기 | ROW_NUMBER |
본 글 시나리오는 “고객별 가장 최근 주문” 인데, 같은 날짜 여러 주문이 있으면 모두 포함하는 게 자연스러우므로 RANK 가 적합합니다. ROW_NUMBER 를 쓰면 동률 중 한 명만 임의로 선택되어 결과가 비결정적입니다.
같은 테이블을 집계용 + 상세 조회용 으로 두 번 access 하는 패턴이 보이면 분석함수 변환을 검토할 수 있습니다. I/O 절감이 일반적으로 큰 이득이지만 PGA SORT 부하 가 함께 늘어나므로, 대용량 환경에서는
Used-Mem컬럼의 Optimal 여부를 반드시 확인해야 합니다.
Buffers 비교
| 단계 | 원본 | 수정 |
|---|---|---|
| ORDERS 풀 스캔 (서브쿼리) | 19,612 | 0 |
| ORDERS 풀 스캔 (메인) | 0 | 19,620 |
| IX_ORDERS_N1 RANGE SCAN (49K 회) | 101K | 0 |
| ROWID lookup | 50,688 | 0 |
| 합계 | 171K | 19,620 |
정리
- MAX 집계 + IN 서브쿼리 + 같은 테이블 패턴은 분석함수
RANK() OVER (PARTITION BY ... ORDER BY ... DESC)+WHERE RNK = 1로 변환하면 테이블 access 가 2 회 → 1 회 로 줄어듭니다. - plan signature 는
WINDOW SORT PUSHED RANK입니다. 외부 RNK 필터가 SORT 안쪽으로 push 되어 정렬 메모리가 일부 절감된 신호입니다. - Trade-off 검토 필수: I/O (Buffers) 는 절감되지만 PGA SORT 부하가 증가합니다. 대용량 환경에서는 Optimal 여부 (
Used-Mem괄호의(0)) 를 반드시 확인해야 합니다.