ROWNUM의 동작 원리
- ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudo column)
- ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당, 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미
- ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, sort, aggregation(GROUP BY)이 수행되기 이전에 할당
- ROWNUM 값은 할당된 이후에만 증가(increment)될수 있다.
- 처리 순서
1. FROM/WHERE 절이 먼저 처리
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment)
3. SELECT가 적용
4. GROUP BY 조건이 적용
5. HAVING이 적용
6. ORDER BY 조건이 적용
SELECT *
FROM emp
WHERE ROWNUM <= 5
ORDER BY sal DESC;
→ 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬한다 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행되므로 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건이 의미가 없음. 올바르게 작성된 쿼리는 아래와 같다.
SELECT *
FROM ( SELECT * FROM emp ORDER BY sal DESC )
WHERE ROWNUM <= 5;
ROWNUM을 이용한 Top-N 쿼리 프로세싱
- 일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용
- ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공한다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있음
SELECT * FROM t
ORDER BY unindexed_column;
→ 매우 많은 I/O 작업이 발생하게 되고 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높다.
SELECT *
FROM ( SELECT * FROM t ORDER BY unindexed_column )
WHERE ROWNUM < :N;
→ 전체 로우를 정렬하는 대신 N 개의 로우만을 정렬하면 되므로 물리적 I/O가 발생하지 않고 쿼리의 실행시간과 런타임 리소스를 줄일 수 있다.