15. Top-N 쿼리

Top-N 쿼리는 상위 N개의 행을 조회하는 쿼리이다.

15.1 기본 문법

오라클에서는 세 가지 방식의 Top-N 쿼리를 사용할 수 있다.

15.1.1 ROWNUM 방식

ROWNUM 방식은 전통적인 Top-N 쿼리 방식이다. ORDER BY 절로 행을 정렬하고, 정렬된 행을 ROWNUM 슈도 칼럼으로 제한한다. 행이 반환되는 순서대로 순번을 반환하고, 행이 반환될 때마다 순번이 증가한다.

-- ROWNUM은 1부터 시작하고 행이 반환될 때마다 순번이 증가하기 때문에 ROWNUM = 2 조건은 항상 FALSE이다.
SELECT empno, sal, ROWNUM AS rn FROM emp WHERE ROWNUM = 2;

-- ROWNUM 컬럼은 < 조건이나 <= 조건을 사용해야 한다.
SELECT empno, sal, ROWNUM AS rn FROM emp WHERE ROWNUM <= 2;

-- 정렬을 한 뒤 갯수 제한을 하고 싶은 경우, 인라인 뷰를 사용해야 한다.
SELECT empno, sal, EOWNUM AS rn
FROM (SELECT empno, sal FROM ORDER BY sal, empno)
WHERE ROWNUM <= 5;

경품 추첨 등 무작위로 n개의 행을 조회하는 경우 ORDER BY 절에 DBMS_RANDOM.VALUE 함수를 사용할 수 있다. 하지만 문맥 전환에 의한 성능 저하가 발생할 수 있으므로 DBME_RANDOW.VALUE 함수 대신 ORA_HASH 함수를 사용하여 성능개선을 할 수 있다.

15.1.2 분석 함수 방식

-- ROW_NUMBER를 이용한 Top-N 쿼리
SELECT *
FROM (SELECT empno, sal, ROW_NUMBER() OVER (ORDER BY sal, empno) AS rn FROM emp)
WHERE rn <= 5
ORDER BY sal, empno;

-- ROW_NUMBER를 이용한 페이징 쿼리
SELECT *
FROM (SELECT empno, sal, ROW_NUMBER() OVER (ORDER BY sal, empno) AS rn FROM emp)
WHERE rn BETWEEN (:v_pr * (:v_pn - 1)) + 1 AND :v_pr * v_pn
ORDER BY sal, empno;

-- PERCENT_RANK 함수를 사용하여 백분율에 의한 Top-N 쿼리
SELECT empno, sal, pr
FROM (SELECT empno, sal, PERCENT_RANK() OVER (ORDER BY sal, empno) AS pr FROM emp)
WHERE pr <= 0.25
ORDER BY sal, empno;

15.1.3 ROW LIMITING 절

ROW LIMITING은 ANSI 표준 SQL 문법이다. ROW LIMITING 절은 ORDER BY 절 다음에 기술하고, ORDER BY 절과 함께 수행된다.

• OFFSET offset : 건너뛸 행의 개수를 지정
• FETCH : 반환할 행의 개수나 백분율을 지정
• ONLY : 지정된 행의 개수나 백분율만큼 행을 반환
• WITH TIES : 마지막 행에 대한 동순위를 포함해서 반환

SELECT empno, sal
FROM emp
ORDER BY sal, empno
FETCH FIRST 5 ROWS ONLY;

PERCENT 키워드를 사용하면 반환할 행의 백분율을 지정할 수 있다.

15.2 고급 주제

15.2.1 Top-N 쿼리와 조인

-- 조인 후 Top-N 처리를 수행 emp 테이블의 데이터가 14건이 조회되면 조인도 14번 수행하고, 2건의 결과만 반환한다.
SELECT empno, sal, deptno, dname
FROM (SELECT a.empno, a.sal, a.deptno, b.name
        FROM emp a, dept b
        WHERE b.deptno(+) = a.deptno
        ORDER BY a.sal, a.empno)
WHERE ROWNUM <= 2;

15.2.2 Top-N 쿼리와 UNION ALL 연산자

-- UNION ALL 연산자를 사용하여 연결한 결과를 Top-N 처리를 수행한다. 결과 집합을 정렬해야 하므로 소트 부하가 발생할 수 있다.
SELECT *
FROM (SELECT 1 AS tp, deptno AS no, dname AS name FROM dept
        UNION ALL
        SELECT 2 AS tp, empno AS no, ename AS name FROM emp
        ORDER BY tp, no)
WHERE ROWNUM <= 3;