[Oracle] 오라클 페이징 쿼리 쉽게 만들기 Row Limiting Clause 사용 :: 마이자몽

오라클 페이징

오라클 데이터베이스 페이징 쿼리는 어떻게 작성할까요? 포털 사이트에서 검색을 했을 때, 게시판 형태의 웹사이트에서 결과를 볼때, 한번에 모든 결과를 볼 수 없기 때문에 페이징 처리를 하여 화면에 출력되는 게시물을 제한합니다. 그럼 이런 변동적인 결과를 보여주기 위해 오라클 페이징 쿼리는 어떻게 짤까요? Top-N Query를 사용할 수 있지만, 오라클 데이터베이스에서는 Row Limiting Clause라는 Feature를 제공합니다.

 

Oracle Database scott 계정에 기본적으로 존재하는 EMP 테이블을 갖고 페이징 처리를 해보겠습니다.

 

 

Top-N Query

 Top-N Query는 상위 N개 데이터를 보여주는 쿼리입니다. 페이징을 하기전에 데이터를 어떤 순서로 정렬해서 보여줄건지 정해야합니다. 일반 게시판에서는 주로 날짜순으로 정렬을 해서 페이징 처리를 하고, 네이버, 다음, 구글과 같은 검색 포털사이트에서는 자사의 검색 알고리즘을 통해 어떤 순서로 글들을 보여줄지 정합니다.

 

보여줄 데이터가 정해지면 모든 데이터를 한번에 보여줄 수 없기 때문에 페이징 처리를 하게됩니다. 정렬된 데이터에서는 상위 N개의 데이터를 보여주기 위해 Top-N Query를 사용합니다.

SELECT EMPNO ,ENAME ,SAL
FROM
  (
    SELECT 
      * 
    FROM 
      EMP 
    ORDER BY SAL DESC
  )
WHERE ROWNUM <= 5;

상위 5개의 데이터를 출력하는 쿼리입니다. Top-N Query는 Order By 절이 맨 마지막에 실행되기 때문에 서브쿼리로 먼저 정렬시킨 후, 밖에서 WHERE 절로 ROWNUM에 조건을 걸어주는 Query입니다. 오라클 페이징 쿼리의 기본은 TOP-N Query를 사용합니다. 하지만, 이러한 TOP-N Query의 방식을 사용하더라도 페이징 처리를 하기 위해서는 쿼리내용이 조금 복잡해집니다.

 

페이지 당 5개의 데이터를 출력한다고 했을때, 첫 5개는 위 처럼 쉽게 구현할 수 있습니다. 하지만, 2번째 페이지의 데이터를 받아오기 위해서는 6번째 부터 10번째의 데이터를 받아와야합니다. 이를 구현하기 쿼리 내용이 좀 더 복잡해집니다.

 

Paging Query

SELECT EMPNO, ENAME, SAL 
FROM
  (
  SELECT SEQ, EMPNO, ENAME, SAL 
  FROM
  (
    SELECT ROWNUM AS SEQ, EMPNO, ENAME, SAL
    FROM
      (
        SELECT *
        FROM EMP
        ORDER BY SAL DESC
      )
  )
WHERE SEQ >= 6 -- START NO
)
WHERE ROWNUM <= 5; -- COUNT

Top-N Query를 응용하여 만든 오라클 페이징 쿼리입니다. 내부적으로 정렬시키고, START NO 번째 데이터 부터 보여지도록 한번 감쌓고, 마지막으로 보여질 게시물의 갯수만큼 지정해줍니다. 육안으로 확인하여 바로 내용을 이해하기 조금 힘든 쿼리입니다. 하지만, 오라클에서는 Row Limiting Clause를 사용해서 좀 더 쉬운 방법으로 페이징 처리를 할 수 있습니다.

 

 

Row Limiting Clause

Row Limiting Clause는 오라클 12c 버전부터 사용이 가능합니다. 11g까지는 Top-N Query를 이용합니다. ORDER BY 절 이후에 실행되는 절로 몇 개의 데이터를 어디서부터 볼지 중복여부와 함께 출력이 가능합니다.

OFFSET : 첫 번째 데이터로 부터 얼마나 떨어져 있는지

FETCH : 몇개의 데이터를 볼건지

Paging Query

SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Top-N Query를 사용한 페이징 처리 쿼리와 같은 내용을 출력해주는 쿼리입니다.

 

SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
OFFSET 6 ROWS FETCH FIRST 5 ROWS ONLY;

Top-N Query를 사용하는것 보다 확실히 어떤 내용을 출력해주는 쿼리인지 눈으로도 확인가능하고 유지보수 측면에서 효율적으로 관리할 수 있는 쿼리입닌다.

 

Row Limiting Clause은 오라클 페이징 쿼리를 작성하는데 많은 도움을 주지고 여러가지 방법으로 사용이 가능합니다.

 

중복 처리 [ONLY | WITH TIES]

좌측 : ONLY, 우측 : WITH TIES

SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 9 ROWS [ONLY | WITH TIES];

FETCH절과 함께 ONLY와 WITH  TIES를 사용하여 중복되는 데이터를 보여줄것인지 여부를 선택할 수 있습니다. MILLER와 WARD의 월급은 같은데 9개의 데이터만 출력했을때 WITH TIES를 함께 사용하면 중복되는 데이터를 같이 보여줘 총 10개의 데이터를 출력해줍니다.

 

비율로 출력 PERCENT

SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 50 PERCENT ROWS ONLY;

PERCENT를 사용하면 조회된 데이터를 지정한 비율만큼 출력할 수 있습니다. 총 12개의 데이터 중 절반인 6개를 출력합니다.