DB 최적화를 고려한 다건조회 페이징처리

오라클(Oracle)을 사용하여 다건조회(페이징) 처리를 해야하는 경우가 종종 있습니다.

물론 SQL 쿼리는 여러가지 방식으로 만들수 있고
다양한 방식이 존재하기에 어느하나가 정답이라고 할 수는 없습니다.

다만 실제 업무에서 개발중인 노하우를 공유하는 일환으로
다건조회(페이징) 처리시 DB의 성능 (대부분은 검색 속도와 관련된 부분이겠지요..)과 관련한 최적화된 쿼리 방식을 알려드리도록 하겠습니다.

이는 실제 DBA에게서 권고 받은 것이기 때문에 검증을 거쳤다고 할 수 있습니다.

자 그럼 아래를 참고하시어 최적화된 다건조회 쿼리 작성에 도움이 되시길 바랍니다.

## 1개 컬럼으로 페이징처리

SELECT *
FROM (
 SELECT ...
 WHERE ...
 AND A >= : io_nx_a
 ORDER BY A
)
WHERE ROWNUM <= 51

## 2개 컬럼으로 페이징처리

SELECT *
FROM (
SELECT ...
WHERE ///
AND A >= :io_nx_a
AND ( A > :io_nx_a
    OR ( A = :io_nx_a AND B >= :io_nx_b)
)
ORDER BY A, B)
WHERE ROWNUM <= 51

## 3개 이상의 다수개 컬럼으로 페이징처리

예) 5개 컬럼일 경우

SELECT *
FROM (
SELECT ...
WHERE ///
AND A >= :io_nx_a
AND (  A > :io_nx_a
     OR ( A = :io_nx_a AND B > :io_nx_b) )
     OR ( A = :io_nx_a AND B = :io_nx_b AND C > :io_nx_c)
     OR ( A = :io_nx_a AND B = :io_nx_b AND C = :io_nx_c AND D > :io_nx_d)
     OR ( A = :io_nx_a AND B = :io_nx_b AND C = :io_nx_c AND D = :io_nx_d AND E >= :io_nx_e)
)
ORDER BY A, B, C, D, E)
WHERE ROWNUM <= 51

(*) 제일 마지막의 OR에서만 '>='이고 나머지 OR에서는 '>'입니다.
반드시 ORDER BY에 기술된 컬럼들이 UNIQUE여야 하고 UNIQUE가 아닐 경우 이미 가이드 했던 것처럼 ROWID를 이용하셔야 합니다. SQL 가이드 참조

DBA의 말에 따르면 위와같은 방식으로
페이징 처리를 해주는 것이 DB(데이타베이스)의 성능향상에 도움이 된다더군요.

실제 예제는 아래처럼 사용됩니다.

SELECT /*+TB_CS_JH_CH_MCHTGRP_VS0001*/ *
 FROM
(
 SELECT
         a.mcht_grp_no,
         b.mcht_grpnm,
         a.mcht_no,
         c.mcht_mtalnm,
         a.apl_st_dt,
         a.apl_ed_dt
 FROM
       tb_cs_jh_ch_mchtgrp  a,
       tb_cs_jh_cm_mchtgrp  b,
       tb_cs_mc_cm_bsc      c
 WHERE a.mcht_no >= :mcht_no
   AND ( a.mcht_no > :mcht_no
          OR ( a.mcht_no = :mcht_no AND a.mcht_grp_no > :mcht_grp_no )
          OR ( a.mcht_no = :mcht_no AND a.mcht_grp_no = :mcht_grp_no  AND a.apl_st_dt >= :apl_st_dt )
           )
  AND  b.mcht_grp_no  =  a.mcht_grp_no 
     AND  b.apl_st_dt    <=  a.apl_st_dt
      AND  b.apl_ed_dt    >=  a.apl_ed_dt
     AND  c.mcht_no      =  a.mcht_no
 ORDER BY
       a.mcht_no, a.mcht_grp_no, a.apl_st_dt
)
WHERE ROWNUM <= 16

설명을 덧붙이자면
페이징시  메인 키가 되는 A >= :io_nx_a 의 인덱스서칭 시간만큼
OR을 동반한 올 시퀀스 서치시간을 줄여줄 수 있기 때문에 실제로 DB 검색시 속도향상 효과를 기대할 수 있습니다.

유용하게 사용하시기 바랍니다. ^^