MySQL · PostgreSQL 에서 쓰던 LIMIT 10 을 오라클에 그대로 넣으면 ORA-00933: SQL command not properly ended 가 난다. 오라클에는 LIMIT 절이 없다.
12.1 부터 ANSI 표준 문법을 지원한다. 이것이 표준이고 가장 읽기 쉽다.
SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
건너뛰기와 함께 쓰면 페이지 나누기가 된다.
SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
동점 처리도 지정할 수 있다. WITH TIES 는 마지막 행과 정렬 키가 같은 행을 모두 포함한다. 10 을 요청해도 11 건이 나올 수 있다.
SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;
비율로도 자를 수 있다.
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 PERCENT ROWS ONLY;
ROWNUM 은 정렬 전에 매겨지는 의사 열이다. 그래서 다음은 틀린 쿼리다. 임의의 10 건을 뽑은 뒤 그것만 정렬한다.
-- 잘못된 예
SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY salary DESC;
정렬을 먼저 끝낸 인라인 뷰를 만들고, 그 바깥에서 ROWNUM 을 건다.
SELECT *
FROM ( SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC )
WHERE ROWNUM <= 10;
중간 구간을 뽑으려면 ROWNUM 을 열로 고정한 뒤 다시 감싼다. ROWNUM > 20 같은 조건은 항상 거짓이 되므로 한 겹으로는 안 된다.
SELECT *
FROM ( SELECT a.*, ROWNUM rn
FROM ( SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC ) a
WHERE ROWNUM <= 30 )
WHERE rn > 20;
ROWNUM <= 30 을 안쪽에 두는 것이 중요하다. 이렇게 해야 옵티마이저가 상위 N 최적화를 걸어 전체를 정렬하지 않는다.
부서별 급여 상위 3명처럼 그룹 안에서 잘라야 하면 ROW_NUMBER() 를 쓴다.
SELECT dept_id, emp_id, name, salary
FROM ( SELECT dept_id, emp_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
FROM employees )
WHERE rn <= 3;
동점을 모두 살리려면 RANK(), 순위를 건너뛰지 않으려면 DENSE_RANK() 를 쓴다.
ORDER BY 없이 자른 결과는 매번 달라질 수 있다. 정렬 키가 유일하지 않으면 페이지를 넘길 때 같은 행이 두 번 나오거나 빠질 수 있으므로, 정렬 키 뒤에 기본키를 덧붙여 순서를 확정한다.
OFFSET 이 커질수록 느려진다. 깊은 페이지가 필요하면 마지막으로 본 키를 조건으로 넘기는 방식(키셋 페이지네이션)으로 바꾼다.
FETCH FIRST 는 내부적으로 분석 함수로 다시 쓰이므로, 옛 ROWNUM 방식이 더 빠른 사례도 있다. 실행 계획을 비교해 고른다.