오라클 로우넘(ORACLE ROWNUM)

 오라클 로우넘(ORACLE ROWNUM)

​

오라클 ROWNUM

재생 수10

00:00 | 15:58

ROWNUM은 SELECT절에 의해 추출되는 WHERE절을 만족하는 행(ROW)에 붙는 순번이다. 다시 말해 WHERE절 조건을 만족 시킨 행에 대해 1부터 붙는 순번이다.

​

ROWNUM은 SELECT절에 의해 추출되는 WHERE절을 만족하는 행(ROW)에 붙는 순번이다. 다시 말해 WHERE절 조건을 만족 시킨 행에 대해 1부터 붙는 순번이다.

​

https://www.youtube.com/channel/UC9LYhJdG5r7jImSUpnp1q1g

오라클자바커뮤니티

풀스택개발 강좌. 자바, 스프링, 닷넷, WPF, 자마린, 오라클, SQL, Angular

www.youtube.com

​

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;

​