Post

(Oracle) Pagination

Posted Updated
By umbum 5 min read

Pagination

https://en.wikipedia.org/wiki/Select_(SQL)#Result_limits

  • Offset 기반
    • rownum
    • ROW_NUMBER()
  • Cursor 기반
    • Cursor-based pagination을 Seek method라고도 부르고 No offset이라고도 부른다.

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

12c 이전에는?

Offset 기반 : ROW_NUMBER() 사용한 방법

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

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를 사용해야 해서 가독성이 좀 떨어지기 때문에 요게 더 낫긴 하다.
  • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때, 전체적으로 1개 만큼 밀리며 offset이 틀어지게 됨. 이전 페이지에서 보였던 마지막 항목이 다음 페이지 처음에 또 보일 수 있음.

Cursor 기반 : 이전 결과의 마지막 항목을 기준으로 그 다음부터 찾아나가는 방법

1
2
3
4
5
6
7
8
9

SELECT *
FROM (
    SELECT *
    FROM games
    WHERE game_id > {lastResultId}
    ORDER BY game_id DESC
)
WHERE ROWNUM <= {pageSize};
  • https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method/
  • https://jojoldu.tistory.com/528
  • 장점?
    • Index range scan이 뜨며 인라인 뷰를 사용하지 않기 때문에 싱글 스레드로 조회 시 성능이 가장 좋음.
      • 기본적으로 offset 기반 paging은 그다지 빠른 방법은 아님. index가 걸려있으면 그나마 낫지만 안걸려있다면 페이징 쿼리를 실행 할 때 마다 풀스캔 할 수도 있다.
      • 반면 Cursor 기반 방식은 index column을 기반으로 직접 range를 지정해서 스캔하는 방식이기 때문에 성능 관점에서 더 유리함.
    • 이미 조회가 끝난 앞쪽 부분에 row가 추가 되었을 때, 문제 없음. (이전 결과 기준 n개의 다음 항목 조회이므로)
  • 단점?
    • 한 번에 페이지를 건너뛰어 n번째 페이지를 조회 하는 것은 불가능. 앞에서부터 순차적으로만 가능하다. (More 기능)
    • 병렬 조회가 불가능하다는 것이 제일 큰 단점.
    • 조회 조건으로 거는 컬럼이 Unique 해야 한다는 제약이 있음.

paging 쿼리가 처음엔 반환이 빠르다가 갈 수록 느려진다?!

https://developpaper.com/large-amount-of-data-paging-query-is-very-slow-how-to-optimize/

정렬된 데이터를 앞에서부터 offset 만큼 지나쳐가서 결과를 가져와야 하니까 그럴 수 밖에. 여러가지 해결 방안은 위 링크 참조.

반대로 2번째 수행 부터 빨라지는 케이스?

Offset 기반 pagination은 row 상태 변화로 인해 offset이 틀어질 가능성 존재

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

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이 틀어진다. => 처리 누락건 발생

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

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