페이징 기법 모음

원본 : http://woongsanta.tistory.com/13


 SELECT *
  FROM (SELECT ROW_NUMBER () OVER (ORDER BY ID) AS ROW_NUMBER,
               COUNT (*) OVER () AS row_count,

               ID, cat_id, reg_date, type, send_email, subject
          FROM question
         WHERE reg_date = '20030129')
 WHERE row_number>=:start_row AND row_number<=:end_row

1. row_number() over(정렬조건) : 기존의 ROWNUM을 사용할 때에는 정렬되기전에  rownum이 계산되기 때문에 정렬 조건을 주게되면 rownum 기준으로 페이지를 잘라 올 수 없음. 이것을 보완하는 방식이 row_number() over()를 이용하여 해결한다.  
2. count(*) over() : 페이징을 위해서는 목록의 총 개수를 구해오는 것이 필요한데, 기존에는 총 개수를 구하기 위해 count SQL을 수행하고 다시 실제 목록을 가져오는 SQL을 두 번 수행해서 가져오게 된다. 이를 한번에 해결하기 위해서 count(*) over()를 이용한다. 
3. row_number>=:start_row AND row_number<=:end_row : row_number() over() 함수를 통해 목록을 채번하고 해당 페이지의 결과 set만 가져오도록 한다.

  select * from (
    select rownum as rnum, A.* from (
      [Original SQL]
    ) A where rownum <= [offset + limit] order by rownum desc
  ) where rownum <= [limit] order by rnum asc;

프로젝트에서 페이징을 위해 okjsp에서 kenu님이 만든 SQL을 적용햇는데,

소팅이 반대로 되더군여. 그래서 조금 고쳤습니다.

오라클 8i에서 잘 됩니다.

offset은 해당 페이지의 시작 아이템번호..0부터...

limit은 한페이지당 보여줄 최대 아이템 수...

offset + limit = (pageNo + 1) * pageSize과 동일 의미

페이징 처리를 할려면 전체 Row수와 order by한 부분중 가져올 부분을 짤라서 가져와야 한다.

그래서 다음의 쿼리를 이용하면 된다.

SELECT * FROM
( SELECT a.*, rownum+rnum-1 as total_rows
   FROM ( SELECT a.*, rownum rnum
            FROM (

원하는 ORDER BY 절이 포함된 SELECT 쿼리

            ) a
          ORDER BY rnum DESC ) a
  ORDER BY rnum )
WHERE rnum BETWEEN ? AND ?

======================================================

예)

SELECT * FROM
( SELECT a.*, rownum+rnum-1 as total_rows
   FROM ( SELECT a.*, rownum rnum
            FROM (

SELECT column1, column2

   FROM table

 WHERE column like '%'

ORDER BY column DESC

            ) a
          ORDER BY rnum DESC ) a
  ORDER BY rnum )
WHERE rnum BETWEEN 1 AND 10

주의) 오라클 8i이상만 지원한다.

======================================================

참고) BETWEEN ? AND ?의 start와 end 구하기

int currentPage = 1;                   // 현재페이지
int ItemCountPerOnePage  = 10;  // 한페이지 보여줄 목록 개수

int end   = currentPage * ItemCountPerOnePage;
int start = end - (ItemCountPerOnePage - 1);

위와 같이 구하면 된다.

MSSQL 쿼리사용

 

mkex.pe.kr 에 '허동석' 님이 작성해 주신 글을 옮겨 옵니다.

---------------------------------------------------------------------------------------------------------------------------------------

페이징 쿼리 종류별로 성능을 비교한 좋은 자료가 있어서 상당부분 인용했습니다.

"ex)"에 들어가는 샘플 쿼리는 바투 락커룸 DB에 파일첨부(TB_AttachFile) 테이블을 대상으로 페이징 쿼리를 작성해 봤습니다.

5번에 표시된 내용이 데이터 건수에 상관없이 실질적으로 가장 빠르지만 우리 시스템에 적용할 수 있는지는 의문이 갑니다. 테이블 별로 인덱스 생성이 필요할 수 있고 중간에 데이터가 삭제되거나 어떤 반응이 생길 때 문제가 될 소지가 있는지도 조사해야 할 것 같습니다.

참고로 웹젠 빌링에서 사용하던 페이징 쿼리는 4번을 이용했었습니다.
그리고 2번에 샘플 쿼리를 보면 TB_AttachFile 테이블에 FileGuid가 None Clustered Index로 걸려 있어서 서브쿼리 내에서도 ORDER BY FileGuid DESC를 해줘야합니다.(즉 ORDER BY 를 2번 해야한다는..)
대부분의 기본키들이 None Clustered Index 라 이 점에 대한 이슈도 있습니다.

1. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]  

  레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate
FROM TB_AttachFile



2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])

   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate
FROM TB_AttachFile
WHERE FileGuid NOT IN
(
 SELECT TOP 0 FileGuid
 FROM TB_AttachFile
 ORDER BY FileGuid DESC
)
ORDER BY FileGuid DESC


3. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] IN (SELECT TOP [페이지출력 갯수] [글번호] FROM
   (SELECT TOP [불러올 총 게시물수] [글번호] FROM [테이블 명]) AS A ORDER BY [글번호])
   ORDER BY [글번호] DESC

   이 쿼리 구문은 2번의 쿼리 구문의 문제점을 보완한 구문입니다. NOT IN 대신에 IN을 사용 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate
FROM TB_AttachFile
WHERE FileGuid IN
(
 SELECT TOP 123 FileGuid -- 총 데이터수 - ( (페이지수 - 1) * 10) // 1페이지 : 123 - 0, 2페이지 : 123 - 10 ...
 FROM
 (
  SELECT FileGuid
  FROM TB_AttachFile
 )AS A
 ORDER BY FileGuid
)
ORDER BY FileGuid DESC


4. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호] FROM [테이블명]) AS A)

   4번째 쿼리 구문은 IN, NOT IN이 아닌 출력할 마지막 글번호 바로 앞이 글번호를 찾아서 처리를 해주는
   쿼리 구문입니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate
FROM TB_AttachFile
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1 
  FROM TB_AttachFile
  ORDER BY FileGuid DESC
 )AS A
)
ORDER BY FileGuid DESC


5. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호]
   FROM [테이블명] WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]) AS A
   WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호])
   AND [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]

   5번재 쿼리는.. 글에 인덱스(가칭)라는 필드를 하나 더 추가 해서.. 글 기본 2000개마다 (가변적입니다)
   인덱스를 증가 시켰습니다. 즉 2000개를 하나의 묶음으로 만든것입니다.
   그 인덱스를 기준으로 처리를 해주었습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate
FROM TB_AttachFile
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1 
  FROM TB_AttachFile
  WHERE IDX_FileGuid > 350  -- 350은 이전 페이지의 끝 게시물 번호. (350보다 큰 10개를 얻음.)
  ORDER BY FileGuid DESC
 )AS A
)
ORDER BY FileGuid DESC


결과.

게시물은 100만개를 넣고 테스트를 했습니다
서버정보 : CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000
처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms)
 첫페이지 실행    끝페이지 실행
1 :  273                 11476.56
2 :  289                 4406.25
3 :  289                 2695.31
4 :  289                 1218.75
5 :  7.81                23.44

http://woongsanta.tistory.com/trackback/13
.












-----------------------------
http://blog.naver.com/manpro/100115522507

1. 일반적인 오라클 페이징 처리

 

01.SELECT * FROM (

02.     SELECT A.*, 

03.                 ROWNUM AS RNUM,

04.                 FLOOR((ROWNUM-1)/{디스플레이수}+1) AS PAGE,

05.                 COUNT(*) OVER() AS TOTCNT FROM (

06.          {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}

07.    ) A

08.) WHERE PAGE = {페이지번호};

09.  

10.OR

11.  

12.SELECT * FROM (

13.     SELECT A.*, 

14.                 ROWNUM AS RNUM,

15.                 COUNT(*) OVER() AS TOTCNT FROM (

16.          {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}

17.    ) A

18.) WHERE RNUM > {범위부터} AND RNUM <= {범위까지};

2. 오라클 대용량 페이징 처리

 

01.SELECT * FROM (

02.     SELECT ROWNUM AS RNUM, A.* FROM (

03.          {검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}

04.    ) A WHERE ROWNUM <= {범위까지}

05.) WHERE RNUM > {범위부터};

06.  

07.OR

08.  

09.SELECT * FROM (

10.     SELECT /*+ INDEX_ASC or INDEX_DESC(A {정렬조건 인덱스명}) */

11.                 ROWNUM AS RNUM, A.*  FROM (

12.          {검색쿼리 - 정렬이 필요한 경우 정렬조건을 포함하지 않고 ORACLE 힌트사용}

13.     ) A WHERE ROWNUM <= {범위까지}

14.) WHERE RNUM > {범위부터};

------------------

게시판에서 가장 어려운 부분이고 이해하기가 좀 어려운 페이지징에 관한 쿼리입니다.

페이지 번호에 따라 데이터를 가져올수 있는 것입니다! 우왕~ 굳!

총 12개의 데이터를 가지고 있느 테이블이 있다라는 가정하에 ㅋ

listnumber = 5  //한 페이지 당 보여주는 게시물의 수

page_no = 1 //페이지 번호

select *
       from (select A.*,FLOOR((ROWNUM - 1)/listnumber + 1) page, rownum
              from (
                     select board_title from board order by board_no desc
                     ) A
               )
       where page = page_no;

우선 FLOOR함수는 값의 내림값을 표현해주는 함수입니다.

SQL> select FLOOR(7.4) from dual;

SQL> 7

ROWNUM은 테이블이 가지고 있는 행의 번호를 순서대로 반환해줍니다.

SQL> select rownum from 테이블명; //3개의 데이터를 가지고 있는 테이블이라 가정

SQL> ROWNUM

SQL> --------

SQL> 1

SQL> 2

SQL> 3

결국 총 데이터가 12개이면 rowum 은 1부터 12까지가 되죠.

FLOOR((ROWNUM - 1)/listnumber + 1) page

그리하여 rownum은 1부터 12까지 대입을 하게되고 수식에 의해서 page 는 3까지의 값을 가지게 되고

값 페이지값에 해당되는 rownum 값을 알게 되겠죠.

page     rownum

------- -----------

1          1

1          2

1          3

1          4

1          5

2          6

2          7

2          8

2          9

2         10

3         11

3         12

그럼

where page = page_no;

page 에 jsp또는 서블릿에서 넘어오는 페이지 번호를 대입하면

자연스럽게 페이지별 데이터를 가져오게 됩니다.

[출처] 오라클 페이징 쿼리|작성자 쭌이쭈니



-----------------------------








20101109 예제


SELECT *
  FROM (SELECT ROWNUM AS rnum,
               A.*,
               FLOOR ( (row_count / 10) - page0 + 1) page
          FROM (  SELECT ROW_NUMBER () OVER (ORDER BY b_seq) AS ROW_NUMBER,
                         COUNT (b_seq) OVER () AS row_count,
                         A.B_SEQ,
                         FLOOR ( (ROWNUM - 1) / 10) AS page0,       --디스플레이수+1
                         A.TITLE,
                         TO_CHAR (A.WRITE_DT, 'yyyymmdd') WRITE_DT,
                         A.B_ID,
                         A.COM_NAME,
                         A.WRITE_NM
                    FROM LMS_BOARD A
                   WHERE 1 = 1 AND b_id = 1
                ORDER BY b_seq DESC) A)
 WHERE page = 1       --보여질 페이지 수


--위드문으로 변경하기
WITH B
        AS (SELECT ROWNUM AS rnum,
                   A.*,
                   FLOOR ( (row_count / 10) - page0 + 1) page
              FROM (  SELECT ROW_NUMBER () OVER (ORDER BY b_seq) AS ROW_NUMBER,
                             COUNT (b_seq) OVER () AS row_count,
                             A.B_SEQ,
                             FLOOR ( (ROWNUM - 1) / 10) AS page0,   --디스플레이수+1
                             A.TITLE,
                             TO_CHAR (A.WRITE_DT, 'yyyymmdd') WRITE_DT,
                             A.B_ID,
                             A.COM_NAME,
                             A.WRITE_NM
                        FROM LMS_BOARD A
                       WHERE 1 = 1 AND b_id = 1
                    ORDER BY b_seq DESC) A)
SELECT *
  FROM B
 WHERE page = 1