Oracle 데이터베이스에서 ROWNUM은 쿼리 결과 집합의 각 행에 순차적인 번호를 부여하는 가상 컬럼(pseudocolumn)입니다. 이 번호는 쿼리 실행 중에 동적으로 할당되며, 특히 결과 집합의 크기를 제한하거나 페이지네이션을 구현하는 데 매우 유용합니다. 하지만 ROWNUM의 동작 방식에는 몇 가지 중요한 특징이 있어 이를 정확히 이해하고 사용해야 합니다.
ROWNUM은 SELECT 문 처리 과정에서 논리적으로 FROM 및 WHERE 절이 처리된 후, 그리고 ORDER BY 절이 적용되기 전에 할당됩니다. 이러한 처리 순서는 ROWNUM을 사용할 때 예상치 못한 결과가 나올 수 있는 주된 이유가 됩니다.
기본적인 ROWNUM 할당
기본 테이블에서 ROWNUM은 Oracle이 데이터를 읽어오는 순서에 따라 1부터 순차적으로 부여됩니다. 만약 쿼리에 특정 정렬 기준이 명시되어 있지 않다면, ROWNUM의 순서는 물리적 저장 순서나 쿼리 최적화 방식에 따라 달라질 수 있습니다.
SELECT
ROWNUM AS sequence_num,
e.employee_id,
e.employee_name
FROM
employees e
WHERE
e.department_id = 10;
정렬된 결과와 ROWNUM의 관계
ROWNUM이 할당된 후 ORDER BY 절이 적용되면, ROWNUM의 번호는 정렬된 결과와 일치하지 않을 수 있습니다. 예를 들어, 직원들의 급여 순으로 정렬하여 ROWNUM을 확인하려 하면 다음과 같이 의도와 다른 결과가 나올 수 있습니다.
SELECT
ROWNUM AS current_rn,
e.employee_name,
e.salary
FROM
employees e
ORDER BY
e.salary DESC;
위 쿼리에서 current_rn은 급여가 높은 순서와 무관하게 할당됩니다. ROWNUM이 특정 정렬 순서에 기반하여 부여되도록 하려면, 반드시 서브쿼리를 사용하여 먼저 정렬을 수행한 후 외부 쿼리에서 ROWNUM을 할당해야 합니다.
SELECT
ROWNUM AS sorted_sequence,
ordered_employees.employee_name,
ordered_employees.salary
FROM
(SELECT
e.employee_name,
e.salary
FROM
employees e
ORDER BY
e.salary DESC) ordered_employees;
이 방식은 먼저 salary를 기준으로 정렬된 결과 집합을 생성한 다음, 이 결과 집합의 각 행에 sorted_sequence를 순서대로 부여하여 원하는 정렬된 ROWNUM을 얻을 수 있게 합니다.
ROWNUM을 이용한 결과 제한 및 주의사항
ROWNUM은 특정 개수의 행만 조회할 때 자주 사용됩니다. 그러나 WHERE 절에서 ROWNUM을 사용할 때는 몇 가지 중요한 제약 사항이 있습니다.
- 첫 번째 행 조회 (
ROWNUM = 1)
ROWNUM은 항상 1부터 시작하므로,ROWNUM = 1조건은 항상 첫 번째로 읽히는 행을 성공적으로 반환합니다.SELECT * FROM employees WHERE ROWNUM = 1; N번째 행 조회 (ROWNUM = N, N > 1)
ROWNUM = 2와 같은 조건은 어떠한 결과도 반환하지 않습니다. 이는ROWNUM이 1부터 순차적으로 부여되기 때문입니다. Oracle은 첫 번째 행을 가져와ROWNUM=1을 부여한 후ROWNUM=2조건을 검사합니다. 이 조건은 거짓이므로 첫 번째 행은 버려지고, 따라서 두 번째 행은ROWNUM=2를 받을 기회를 얻지 못합니다. 이 과정이 반복되므로 어떤 행도 조건을 만족하지 못하게 됩니다.ROWNUM > N(N > 1)
마찬가지로ROWNUM > 1과 같은 조건도 직접 사용하면 데이터를 찾을 수 없습니다. Oracle이 첫 번째 행을 가져와ROWNUM=1을 부여하지만, 이 행은ROWNUM > 1조건을 만족하지 못하므로 버려집니다. 결과적으로 두 번째 행은 절대ROWNUM=2를 받을 기회를 얻지 못하고, 쿼리는 어떠한 행도 반환하지 않습니다.ROWNUM < N또는ROWNUM <= N
이 조건은ROWNUM의 순차 할당 방식과 잘 맞아 떨어지므로 정상적으로 작동합니다.ROWNUM <= 5는 첫 번째부터 다섯 번째까지의 행을 반환합니다.SELECT e.employee_id, e.employee_name FROM employees e WHERE ROWNUM <= 5;
특정 범위의 행 조회 (페이지네이션)
ROWNUM을 사용하여 특정 범위, 예를 들어 2번째부터 5번째까지의 행을 조회하려면 서브쿼리를 활용해야 합니다. 외부 쿼리에서 ROWNUM을 조건으로 직접 사용하는 것은 위에서 언급한 이유로 인해 불가능합니다.
SELECT
r_alias.employee_id,
r_alias.employee_name
FROM
(SELECT
ROWNUM AS row_index,
e.employee_id,
e.employee_name
FROM
employees e
WHERE
ROWNUM <= 5) r_alias
WHERE
r_alias.row_index >= 2;
이 쿼리는 먼저 내부 서브쿼리에서 최대 5개의 행을 가져오면서 ROWNUM에 row_index라는 별칭을 부여합니다. 그 다음 외부 쿼리에서 이 row_index가 2 이상인 행들을 필터링하여 2번째부터 5번째까지의 행을 얻습니다.
효율적인 페이지네이션 기법
Oracle에서 대량의 데이터에 대한 페이지네이션을 구현할 때는 ROWNUM의 특성을 고려한 최적화된 쿼리 작성이 중요합니다.
- 정렬이 필요 없는 페이지네이션 (최고 효율)
특정 정렬 순서가 중요하지 않고 단순히 특정 조건에 맞는 데이터 중 일부를 가져오려는 경우에 가장 효율적인 방법입니다.SELECT * FROM ( SELECT ROWNUM AS page_row_num, t.employee_id, t.employee_name, t.hire_date FROM employees t WHERE t.hire_date BETWEEN TO_DATE('20220101', 'YYYYMMDD') AND TO_DATE('20220630', 'YYYYMMDD') AND ROWNUM <= 20 -- 먼저 상위 N개 행 제한 ) page_data WHERE page_data.page_row_num >= 11; -- 그 중에서 하위 M개 행 필터링이 쿼리는 먼저 2022년 상반기에 고용된 직원 중 처음 20개 행을 가져와
page_row_num을 부여합니다. 그런 다음, 이 20개 행 중에서page_row_num이 11 이상인 행, 즉 11번째부터 20번째 행을 선택합니다. - 정렬이 필요한 페이지네이션 (효율적)
특정 컬럼 기준으로 정렬된 결과를 페이지별로 가져와야 하는 경우입니다.ORDER BY가ROWNUM할당 전에 적용되도록 서브쿼리를 두 번 중첩하여 사용합니다.SELECT * FROM ( SELECT ordered_data.*, ROWNUM AS page_index FROM ( SELECT e.employee_id, e.employee_name, e.hire_date, e.salary FROM employees e WHERE e.hire_date BETWEEN TO_DATE('20210701', 'YYYYMMDD') AND TO_DATE('20211231', 'YYYYMMDD') ORDER BY e.hire_date DESC, e.employee_id ASC -- 여기서 정렬 수행 ) ordered_data WHERE ROWNUM <= 30 -- 정렬된 결과에서 상위 N개 행 제한 ) final_page WHERE final_page.page_index >= 21; -- 그 중에서 하위 M개 행 필터링이 쿼리는 가장 안쪽 서브쿼리에서 먼저
hire_date와employee_id기준으로 정렬된 결과를 만듭니다. 그 다음 중간 서브쿼리에서 정렬된 결과에ROWNUM을page_index로 부여하며, 상위 30개 행으로 제한합니다. 마지막으로 외부 쿼리에서page_index가 21 이상인 행, 즉 21번째부터 30번째 행을 추출합니다. 이 방식은ROWNUM이 정렬된 결과에 올바르게 적용되도록 보장하면서 효율적인 페이지네이션을 가능하게 합니다.