오라클버전 P244- 오라클 페이징 기법

오라클의 페이징은 MySQL과 같이 간단하게 처리되지 못하고 rownum을 활용해 주어야 합니다.

쿼리를 실행할 때 primary key와 관련된 조건을 준다면  이를 이용하는 '실행 계획(Execution Plan)'을 생성하게 됩니다.

예를 들어 아래와 같은 SQL문을 실행해 sqldeveloper에서 실행해 봅니다.

select * from tbl_board
where bno > 0
order by bno desc;

sqldevloper의 실행 계획을 보면 아래와 같이 만들어 지는 것을 볼 수 있습니다.

실행 계획을 보면 내부적으로 primary key로 지정한 PK_BOARD를 이용해서 TBL_BOARD를 접근하는 것을 볼 수 있습니다.

이 때 DESCENDING이 적용되면서 접근하기 때문에 결과는 최근 데이터가 가장 먼저 나오게 됩니다.

order by의 경우 join등을 할 때 내부적으로 최적화된 방법을 찾기 때문에 항상 게시물의 역순으로 데이터를 검색한다는 보장은 없습니다.

이를 위해서 오라클에서는 개발자가 직접 실행계획을 유도하는 '힌트(Hint)'라는 것을 이용합니다.



select
/*+INDEX_DESC(tbl_board pk_board) */
*
from tbl_board
where bno > 0;


위의 SQL은 오라클의 힌트를 이용해서 강제로 primary key의 역순으로 데이터를 검색하도록 합니다. 실행 결과는 위의 그림과 동일하게 게시물의 역순으로 출력되게 됩니다.




실행된 결과물을 페이징 처리하기 위해서 필요한 또 다른 개념은 'ROWNUM'입니다.

ROWNUM은 쿼리의 처리가 끝나고 최종적으로 출력될 때 각 레코드에 붙는 일종의 일련 번호 입니다.

아래의 쿼리를 실행해 봅니다.


select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0;


위의 쿼리를 실행하면 출력되면서 번호가 붙는 것을 확인할 수 있습니다.

오라클의 페이징은 이렇게 실행 결과의 ROWNUM 값을 이용해서 페이지에 해당하는 데이터를 걸러내는 방식을 이용합니다.


아래의 쿼리를 실행해 봅니다.


select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0
and rownum <= 10;

rownum 이 10보다 작다는 조건은 마지막에 처리되는데, 이를 이용하면 아래와 같이 최신 데이터 10개를 가져올 수 있습니다. 

여기서 주의할 점은 ROWNUM 조건은 반드시 1이 포함되어야 한다는 점입니다. ROWNUM은 마지막에 데이터가 출력되면서 붙는 번호이기 때문에 where 조건에 1이 포함되지 않으면 매번 새로운 데이터가 1번이 되면서 아무런 데이터가 출력되지 않는 문제가 생깁니다.

예를 들어 아래의 쿼리는 기대와 달리 아무런 결과가 만들어지지 않습니다.

select
/*+INDEX_DESC(tbl_board pk_board) */
rownum rn, bno, title, content, writer, regdate, viewcnt
from tbl_board
where bno > 0
and rownum between 11 and 20;

이 문제를 해결하기 위해서는 'in-line view'기법을 이용해야 합니다.

in-line view는 쉽게 말해서 실행된 SQL문을 하나의 테이블처럼 간주해서 사용하는 방식입니다.

만일 10개씩 화면에 내용을 출력하는 경우 2페이지의 데이터는 우선 20개의 레코드를 가져온 후에 다시 rownum으로 생성된 번호를 이용해서 최신 10개의 데이터를 배제하는 방식입니다.

위의 설명을 쿼리로 만들면 아래와 같습니다.

select
*
from
  (
  select
  /*+INDEX_DESC(tbl_board pk_board) */
  rownum rn, bno, title, content, writer, regdate, viewcnt
  from tbl_board
  where bno > 0
  and rownum <= 20 )
where rn > 10;


쿼리의 실행 결과는 아래와 같습니다.

실행된 결과를 보면 RN 컬럼이 ROWNUM번호를 의미하고 있고, BNO가 역순으로 정렬된 것을 볼 수 있습니다.

이 때 발생하는 실행 계획은 다음과 같습니다.

실행 계획을 보면 우선 ROWNUM <= 20 조건을 먼저 실행하고, 실행된 결과 내에서 RN > 10 조건을 이용하므로 2페이지의 데이터를 출력하게 됩니다.