오라클 페이지네이션 성능 최적화: ROWNUM vs ROW_NUMBER() 심층 분석 및 추가 최적화 방안

 

2024.12.14 - [Oracle Database 강좌] - 오라클 페이징 구현 및 스토어드 프로시저 활용: 심층 분석 및 실제 예제

오라클 페이징 구현 및 스토어드 프로시저 활용: 심층 분석 및 실제 예제

 

두 SQL 구문은 페이지네이션을 수행하는 방법을 나타내며, Oracle 데이터베이스에서 성능 차이가 발생할 수 있습니다. 두 방법의 차이를 성능 관점에서 분석하면 다음과 같습니다.

---

1. ROWNUM 기반 페이지네이션 (첫 번째 쿼리)

동작 원리:

1. ROWNUM은 결과 집합에서 행이 반환되는 순서대로 부여됩니다.
2. 내부 서브쿼리에서 ROWNUM으로 최대 행 번호를 제한하고, 그 결과에서 다시 시작 번호를 필터링합니다.

장점:

• 간단한 사용: ROWNUM은 Oracle에서 오래된 방식으로 직관적이고 간단하게 페이지네이션이 가능합니다.
• 작은 데이터셋: 데이터가 작을 때 비교적 효율적으로 작동합니다.

단점:

• 제약: ROWNUM은 할당 순서대로 번호를 부여하므로, 일부 조건 (e.g., ROWNUM > n)은 제대로 작동하지 않거나 추가적인 서브쿼리가 필요합니다.
• 비효율적인 정렬: 쿼리 실행 계획에서 내부적으로 전체 데이터셋을 정렬한 후 ROWNUM을 적용하므로, 정렬 비용이 높은 대규모 데이터셋에서는 성능 저하가 발생할 수 있습니다.

---

2. ROW_NUMBER() 기반 페이지네이션 (두 번째 쿼리)

동작 원리:

1. ROW_NUMBER()는 Oracle의 Window 함수로, 데이터셋을 정렬하고 각 행에 고유 번호를 부여합니다.
2. 이 번호를 기반으로 필터링을 수행합니다.

장점:

• 정확한 범위 필터링: ROW_NUMBER()는 정렬이 완료된 후 번호를 부여하므로, BETWEEN 조건이 직관적으로 동작합니다.
• 유연성: 다양한 윈도우 함수와 함께 사용 가능하여 고급 분석 쿼리에 적합합니다.
• 병렬 처리 지원: 대규모 데이터셋에서 Oracle의 병렬 처리가 더 잘 동작할 수 있습니다.

단점:

• 초기 오버헤드: ROW_NUMBER()를 계산하기 위해 전체 데이터셋을 스캔하고 정렬해야 하므로, 작은 데이터셋에서는 상대적으로 비효율적일 수 있습니다.

---

3. 성능 차이 비교

 

비교 항목	ROWNUM 기반	ROW_NUMBER() 기반
정렬 방식	내부적으로 데이터 정렬 후 ROWNUM 적용	정렬 후 번호를 부여 (명시적)
데이터 크기	작은 데이터셋에 유리	대규모 데이터셋에 더 적합
조건 처리	ROWNUM > n와 같은 조건은 비효율적	BETWEEN 조건으로 효율적 필터링 가능
유연성	정렬/필터링이 제한적	윈도우 함수 조합 가능, 고급 분석에 적합
병렬 처리	병렬 처리 제한적	병렬 처리 성능 최적화

---

4. 실제 사용에서의 고려사항

1. 데이터셋 크기:
   • 데이터가 작다면 첫 번째 방식이 더 빠를 수 있습니다.
   • 데이터가 크다면 ROW_NUMBER() 방식이 적합하며, 특히 정렬 작업이 복잡한 경우 성능 차이가 커질 수 있습니다.
2. 정확성:
   • ROWNUM 방식은 정확한 범위 필터링이 까다롭기 때문에, 시작/끝 번호로 명확한 페이지네이션이 필요하다면 ROW_NUMBER() 방식을 사용하는 것이 더 안전합니다.
3. 실행 계획 분석:
   • 두 쿼리에 대해 Oracle의 **실행 계획(EXPLAIN PLAN)**을 확인하여 성능을 비교하세요. 특정 인덱스나 병렬 처리 설정에 따라 결과가 다를 수 있습니다.
4. Oracle 버전:
   • 최신 Oracle 버전(12c 이상)에서는 ROW_NUMBER() 방식이 최적화되어 더 나은 성능을 보이는 경우가 많습니다.

---

5. 추천

• 작은 데이터셋: ROWNUM 기반.
• 대규모 데이터셋 / 정확한 페이지네이션 필요: ROW_NUMBER() 기반.
• 최적화: 인덱스 설계와 실행 계획 분석을 통해 병목 현상을 파악한 후 선택하세요.

 

728x90

 

페이징 대상 컬럼만 먼저 페이징 처리한 후 나머지 필드를 INNER JOIN으로 가져오는 방법

 

 

네, 말씀하신 방식은 페이징 대상 컬럼만 먼저 처리한 후 나머지 필드를 INNER JOIN으로 가져오는 방법입니다. 이 접근법은 일부 경우에서 성능 향상을 가져올 수 있습니다. 아래에서 그 이유와 고려 사항을 설명드리겠습니다.

---

1. 제안된 방법의 동작 원리

1단계: ROW_NUMBER() 또는 ROWNUM을 사용하여 페이징 대상이 되는 키 또는 주요 컬럼만 추출.

SELECT ROW_NUMBER() OVER (ORDER BY 주요_컬럼 ASC) AS RNUM, 주요_컬럼
FROM 테이블명
WHERE 조건

 

2단계: 페이징된 결과를 기반으로 나머지 필드를 원본 테이블과 조인하여 가져옴.

SELECT B.*
FROM (
    SELECT 주요_컬럼
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY 주요_컬럼 ASC) AS RNUM, 주요_컬럼
        FROM 테이블명
        WHERE 조건
    )
    WHERE RNUM BETWEEN :시작_번호 AND :끝_번호
) A
INNER JOIN 테이블명 B ON A.주요_컬럼 = B.주요_컬럼;

 

---

2. 이 방법의 장점

1. 작은 데이터셋으로 작업:
   • 페이징 대상이 되는 주요 컬럼(예: 기본 키, 특정 인덱스 필드)만 추출하므로, 페이징 단계에서 처리되는 데이터량이 줄어들어 성능이 향상될 수 있습니다.
   • 정렬(ORDER BY) 작업도 작은 데이터셋에서 이루어지므로 더 효율적입니다.
2. I/O 감소:
   • 대량의 데이터에서 모든 열을 페이징하는 대신, 필요한 최소 컬럼만 대상으로 작업하므로 디스크 I/O 및 메모리 사용량이 줄어듭니다.
3. 효율적인 병렬 처리:
   • 첫 단계에서 적은 양의 데이터를 다루기 때문에 병렬 처리 최적화가 더 잘 작동할 가능성이 높습니다.

---

3. 고려해야 할 점

1. 조인 비용:
   • 두 번째 단계에서 INNER JOIN을 수행하므로, 조인을 효율적으로 수행하려면 조인 키(예: 기본 키 또는 인덱스 컬럼)에 적절한 인덱스가 필요합니다.
   • 조인 대상 테이블이 대규모라면 성능 향상이 오히려 상쇄될 수 있습니다.
2. 추가 복잡성:
   • 단순한 쿼리보다 실행 계획이 복잡해질 수 있습니다. 실행 계획을 통해 병목 지점을 확인해야 합니다.
3. 데이터 분포:
   • 주요 컬럼의 값이 불균등하게 분포된 경우(예: 특정 구간에 데이터가 몰려 있음) 조인 후 반환되는 데이터량이 커질 수 있습니다.

---

4. 언제 효과적인가?

1. 페이징 대상 데이터가 작고, 나머지 열이 크거나 많을 때:
   • 페이징의 대상이 되는 컬럼(예: 기본 키)만 추출하는 작업은 상대적으로 빠르며, 이후 필요한 열만 조인해서 가져오면 총 처리량이 줄어듭니다.
2. 대규모 테이블에서 특정 열이 인덱싱된 경우:
   • 주요 컬럼에 효율적인 인덱스가 적용되어 있다면, 페이징 및 조인 과정이 최적화될 수 있습니다.
3. 특정 조건에서 선별된 데이터:
   • 예를 들어, WHERE 조건으로 필터링된 데이터가 전체 데이터의 일부라면 이 방법이 더 효율적입니다.

---

5. 실제 성능 확인 방법

1. 실행 계획(EXPLAIN PLAN) 분석:
   • 제안된 방법과 기존 방법의 실행 계획을 비교하여, 테이블 스캔 비용, 정렬 비용, 조인 비용 등을 확인하세요.
2. 통계 수집(ANALYZE):
   • 대상 테이블의 통계 정보가 최신인지 확인하고, 필요하다면 DBMS_STATS.GATHER_TABLE_STATS를 실행하여 최적화를 도와주세요.
3. 샘플 데이터 테스트:
   • 실제 데이터 환경에서 샘플 크기로 테스트하여 성능 비교를 수행해 보세요.

---

6. 결론

• 대규모 테이블에서 특정 컬럼만으로 페이징 후 필요한 필드를 INNER JOIN으로 가져오는 방식은 성능 향상이 있을 가능성이 높습니다.
  • 특히, 페이징 대상 컬럼에 인덱스가 잘 설정되어 있고, 나머지 열이 많거나 큰 경우 유리합니다.
• 다만, 추가 조인 비용 및 실행 계획 분석 결과에 따라 기존 방식보다 효율적이지 않을 수도 있습니다.
  테스트와 실행 계획 분석을 통해 최적의 방식을 선택하는 것이 중요합니다.