Post

(Oracle) Pagination

Posted Updated
By umbum 6 min read

Offset-based pagination

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

1
2
3
4

SELECT *
FROM table_name
ORDER BY column_name
OFFSET #{offset} ROWS FETCH NEXT #{pageSize} 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를 사용해야 해서 가독성이 좀 떨어지기 때문에 요게 더 낫긴 하다.

Offset 기반 단점

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

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

Cursor-based pagination

[!info] Seek method 또는 No offset 이라고도 부른다.
이전 결과의 마지막 항목을 기준으로 그 다음부터 찾아나가는 방법.

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번째 페이지를 조회 하는 것은 불가능. 앞에서부터 순차적으로만 가능하다. (More 기능)
  • 병렬 조회가 불가능하다.
  • 조회 조건으로 거는 컬럼이 Unique 해야 한다는 제약이 있음. (가장 큰 단점)
    • 따라서 복합키에는 사용 할 수 없다.
    • 시간 컬럼에도 사용 할 수 없다.

참고

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

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

docs

RDBMS, Query design
oracle
This post is licensed under CC BY 4.0 by the author.