Oracle offset (오라클 페이징 처리)

이번 프로젝트에서 SQL 튜닝 가이드를 받아 쿼리를 수정하면서 offset 키워드를 알게 되어 포스팅한다. 오라클은 12c 버전부터 offset과 limit가 추가되었다.

Oracle 12c 이전

Oracle 12c 이전에는 ROW_NUM을 사용하여 페이징 처리를 했다. 단점은 쿼리를 view로 감싸므로 쿼리가 복잡해진다.

SELECT *
FROM (
    SELECT a.*, ROWNUM AS "ROWNUM"
    FROM (
        사용자 정의 쿼리 QUERY
    ) a
    WHERE ROWNUM <= end_number
) a
WHERE rnum >= start_number

Oracle 12c 이후

Oracle 12c 이후부터는 행 제한(row limitng)을 이용하여 간단하게 페이징 처리할 수 있다. 오라클의 지저분한 페이징 처리를 간결하게 할 수 있는 장점이 있다.

• offset : 건너뛸 행의 수를 지정
• fetch : 반환할 행의 수를 지정

SELECT *
  FROM table_name
 ORDER BY column_name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

실제 활용 사례 1

SELECT * 
  FROM table_name
 ORDER BY column_name
OFFSET (pageNbr-1)*pageCnt ROWS FETCH NEXT pageCnt ROWS ONLY;

--OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; -- 1페이지 : 0개 행 건너뛰고 10개의 행 반환(pageNbr:1, pageCnt:10)
--OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- 2페이지 : 10개 행 건너뛰고 10개의 행 반환(pageNbr:2, pageCnt:10)
--OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- 3페이지 : 20개 행 건너뛰고 10개의 행 반환(pageNbr:3, pageCnt:10)

실제 활용 사례 2

Pageable를 이용하여 페이징 구현시 아래처럼 offset, pageSize를 이용하여 구현할 수도 있다.

• 관련 포스팅 : 스프링부트 개발환경 구성하기 (8) 페이징 (Mybatis에서 Pageable)

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