[Oracle] Pagination

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

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

 

12c 이전에는?

• 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

 

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

```sql

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 기반 : 이전 결과의 마지막 항목을 기준으로 그 다음부터 찾아나가는 방법

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

 

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

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

정렬된 데이터를 앞에서부터 offset 만큼 지나쳐가서 결과를 가져와야 하니까 그럴 수 밖에.

여러가지 해결 방안은 위 링크 참조.

 

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

 

Offset 기반 pagination은 row 상태 변화로 인해 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이 틀어진다. => 처리 누락건 발생

 

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

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