(Oracle) Pagination

Offset-based pagination

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

• https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
• https://rimkongs.tistory.com/158

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

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

• https://stackoverflow.com/questions/241622/paging-with-oracle
• https://blogs.oracle.com/oraclemagazine/on-top-n-and-pagination-queries
• https://use-the-index-luke.com/sql/partial-results/window-functions

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이 틀어지게 됨.
  • 처리 누락건 발생.

예시)

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 쿼리가 처음엔 반환이 빠르다가 갈 수록 느려진다?

• https://developpaper.com/large-amount-of-data-paging-query-is-very-slow-how-to-optimize/
• 정렬된 데이터를 앞에서부터 offset 만큼 지나쳐가서 결과를 가져와야 하니까 그럴 수 밖에.
• 여러가지 해결 방안은 위 링크 참조.
• 반대로 2번째 수행 부터 빨라지는 케이스?

Cursor-based pagination

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

• https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method/
• https://jojoldu.tistory.com/528

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 기능)
• 병렬 조회가 불가능하다는 것이 제일 큰 단점.
  • 하지만 Spring Batch에서 AbstractPagingItemReader::doRead는 synchronized block을 사용하기 때문에 애초에 병렬 조회 하지 않아 별 단점이 아니다.
  • [Spring Batch] 병렬 처리
  • Spring Batch Multi-threaded Step 사용 시 chunk 구성에 대한 오해
• 조회 조건으로 거는 컬럼이 Unique 해야 한다는 제약이 있음.

참고

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

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

docs

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