Offset , Limit (페이징 처리)

12c 이전의 Oracle은 Top-N쿼리를 구현하기 위해 ROWNUM을 사용해 왔습니다.

하지만 이는 쿼리를 View로 감싸야 하고 순서보장을 원한시에는 한번더 View로 감싸 쿼리가 복잡해지는 단점이 있었습니다.

Oracle 12c부터 Row limiting 절을 이용하여 기존에 사용하던 방식을 간단하게 풀어 Top-N 쿼리를 구현할 수 있게 되었습니다.

 

 

구문의 이해

[ OFFSET offset { ROW | ROWS } ]

[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ]

 

OFFSET 건너뛸 행의 수를 지정, 즉 시작 행을 정함. 생략 시 offset 값은 0

FETCH 반환할 row 수를 지정

FIRST | NEXT 의미상으로만 구분하기 위한 구문. 어느것을 써도 무방

rowcount | percent rowcount는 해당 수만큼 행을 반환,percent를 사용할 경우 해당 비율만큼 반환,음수 지정 시 0으로 반환

ONLY | WITH TIES 

ONLY를 지정 할 경우 지정한 행수 만큼 리턴

WITH TIES를 지정할 경우 마지막 행과 같은 행도 반환. Order by 절을 지정해야 함

 

제약 사항

For Update 절과 함께 사용할 수 없음

Sequence의 Nextval,Curval과 함께 사용할 수 없음

M-View의 Incremental Refresh를 사용할 수 없음

 

 

TEST

1.테스트테이블 생성 (총 2백만건의 row 생성)

TEST@ysbae> create table rownum_test(val number);

Table created.

  1  insert all into rownum_test
  2  into rownum_test
  3* select level from dual connect by level <= 1000000
TEST@ysbae> /

2000000 rows created.

Elapsed: 00:00:01.45
TEST@ysbae> commit;

Commit complete.

 

2.전체 Rows 중 역방향 정렬하여 0.001%만 출력

TEST@ysbae> select val from rownum_test order by 1 desc fetch first 0.001 percent rows only;

       VAL
----------
   1000000
   1000000
    999999
    999999
    999998
    999998
    999997
    999997
    999996
    999996
    999995
    999995
    999994
    999994
    999993
    999993
    999992
    999992
    999991
    999991

20 rows selected.

 

3.뒤에서 부터 5개 값 출력

TEST@ysbae> select val from rownum_test order by 1 desc fetch first 5 rows only;
 
       VAL
----------
   1000000
   1000000
    999999
    999999
    999998

5 rows selected.

 

4.뒤에서부터 5개값 출력(타이기록 포함)

TEST@ysbae> select val from rownum_test order by 1 desc fetch first 5 rows with ties ;
 
       VAL
----------
   1000000
   1000000
    999999
    999999
    999998
    999998

6 rows selected.

 

5.앞에서부터 5개값 출력(타이기록 포함)

TEST@ysbae> select val from rownum_test order by 1  fetch first 5 rows with ties ;
 
       VAL
----------
         1
         1
         2
         2
         3
         3

6 rows selected.

 

 

Row Limiting 절과 페이징 처리

 

val을 기준으로 order by 한 후 5번째 행에서 8번째 행까지 select 하는 쿼리를 만들고 싶습니다.

 

- 기존의 페이징 처리

 TEST@ysbae> SELECT /*+ gather_plan_statistics */ val
 FROM (SELECT val, rownum AS rnum
 FROM (SELECT val
 FROM rownum_test
 ORDER BY val)
 WHERE rownum <= 8)
 WHERE rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

 

-12c 이후의 페이징 쿼리

TEST@ysbae> select /*+ gather_plan_statistics */ val
  from rownum_test
  order by val
  offset 4 rows fetch next 4 rows only;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

 

 

 

출처 : http://dasinfo.co.kr/data/bbs/file_s3OSnr.pdf