Post

(Oracle) Pagination

Posted Updated
By umbum
10 min read
(Oracle) Pagination

OFFSET pagination

Offset 기반 장점

  • 한 번에 페이지를 건너뛰어 n번째 페이지를 조회 하는 것이 가능하다.
  • 1…10 방식으로 페이지 번호를 제공하고 한번에 그 페이지로 이동하는 것이 필요하다면, 이 방식을 쓸 수 밖에 없다.

Offset 기반 단점

1. row 상태 변화로 인해 offset이 틀어질 가능성 존재

  • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때,
    • 전체적으로 1개 만큼 밀리며 offset이 틀어지게 됨.
    • 이전 페이지에서 보였던 마지막 항목이 다음 페이지 처음에 또 보일 수 있음.
  • 반대로 이미 조회가 끝난 앞쪽 부분에서 row가 삭제 되었을 때,
    • 전체적으로 1개 만큼 당겨지며 offset이 틀어지게 됨.
    • 처리 누락건 발생.

예시)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT
    *
FROM
    (
        SELECT *
        FROM
            (
                SELECT ROWNUM rownum_, sub.*
                FROM
                    (
                        SELECT
                            bno...
                        FROM RFND
                        WHERE obj_prd = '2022_1H'
                          AND chgn_amt IS NULL -- 주목
                        ORDER BY bno
                    ) sub
            )
        WHERE rownum_ > (#{_page} * #{_pagesize})
    )
WHERE ROWNUM <= #{_pagesize}
  • ItemReader에서 위와 같이 조회하고
  • ItemWriter에서는 chgn_amt를 NULL이 아닌 값으로 업데이트한다면?
  • ItemReader에서 불러오는 대상 row들의 상태가 바뀌었기 때문에 조회 결과가 달라져 offset이 틀어진다. => 처리 누락건 발생

2. Offset-based 쿼리는 첫 페이지는 빠르지만 뒤쪽 page로 갈 수록 느려진다.

[!warning] 얼마나 느려지는가? 500,000번째 데이터를 읽어오는 것은 10s 소요. OLTP에서는 치명적이다. 배치인 경우 병렬 처리로 속도 개선 노려볼 수 있지만… 보통 cursor가 더 낫다

Cursor/Seek/Keyset pagination

[!info] 이전 결과의 마지막 항목을 기준으로 그 다음부터 찾아나가는 방법. 빠르다.

1
2
3
4
5
6
7
8

SELECT *
FROM (
    SELECT *
    FROM games
    WHERE game_id > {lastResultId}
    ORDER BY game_id DESC
)
WHERE ROWNUM <= {pageSize};

cursor 기반 장단점

장점

  • Index range scan이 뜨며 인라인 뷰를 사용하지 않기 때문에 싱글 스레드로 조회 시 성능이 가장 좋음.
    • 기본적으로 offset 기반 paging은 그다지 빠른 방법은 아님. index가 걸려있으면 그나마 낫지만 안걸려있다면 페이징 쿼리를 실행 할 때 마다 풀스캔 할 수도 있다.
    • 반면 Cursor 기반 방식은 index column을 기반으로 직접 range를 지정해서 스캔하는 방식이기 때문에 성능 관점에서 더 유리함.
  • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때, 문제 없음. (이전 결과 기준 n개의 다음 항목 조회이므로)

단점

  • 한 번에 페이지를 건너뛰어 n번째 페이지를 조회 하는 것은 불가능. 앞에서부터 순차적으로만 가능하다.
    • 1…10 같이 번호로 한번에 가는 것은 불가능하고, 더보기 방식 기능만 제공 가능 (기획적 제약)
  • 병렬 조회가 불가능하다.
  • 조회 조건으로 거는 컬럼이 Unique 해야 한다는 제약이 있음.
    • 일반적으로 시간 컬럼은 조건으로 사용하지 않는 것이 좋다.
      • 초 단위 정밀도의 시간 컬럼을 cursor 조건으로 사용하게 되면?
      • 페이징 단위가 100, 초 까지 같은 데이터가 1000개라고 가정해보면, 앞의 100개만 처리되고 뒤의 900개는 건너뛰어 누락이 발생한다.
    • 비슷한 맥락에서 복합키도 조건으로 사용 할 수 없다.
      • 두개 이상의 컬럼을 대상으로 다음 읽어야 할 지점을 계산하는 일반적인 방법은 없다. 여러 컬럼 고려해서 남은 데이터가 있는지, 누락은 없는지 확인해야 하니까.
      • PK 설계 - 복합키 vs 단일ID키

참고

12c 버전 이후라면? : Row Limiting Clause

1
2
3
4

SELECT *
FROM table_name
ORDER BY column_name
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY;

참고) LIMIT 기능은 FETCH FIRST

1
2
3
4

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

12c 이전에는? : ROW_NUMBER(), rownum

1
2
3
4
5
6
7
8
9
10
11
12

SELECT *
FROM (
    SELECT /*+ first_rows(25) */
        tid,
        ...etc fields,
        ROW_NUMBER() OVER (ORDER BY tid DESC) rn
    FROM
        trad
    )
WHERE
    rn BETWEEN 25 AND 50
ORDER BY rn;
  • Oracle Hint 주석 같이 넣어준다. 자주 쓰이는 Oracle Hint 목록
  • ROW_NUMBER() vs rownum
    • 성능상 차이는 없어보이고, 다만 rownum을 사용하는 방식은 두 단계의 inline view를 사용해야 해서 가독성이 좀 떨어지기 때문에 요게 더 낫긴 하다.

Oracle에서 ROWNUM은 ORDER BY 바깥에서 사용해야 하는 이유

  • https://jutudy.tistory.com/13- 요약 : ROWNUM과 ORDER BY가 같이 있으면 ROWNUM이 먼저 적용되어 n개만 가져온 다음 정렬하기 때문.
  • 전체를 대상으로 정렬하고 앞의 n개만 가져오는 것이 보통 원하는 동작이다.
  • 반면 순서 상관 없이 랜덤하게 n개만 가져올거라면 ORDER BY 서브쿼리 없이 ROWNUM만 사용해도 된다.

페이징 쿼리가 두번째 실행 부터 빨라지는 경우

1
2
3
4
5
6

2023-02-27 14:40:23 [main] [INFO ] [MyStepConfig.java]beforeStep(170) : ### start
2023-02-27 15:01:51 [main] [INFO ] [MyStepConfig.java]afterChunk(156) : ### processed chunk count: 1, read item count: 100000, file item count: 100000
2023-02-27 15:02:18 [main] [INFO ] [MyStepConfig.java]afterChunk(156) : ### processed chunk count: 2, read item count: 200000, file item count: 200000
2023-02-27 15:02:44 [main] [INFO ] [MyStepConfig.java]afterChunk(156) : ### processed chunk count: 3, read item count: 300000, file item count: 300000
2023-02-27 15:03:12 [main] [INFO ] [MyStepConfig.java]afterChunk(156) : ### processed chunk count: 4, read item count: 400000, file item count: 400000
2023-02-27 15:03:38 [main] [INFO ] [MyStepConfig.java]afterChunk(156) : ### processed chunk count: 5, read item count: 490835, file item count: 490835
  • 1번째 chunk 처리 할 때는 20분 소요
  • 2번째 청크 부터는 1분도 채 안걸림.
  • 아마 read에서 시간이 단축되었을 것 같은데… 왜일까? index는 확실히 안걸려 있고… ORDER BY가 있었나? 기억이 안나네.
  • => 1번 청크만 극단적으로 느린 상황이므로 정렬 + 콜드캐시 + 초기화 가능성 큼.
RDBMS, Query design
oracle
This post is licensed under CC BY 4.0 by the author.