ROWNUM은 SELECT절에 의해 추출되는 WHERE절을 만족하는 행(ROW)에 붙는 순번이다. 다시 말해 WHERE절 조건을 만족 시킨 행에 대해 1부터 붙는 순번이다.
ROWNUM은 의사 칼럼으로 참조만 될 뿐 데이터베이스에 물리적으로 저장되지 않는다.
(DESC 명령으로 보이지 않는 의사 칼럼).
SELECT 리스트에 ROWNUM을 이용하는 것도 물론 가능하다.
ORDER BY를 사용한다면 WHERE절까지 만족 시킨 결과에 ROWNUM이 붙은 상태로 ORDER BY가 반영된다.(ORDER BY는 맨 마지막에 실행된다) 즉 ROWNUM은 ORDER BY 실행 전에 부여되며, ORDER BY는 맨 나중에 실행되므로 추출되는 순서대로 ROWNUM이 붙어 있지는 않다.
다른 데이터베이스에서 주로 사용되는 top-N 쿼리의 기능을 ROWNUM을 이용하여 구현할 수 있다.
- ROWNUM을 변경하기 위해 DML(데이터 조작어, INSERT, UPDATE, DELETE)을 사용할 수 없다.
- 주로 <, <= 사용하며 >, >= 인 경우 ROWNUM은 동작하지 않는다.
- ROWNUM = 1은 사용 가능 하지만 ROWNUM > 1, ROWNUM=2 인 경우는 데이터가 추출되지 않는다. (ROWNUM은 WHERE절을 만족하는 레코드에 붙이는 순번이므로 처음 한 건 추출해서 ROWNUM이 2인지 비교한다. 처음 레코드는 ROWNUM이 1, 조건에 맞지 않으므로 버리고 다른 다음 레코드 선택 후 또 ROWNUM이 2인지 비교하지만 역시 새로 추출되는 레코드는 ROWNUM이 1이므로 버리고 새로운 레코드를 추출한다, 이 과정을 반복해 보면 ROWNUM = 2는 도달할 수 없는 값이 됨을 알 수 있다.)
SQL> select rownum, empno, ename from emp;
ROWNUM EMPNO ENAME
---------- ---------- --------------------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
……
14 개의 행이 선택되었습니다.
-- 4건만 SEELCT
SQL> select rownum, empno, ename from emp where rownum < 5;
ROWNUM EMPNO ENAME
---------- ---------- --------------------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
SQL> select rownum, empno, ename from emp;
ROWNUM EMPNO ENAME
---------- ---------- --------------------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
……
14 개의 행이 선택되었습니다.
-- 4건만 SELECT, 먼저 다른 조건없이 4건을 꺼내고 ROWNUM을 1부터 부여 (이름으로 정렬해서 4건을 꺼내는 것은 아님) 후 ename으로 정렬하니 ROWNUM이 깨진다.
-- ORDER BY는 ORDER BY를 제외한 쿼리를 먼저 실행 후 그 결과에 대해 정렬하므로 ROWNUM이 ENAME기준으로 정렬되게 되어 순차적으로 부여되지 않는다.
SQL> select rownum, empno, ename from emp where rownum < 5 order by ename;
ROWNUM EMPNO ENAME
---------- ---------- --------------------
2 7499 ALLEN
4 7566 JONES
1 7369 SMITH
3 7521 WARD
-- 위 쿼리를 이름순으로 정렬한 후 ROWNUM을 부여하려면 다음과 같이 해야한다.
-- 안쪽 SELECT에서 이름 오름차순으로 데이터를 정렬한 후 하나씩 꺼내면서 ROWNUM을 부여하고 --- 5보다 작은지 확인한다.
SQL> select * from
( select rownum, empno, ename
from emp
order by ename )
where ROWNUM < 5;
SQL> select rownum, empno, ename from emp where rownum = 1;
ROWNUM EMPNO ENAME
---------- ---------- --------------------
1 7369 SMITH
-- ROWNUM=2인 경우 선택되는 로우(행)가 없다.
-- WHERE절에 ROWNUM 조건 외에는 없으므로 모든 데이터가 조건을 만족하게 되고, 최초 한건을 읽으면 ROWNUM이 1이되어 ROWNUM = 2 조건에 만족하지 않으므로 버리고, 다음 레코드를 또 읽어 ROWNUM을 부여하는데 이전에 읽은 데이터는 조건에 맞지 않아 버렸으므로 다시 ROWNUM이 1이되어 ROWNUM = 2 조건에 맞지않아 데이터를 버리고 다음 레코드를 읽게 된다. 이런식으로 하다보면 결국 ROWNUM = 2 조건을 만족하는 데이터는 없게된다.
SQL> select rownum, empno, ename from emp where rownum = 2;
선택된 레코드가 없습니다.
-- 처음 한건을 추출하고 ROWNUM이 1보다 큰지 확인 => 거짓이므로 버림, 다음 레코드 선택 후 또 ROWNUM이 1보다 큰지 확인하지만 역시 거짓이므로 버린다. 결국 모두 거짓이 되어 선택된는 레코드는 없다.
SQL> select * from emp where ROWNUM > 1;
선택된 레코드가 없습니다.
이번에는 게시판 페이지 나누기 쿼리를 해보자. 개발할 때 흔히 사용되는 쿼리인데 ROWNUM을 이용하고, 안쪽 SELECT 쿼리에서 ORDER BY를 사용하지 않고 인덱스 및 인덱스와 연관된 힌트를 사용하는 것이 포인트 이다.
myemp1 테이블(1000만건)에서 ename 칼럼을 기준으로 적절한 인덱스를 생성 후 게시판 페이지 쿼리를 작성하시오. (이미 ename 기준으로 오름차순 인덱스가 생성되어 있다.)
- 한페이지당 10개 출력.
- ename를 기준으로 내림차순으로 Dislplay.
- 10,000번째 페이지를 출력하는 쿼리를 작성하시오.
-- 먼저 myemp1 테이블의 ename컬럼에 걸려 있는 인덱스 이름을 확인
SQL> SELECT a.index_name, a.column_name, b.visibility
FROM USER_IND_COLUMNS a, USER_INDEXES b
WHERE a.table_name = 'MYEMP1'
AND a.index_name = b.index_name;
IDX_MYEMP1_ENAME
-- 인덱스가 오름차순으로 생성되었으니 내림차순으로 결과를 보기 위해 INDEX_DESC 힌트를 사용하자.
-- 오라클12C에서 17초
-- 실행계획을 살펴보면 안쪽 SELECT에 FULL TABLE SCAN 한다.
-- ROWNUM은 크거나같다(>=)가 동작하지 않기에 SELECT로 한번더 사서 rnum 이라는 이름으로 비교
-- 맨안쪽 SELECT에서 모든 데이터를 다 읽고 맨 바깥쪽에서 해당 페이지를 추출하므로 느리다.
SELECT *
FROM (
SELECT a.*, ROWNUM rnum
FROM (
SELECT empno, ename, sal
FROM myemp1 e
ORDER BY ENAME DESC
) a
) b
WHERE rnum <= 10000*10
AND rnum >= 9999*10 +1;
-- 오라클12C에서 14초
-- 실행계획을 살펴보면 안쪽 SELECT에 INDEX RABGE SCAN 한다.
SELECT *
FROM (
SELECT a.*, ROWNUM rnum
FROM (
SELECT /*+ INDEX_DESC(e IDX_MYEMP1_ENAME)*/
empno, ename, sal
FROM myemp1 e
WHERE ename > '가'
) a
) b
WHERE rnum <= 10000*10
AND rnum >= 9999*10 +1;
-- 0초
-- 안쪽 SELECT에서 ROWNUM으로 한계치를 미리 지정하여 건수를 줄임
SELECT *
FROM (
SELECT a.*, ROWNUM rnum
FROM (
SELECT empno, ename, sal
FROM myemp1 e
--WHERE ROWNUM <= 10000*10
ORDER BY ENAME DESC -- order by 때문에 rownum있어서 한번더 싼 SELECT에서 ROWNUM비교
) a
WHERE ROWNUM <= 10000*10
)
WHERE rnum >= 9999 * 10 +1;
-- 0초
-- 안쪽 SELECT에서 ROWNUM으로 한계치를 미리 지정하여 건수를 줄임
-- ORDER BY를 사용 안하니 직접 ROWNUM으로 비교 가능하므로 SELECT 절이 하나 빠짐
select empno, ename, sal
from
(
select /*+ index_desc(e IDX_MYEMP1_ENAME) */
rownum rnum,
empno,
ename,
sal
from myemp1 e
where rownum <= 10000*10
)
where rnum >= 9999*10+1;
