ROWNUM은 Oracle 에서 제공하는 가상 컬럼으로써, Select 절에서 사용할 경우에는 추출하는 데이터의 순번을 부여하는 용도로 사용되고, Where 절에 사용할 경우에는 추출할 데이터 중 일부 데이터만 가져올 용도로 사용된다. 특히 Where 절에 사용되는 ROWNUM은 인덱스의 특성을 이용하며, 부분범위 처리로 유도할 수 있어 잘 사용하면 효율적인 SQL이 된다.
ROWNUM 데이터를 먼저 추출한 이후 조회하자.
select *
from (select *
from rownum_t1
order by c1)
where rownum >=1
and rownum <=5 ;
C1 C2 C3
---------------------- -- ----------------------
1 C 101
2 D 102
3 E 103
4 F 104
5 G 105
5 rows selected
select *
from (select *
from rownum_t1
order by c1)
where rownum >=6
and rownum <=10 ;
C1 C2 C3
---------------------- -- ----------------------
0 rows selected
=> ROWNUM의 특성: ROWNUM 은 SQL 결과 셋에 순번을 부여하는 가상 컬럼으로써, Fetch 단계에서 추출하는 첫 번째 로우에 1을 부여한다. 그리고 다음 로우가 추출 될 때마다 ROWNUM의 값을 순차적으로 증가시킨다. 그런데 1일 존재하지 않는다면, 다음 순번을 부여할 수 없으므로 아무런 데이터가 추출되지 않는다.
* ROWNUM 의 잘못된 사용 사례
WHERE ROWNUM = N (N > 1)
WHERE ROWNUM > N (N > 1)
* SQL[2] 처리 예=>
select c1,c2, rnum
from (
select rownum rnum,
t1.*
from (select * from rownum_t1 order by c1) t1
)
where rnum >= 6 and rnum <= 10;
C1 C2 RNUM
---------------------- -- ----------------------
6 H 6
7 I 7
8 J 8
9 K 9
10 L 10
5 rows selected
----------------------------------------
select *
from (
select t1.*,
rownum as rnum
from rownum_t1 t1
order by c1
)
where rnum >= 6 and rnum <= 10;
---------------------------------------------
C1 C2 C3 RNUM
---------------------- -- ---------------------- ----------------------
6 H 106 6
7 I 107 7
8 J 108 8
9 K 109 9
10 L 110 10
5 rows selected
생각해볼 Query
select t1.*,
rownum as rnum
from rownum_t1 t1
order by c1 desc
C1 C2 C3 RNUM
---------------------- -- ---------------------- ----------------------
10 L 110 10
9 K 109 9
8 J 108 8
7 I 107 7
6 H 106 6
5 G 105 5
4 F 104 4
3 E 103 3
2 D 102 2
1 C 101 1
10 rows selected
fetch 할때 rownum 이 적용 ? order by 는 언제 적용 ?
다음장에서 설명
ORDER BY 와 ROWNUM을 같은 위치에 두지 말자
SQL 구성 요소들의 수행순서 => 1. FROM, WHERE 절을 처리, 2.ROWNUM 조건 적용, 3.SELECT COLUMN LIST 절을 적용, 4. GROUP BY 절을 적용, 5. HAVING 절을 적용, 6.ORDER BY 절을 적용
즉, ORDER BY 절이 ROWNUM 조건과 동일한 위치에 있다면, ROWNUM 조건을 먼저 처리한 이후 ORDER BY 절이 수행된다는 것을 알 수 있다.
* ROWNUM과 ORDER BY 절의 관계
SQL [1]
ROWNUM=1 은 ROWNUM <= 1 로 사용하자
예: Bind 변수를 사용하지 않아, Literal SQL 이 과다하게 수행되는 DB 서버가 있고, 이로 인해 이미 DB 서버의 성능 문제는 심각할 정도라고 가정하자.
Literal SQL : select * from emp where emp_no = 1;
select * from emp where emp_no = 2;
...
select * from emp where emp_no = 100;
모두 다른 SQL 로 처리, 매번 parsing
소스를 select * from emp where emp_no = :var ; 로 처리하는 방법이 가장 좋으나 프로그램 소스를 수정할 시간이 없는 상황이라면, 개선안으로 CURSOR_SHARPING(파라미터)의 설정 값 변경을 고려할 것이다. 이 때 ROWNUM = 1과 ROWNUM <= 1의 작성 형태애 따라 성능 편차가 발생할 수 있다.
CURSOR_SHARING 파라미터 값을 FORCE(또는 SIMILAR)로 변경할 경우, ROWNUM = 1이란 조건은 우리가 의도한 바와는 다르게 수행되어 성능 문제가 발생할 수 있다.
![CURSOR_SHARING = FORSE or SIMILAR 을 적용하였을 경우 동작차이]
!(1) ROWNUM = 1 --> ROWNUM = :"SYS_B_0"
Optimizer 가 상수 처리를 :"SYS_B_0"로 변경한 후 변수 값으로 어떤 값이 입력될지 알 수 없어, 변경 전에는 조건을 만족하는 1건이 추출되면 수행을 멈추었지만, 변경 후에는 전체 데이터를 검색한 이후 1건을 추출하는 방식으로 수행되는 것이다. STOPKEY가 제공되지 않았기 때문에,부분 범위 처리를 할 수 없게 된다.
!(2) ROWNUM <= 1 --> ROWNUM <= :"SYS_B_0"
어떤값인지 알 수는 없지만, "SYS_B_0" 값보다 작거나 같은 값이라는 범위(STOPKEY)가 정해지기 때문에 "SYS_B_0"변수의 값을 확인하여 작거나 같은 데이터를 추출한다. 그러므로 전체 데이터를 처리하지 않고, 1건만 추추라고 SQL의 수행은 종료된다. COUNT STOPKEY
상수 처리할경우 ROWNUM = 1 과 ROWNUM <= 1 이 동일하나 CURSOR_SHARING 변경할 경우 달라지므로 가급적이면 ROWNUM <=1 로 작성하는 습관을 가져야 한다.
INDEX_DESC와 ROWNUM<=1 을 함께 사용하지 말자.
특정 칼럼의 최대 값 또는 최소 값 추출 시 최근까지 가장 빈번하게 사용되는 방식은 INDEX_DESC(또는 INDEX_ASC) 힌트와 ROWNUM <= 1 조건을 함께 사용하는 것이다. 이 방법은 인덱스의 데이터가 정렬되어 있다는 특성을 이용하는 방법, ORDER BY절을 제거해도 정렬 작업을 하지 않기 때문에 성능상 매우 유리한 방법이라고 할 수 있다.
INDEX_DESC 힌트에 지정딘 인덱스 상태가 UNUSABLE 상태이거나, 인덱스가 존재 하지 않는 경웨 잘못된 데이터가 추출될 수 있다는 치명적인 단점을 가지고 있다. INDEX_DESC 힌트가 무시되고. 무작위로 1건의 데이터를 추출하기 때문이다. 속도나 성능 측면에서 매우 효율적인 방법.
효율적인 MIN,MAX 값을 추출할 수 있는 방법에 대해 다음의 세 가지 SQL을 통해 자세히 알아보도록 하자.
예: ROWNUM_T1(C1 NUMBER ,C2 VARCHAR2(2))
CREATE INDEX IDX_01 ON ROWNUM_T1 ( C2, C1);
nineDeveloper
안녕하세요 현직 개발자 입니다 ~ 빠르게 변화하는 세상에 뒤쳐지지 않도록 우리모두 열심히 공부합시다 ~! 개발공부는 넘나 재미있는 것~!