ROWNUM 이해하기

1. ROWNUM 이란?

ROWNUM은 SELECT 해온 데이터에 일련번호를 붙이는 것이다. 테이블이나, 특정 집합에서 원하는 만큼의 행만 가져오고 싶을 때, 행의 개수를 제한하는 용도로 사용한다.
예) 전체 행에서 TOP 3, 페이징 등

2. ROWNUM의 사용

기본적으로

SELECT * FROM EMP WHERE ROWNUM <= 10;

만 해도 (즉 ROWNUM이라고 명시를 하지 않아도) ROWNUM이 적용되어 추출된다. 10번까지만 나온다.  또는 명시적으로 아래처럼 해줘도 된다.

SELECT ROWNUM, EMP.* FROM EMP WHERE ROWNUM <= 10;

단, 위의 쿼리에서 WHERE 절에 바로 ROWNUM 조건을 BETWEEN 6 AND 10을 사용하는 것은 불가능하다.

왜냐하면, ROWNUM 은 반환되는 쿼리결과의 임시 행번호이기때문에, 반드시 1부터 나와야하기 때문이다. 무조건 1번부터 차례대로 붙여지므로, '<=10'과 같은 조건은 가능하지만 '=5' 나 '>=6' 등의 1번 외의 숫자이상부터 출력할 수는 없다. 그래서 페이징이나 특정 행을 추출할 때, ROWNUM이 포함된 SELECT문 바깥에 한번 더 SELECT를 감싸준다.

여기서 주의할 점은 맨처음에 보았던 것처럼 ROWNUM은 명시적으로 표시해주지 않아도 WHERE 절에서 조건으로 줄 수 있다는 것이다. 그래서 아래의 쿼리문을 실행하게 되면 아무 행도 나오지 않게된다.

SELECT E.* FROM (SELECT ROWNUM, EMP.* FROM EMP) E
WHERE ROWNUM BETWEEN 6 AND 10;

 

내가 원했던 것은 안쪽 괄호의 SELECT 문에 대한 ROWNUM 번호가 6에서 10일때를 가져오는 것이지만, ORACLE이 보기에는 바깥 SELECT문에서 WHERE 절로 ROWNUM을 주고 있으니 바깥 SELECT 문의 쿼리 결과에 대해 ROWNUM을 매기게 되어 6부터 10까지 출력되도록 하기 때문이다.

그래서 예약어인 ROWNUM을 그대로 쓰지않고 별칭을 주는 것이 좋다.

SELECT *
FROM (SELECT ROWNUM AS RN, EMP.* FROM EMP)
WHERE RN BETWEEN 6 AND 10;

 

이렇게 하면 정상적으로 원하는 컬럼만 가져올 수 있게 된다.

 

3. ROWNUM 테이블 정렬하기

ROWNUM은 ORDER BY 적용 이전에 매겨진다. 순서가 먼저 매겨지고 나서 ORDER BY 기준에 따라 정렬되므로, ROWNUM와 ORDER BY의 동시사용은 결과 순서를 뒤죽박죽인 것처럼 만든다. ROWNUM은 쿼리 결과 데이터에 숫자를 붙임으로써 결과 집합을 결정하는데에 관여한다고 할 수 있다. ORDER BY는 결과 집합을 결정하는 요소는 아니다. 순서만 바꿀 뿐이지.

SELECT ROWNUM, EMPNO, ENAME FROM EMP WHERE ROWNUM <= 5 ORDER BY ENAME;

 

그래서 테이블을 먼저 정렬시켜서 가져온 후에, 바깥을 SELECT 로 한번 더 감싸서 ROWNUM으로 일련번호를 먹인다.

SELECT Z.* 
FROM (
	-- 1 DEPTH
        SELECT 
            ROWNUM AS RN, A.*
        FROM (			
	    -- 2 DEPTH
            SELECT 
            	EMPNO
                , ENAME 
            FROM EMP 
            ORDER BY ENAME ASC 	-- ENAME으로 정렬
        ) A
    ) Z
WHERE Z.RN BETWEEN 5 AND 10

 

위처럼 해도 되지만 ROW_NUMBER() 을 사용하면 더 깔끔하게 정렬 후 ROWNUM을 매길 수 있다. 아래의 방법을 더 자주 사용한다. (1 depth)

SELECT 
    A.*
FROM (				
	-- 1 DEPTH
        SELECT 
            EMPNO
            , ENAME 
            , ROW_NUMBER() OVER(ORDER BY ENAME ASC) AS RN	--ENAME으로 정렬
        FROM EMP 
    ) A
WHERE A.RN BETWEEN 5 AND 10

3. oracle로 구현하는 top 함수

SQL Server 에서는 TOP이라는 연산자?를 사용해서 결과 집합으로 출력되는 행의 수를 제한할 수 있다. 이걸 oracle에서는 ROWNUM <=3 으로 구현할 수 있다.
예)
SQL Server : SELECT TOP(3) ENAME FROM EMP;
ORACLE : SELECT ROWNUM, ENAME FROM EMP ROWNUM <= 3;

--SQL Server 
SELECT TOP(3) ENAME FROM EMP; 


--ORACLE
SELECT ROWNUM, ENAME FROM EMP ROWNUM <= 3;