Paging 처리할 때 Join 전 먼저 Paging 처리하여 Join 부하 감소
페이징 쿼리(
ROWNUM <= N) 에서 후행 테이블이 결과 건수에 영향을 미치지 않는다면, 선행 테이블에서 페이징을 먼저 수행한 뒤 후행 테이블을 join 하는 것이 압도적으로 빠릅니다.
핵심 정리
- 원리: 후행 테이블과 join 해도 결과 건수에 차이가 없다면, 선행 테이블에서 페이징(
ROWNUM <= N+STOPKEY) 을 먼저 수행할 수 있습니다. NL 조인 회수가 페이징 결과 건수만큼만 발생합니다. - 적용 가능 조건: 후행 테이블과의 join 이 (a) outer join 이거나 (b) PK / unique index 로 1:1 매칭되는 등 선행 row 수를 변화시키지 않는 형태여야 합니다. 이 조건 검증이 가장 중요합니다.
- 효과: 본 글 시나리오에서 NL 조인 5,432 회가 10 회로 줄고, Buffers 가 15,731 에서 1,204 로 약 13 배 절감되었습니다.
시나리오
ORDERS 와 CUSTOMERS 를 outer join 하여 주문 일자별로 정렬한 뒤 상위 10 건만 가져오는 페이징 쿼리입니다.
인덱스는 다음과 같습니다.
| 테이블 | 인덱스 | 컬럼 |
|---|---|---|
ORDERS | IX_ORDERS_N1 | ORDER_DATE |
CUSTOMERS | IX_CUSTOMERS_PK | CUSTOMER_ID (PK) |
CUSTOMERS 와의 join 은 outer join 이면서 PK INDEX UNIQUE SCAN 으로 매칭되므로 결과 건수에 영향이 없습니다. 이것이 본 패턴이 적용 가능한 핵심 조건입니다.
1) 원본 — Join 후 STOPKEY (NL 5,432 회)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT *
FROM (SELECT ROWNUM RN, ORDER_ID, ORDER_DATE
, CUST_FIRST_NAME, ORDER_TOTAL
FROM (
SELECT /*+ LEADING(A B) USE_NL(B) */
A.ORDER_ID, A.ORDER_DATE,
B.CUST_FIRST_NAME, A.ORDER_TOTAL
FROM ORDERS A, CUSTOMERS B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID(+)
AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND A.ORDER_DATE < TO_DATE('20120105', 'YYYYMMDD')
ORDER BY A.ORDER_DATE, A.ORDER_MODE, A.EMPLOYEE_ID)
WHERE ROWNUM <= 10)
WHERE RN >= 1;
옵티마이저는 ORDERS 의 4 일치(5,432 건) 를 모두 NL 조인한 뒤 마지막에 SORT + STOPKEY 로 10 건만 추출합니다. 페이징을 위해 10 건만 필요한데 후행 테이블을 5,432 번 두드린 셈입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 15731 | |
|* 1 | VIEW | | 1 | 10 | 15731 | |
|* 2 | COUNT STOPKEY | | 1 | 10 | 15731 | |
| 3 | VIEW | | 1 | 10 | 15731 | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 10 | 15731 | 2048 (0) |
| 5 | NESTED LOOPS OUTER | | 1 | 5432 | 15731 | | ← 후행 join 시 5432 건이 그대로 결과로 나옵니다
| 6 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 5432 | 4865 | |
|* 7 | INDEX RANGE SCAN | IX_ORDERS_N1 | 1 | 5432 | 17 | |
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 5432 | 5432 | 10866 | |
|* 9 | INDEX UNIQUE SCAN | IX_CUSTOMERS_PK | 5432 | 5432 | 5434 | |
-----------------------------------------------------------------------------------------------------
Id 5 의 NL OUTER join 이 5,432 회 발생합니다. Id 8 / Id 9 의 Starts = 5432 가 본질적인 비효율로, 후행 CUSTOMERS 인덱스를 5,432 번 두드린 결과 Buffers 15,731 의 약 70% 가 후행 테이블 access 에 소모됩니다.
2) 수정 — STOPKEY 후 Join (NL 10 회)
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT /*+ USE_NL(A B) */
A.RN, A.ORDER_ID, A.ORDER_DATE
, B.CUST_FIRST_NAME, A.ORDER_TOTAL
FROM (SELECT ROWNUM RN, ORDER_ID, ORDER_DATE, CUSTOMER_ID, ORDER_TOTAL
FROM (SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE, ORDER_TOTAL
FROM ORDERS
WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND ORDER_DATE < TO_DATE('20120105', 'YYYYMMDD')
ORDER BY ORDER_DATE, ORDER_MODE)
WHERE ROWNUM <= 10) A,
CUSTOMERS B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID(+)
AND A.RN >= 1;
ORDERS 만으로 ORDER BY + ROWNUM <= 10 페이징을 inline view 안에서 먼저 끝내고, 그 결과 10 건과 CUSTOMERS 를 NL OUTER join 합니다. 후행 join 회수가 10 회로 줄어듭니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1204 | |
| 1 | NESTED LOOPS OUTER | | 1 | 10 | 1204 | | ← ORDERS 10 건에 대해서만 join 발생
|* 2 | VIEW | | 1 | 10 | 1182 | |
|* 3 | COUNT STOPKEY | | 1 | 10 | 1182 | | ← ORDERS 에 대해 STOPKEY 가 먼저 적용됩니다
| 4 | VIEW | | 1 | 10 | 1182 | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 10 | 1182 | 2048 (0) |
| 6 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 1347 | 1182 | |
|* 7 | INDEX RANGE SCAN | IX_ORDERS_N1 | 1 | 1347 | 6 | |
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 10 | 10 | 22 | |
|* 9 | INDEX UNIQUE SCAN | IX_CUSTOMERS_PK | 10 | 10 | 12 | |
-----------------------------------------------------------------------------------------------------
핵심 차이는 다음 세 가지입니다.
Id 3의COUNT STOPKEY가Id 1의 NL OUTER join 위쪽에 위치합니다. 후행 테이블 join 전에 STOPKEY 가 먼저 적용된다는 뜻입니다.Id 8/Id 9의Starts = 10, 즉 후행CUSTOMERS인덱스를 정확히 10 번만 두드립니다.Buffers 15,731 → 1,204으로 약 13 배 절감됩니다.
분석
후행 테이블이 결과 건수에 영향 없는 조건
선행 페이징을 먼저 적용하려면 후행 테이블과의 join 이 선행 row 수를 변화시키지 말아야 합니다. 다음 형태 중 하나여야 안전합니다.
- OUTER JOIN: 후행이 매칭 안 돼도 선행 row 는 살아남으므로 결과 건수가 선행 건수와 같습니다.
- INNER JOIN + 1:1 관계: PK 또는 unique index 로 join 하여 모든 선행 row 가 정확히 1 개의 후행 row 와 매칭되는 경우입니다.
- INDEX UNIQUE SCAN 으로 lookup: PK / unique index 사용으로 1:1 매칭이 보장됩니다.
본 시나리오는 OUTER JOIN 과 INDEX UNIQUE SCAN 두 조건을 모두 만족하므로 페이징을 먼저 해도 안전합니다.
반대로 INNER JOIN + 1:N 관계 (예: ORDERS ↔ ORDER_ITEMS) 는 선행 페이징 후 후행이 N 배로 늘어나 결과 건수가 달라질 수 있습니다. 이때는 본 패턴 적용이 불가능합니다.
ORDER BY 가 선행 테이블 컬럼만일 때 가능
페이징의 ORDER BY 가 선행 테이블 컬럼만 사용한다면 정렬도 선행 테이블 안에서 끝낼 수 있습니다. 본 시나리오의 ORDER BY ORDER_DATE, ORDER_MODE 는 모두 ORDERS 컬럼이라 OK 입니다. 만약 후행 테이블 컬럼이 정렬 키에 포함된다면 정렬을 위해 후행 테이블을 먼저 join 해야 하므로 이 패턴 적용이 불가능합니다.
IX_ORDERS_N1 인덱스가 정렬을 도와줄 가능성
IX_ORDERS_N1 (ORDER_DATE) 가 정렬의 첫 번째 키 (ORDER_DATE) 와 일치하므로, 옵티마이저가 인덱스 스캔 순서대로 데이터를 가져와 SORT 부하를 줄일 수 있습니다. SORT ORDER BY STOPKEY 의 비용이 작은 이유입니다. ORDER_MODE 까지 인덱스에 포함된다면 sort 자체가 불필요해집니다.
Paging 쿼리의 핵심 질문은 “후행 테이블이 결과 건수에 영향을 미치는가” 입니다. 영향이 없다면 페이징을 먼저 적용하여 NL 조인 회수를 N 회에서 페이징 건수 회로 줄일 수 있습니다.
Buffers 비교
| 단계 | 원본 | 수정 | 차이 |
|---|---|---|---|
| ORDERS 액세스 | 4,865 | 1,182 | 약 4 배 절감 (STOPKEY 가 ORDERS 인덱스 스캔도 일찍 종료시킴) |
| CUSTOMERS 액세스 | 10,866 | 22 | 약 494 배 절감 (5,432 회 → 10 회) |
| 합계 | 15,731 | 1,204 | ≈ 13 배 절감 |
정리
세 줄로 압축하면:
- 페이징 쿼리에서 후행 테이블이 결과 건수에 영향이 없다면 페이징을 먼저 수행할 것. inline view 로 선행 테이블만 페이징한 뒤 후행 테이블을 NL 조인합니다.
- 적용 가능 조건 검증. outer join 또는 PK / unique index 로 join 하는 1:1 관계인지, 그리고
ORDER BY가 선행 테이블 컬럼만 사용하는지 확인합니다. - 진단 시그널. 실행계획에서
COUNT STOPKEY가 NL 조인 위쪽에 위치하면 페이징이 먼저 적용된 형태입니다. NL 조인 아래쪽에 위치하면 후행 join 후 STOPKEY 가 적용되는 비효율 형태로 튜닝 대상입니다.