oracle ROWNUM & ROW_NUMBER

페이징을 구현할 때, ORACLE을 사용하면 ROWNUM을 사용한다. 어떤 방식으로 문서에 소개하고 있는지 궁금했고 나아가 관련 함수인 ROW_NUMBER도 궁금하여 같이 조사하게 되었다.

 

ROWNUM이란?

ROWNUM을 사용하면, 각각의 row에 순서대로 값을 부여한 새로운 칼럼이 생성된다. ( 1, 2, 3 ... ) 

 

 

또한, 쿼리로 리턴된 row의 갯수를 제한할 때도 사용이 가능하다.

SELECT * FROM employees WHERE ROWNUM < 11;

 

만약에 ORDER BY를 서브쿼리에 사용하고 탑 레벨에 ROWNUM을 사용하면, ROW가 정렬된 이후에 ROWNUM 조건을 추가할 수 있다. 예를 들어, 다음 쿼리는 employee_id의 가장 작은 10개의 숫자를 리턴한다. 이것을 top-N reporting이라고 한다.

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

 

만약 ORDER BY를 서브쿼리에 사용하지 않고 ROWNUM과 같은 위치에 사용한다면, ROWNUM이 적용된 다음에 정렬을 하기 때문에, 사용해서는 안된다.

SELECT * FROM employees ORDER BY employee_id WHERE ROWNUM < 11; // 잘못된 쿼리!

 

양의 정수 1보다 큰 ROWNUM 테스트 조건을 항상 거짓이다. 예를들어 다음은 어떠한 ROW로 리턴하지 않는다.

SELECT * FROM employees
    WHERE ROWNUM > 1;

검색된 첫번째 ROW는 ROWNUM 1이 할당되는데, ROWNUM > 1에 거짓이기 때문에 더이상 조건을 만족하지 못한다.

 

ROW_NUMBER()란?

ROW_NUMBER는 분석함수이다. 리턴된 각각의 ROW에 고유한 숫자를 할당한다. 1로 시작하며 ORDER_BY_CLASE에서 지정된 ROW들이 순서대로 정렬된다. 

 

구체적 범위에서 ROW_NUMBER를 검색하는 쿼리 내에서 ROW_NUMBER를 사용해 서브쿼리를 중첩하면, 내부 쿼리의 결과로에서 ROW의 정확한 하위 부분을 조회할 수 있다. 함수를 사용하여 top-N, bottom-N, inner-N reporting을 구현할 수 있다. 일관성있는 결과를 위해 쿼리는 정렬순서를 보장해야 한다

SELECT ROW_NUMBER() OVER(ORDER BY a.job) row_num, a* FROM emp a ORDER BY a.job;

 

 

SELECT department_id, last_name, employee_id, ROW_NUMBER()
   OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
   FROM employees;

DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
------------- ------------------------- ----------- ----------
           10 Whalen                            200          1
           20 Hartstein                         201          1
           20 Fay                               202          2
           30 Raphaely                          114          1
           30 Khoo                              115          2
           30 Baida                             116          3
           30 Tobias                            117          4
           30 Himuro                            118          5
           30 Colmenares                        119          6
           40 Mavris                            203          1
. . .
          100 Popp                              113          6
          110 Higgins                           205          1
          110 Gietz                             206          2

PARTION BY를 통해 같은 값들을 묶어서 표현할 수 있다. 일반적으로 파티션을 친다고 생각하면 편하다. 각 파티션은 1로 시작하고 같은 파티션에 속하는 ROW가 많을수록 높은 숫자를 가진다. 

 

 

SELECT last_name FROM 
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;

조금 더 간편하게, 위의 방식으로 51~100번째 사이의 last_name만 조회할 수 있다. 마치 MYSQL의 limit 함수와 비슷하다.