[Oracle] ROWNUM 사용 시 주의

똑같은 query를 다른 두 시스템에서 실행했다. 헌데 그 결과는 완전 정반대.. 이런 어처구니 없는 경우가 있을 수가.. 아주 황당했다. (사실은 query가 바보였음 -ㅅ-)

(실제 테이블을 내용을 쓸 수 없으니 살짝 테이블 이름과 내용을 바꿔서..) 내가 원하는 결과는 사용자 테이블과 로그인한 시간, IP 등을 기록하는 테이블을 조인해서.. 가장 최근의 기록 1개를 얻고 싶다.

SELECT SUB.USER_ID, SUB.LOGIN_TIME
FROM (SELECT U.USER_ID, H.LOGIN_TIME, ROWNUM AS RNUM
FROM USER U, LOGIN_HIST H
WHERE U.USER_ID = H.USER_ID
ORDER BY H.LOGIN_TIME DESC
AND u.user_name = '바보') SUB
WHERE RNUM = 1;

자.. 이 query의 문제는 뭘까?? 사실 이 문제가 발생하기 전 까지는 누가 만들어 놓은건지도 궁금하지 않았던 저 구석에 있던 query인데.. 문제는 이렇게 발생했다. 다음은 동일한 데이터를 가진 두 서버에서 실행한 이 query의 결과이다.

1번 서버
USER_ID | LOGIN_TIME
--------+-----------
test1 | 2010-10-10 10:10:10

2번 서버
USER_ID | LOGIN_TIME
--------+-----------
test1 | 2010-10-05 10:10:10

왜 이런 결과가 나온걸까?? 우선 가장 바깥에 있는 select 문을 벗겨서 subquery 부분만 실행해 봤다. 그랬더니 이런 결과가 나오는 것이 아닌가??

1번 서버
USER_ID | LOGIN_TIME | RNUM
--------+------------+-----
test1 | 2010-10-10 10:10:10 | 1
test1 | 2010-10-09 10:10:10 | 2
test1 | 2010-10-07 10:10:10 | 3
test1 | 2010-10-05 10:10:10 | 4

2번 서버
USER_ID | LOGIN_TIME | RNUM
--------+------------+-----
test1 | 2010-10-10 10:10:10 | 4
test1 | 2010-10-09 10:10:10 | 3
test1 | 2010-10-07 10:10:10 | 2
test1 | 2010-10-05 10:10:10 | 1

아니!! 같은 query인데 RNUM이 정반대로 붙어있는게 아닌가?? 정말 귀신이 곡할 노릇이라고 하면서 검색을 시작.. 흠.. 검색 시작한지 2분도 안돼서 답 발견 @ㅅ@

역시 문제는 ROWNUM이었다. ROWNUM의 동작 원리를 알지 못 한 상태에서 query를 만드니 저런 query가 만들어진 것 같다. 일단, ROWNUM은 select된 결과 집합에 대한 가상 순번이다. 이 번호는 DB에서 꺼내지는 순서대로 붙게 된다. (optimizer가 어떻게 동작하는지에 따라 그 순서는 달라질 수 있다.) 그리고 결정적으로 잘못 생각한 것은 ORDER BY 구문.. ORDER BY는 결과 집합이 생성된 후에 적용되는 조건이다.

이 두 동작 원리를 가지고 다시 문제를 짚어보자. 내가 원하는 것은 해당 이름의 사용자가 가장 최근에 로그인한 시간을 알고 싶은 것이다. 다시 말해 먼저 로그인한 시간으로 정렬한 후 번호를 붙여서 가장 최근 정보를 가져오는 순서다. 헌데.. ROWNUM과 ORDER BY를 하나의 SELECT문에 넣게 되면서 그 적용 순서가 의도와는 다르게 뒤바뀌게 된 것이다. 결과 집합을 꺼내오면서 ROWNUM이 먼저 적용되고, 모든 결과 집합이 모인 후에 정렬이 된 것이다. 오라클의 optimizer가 동작하는 경우에 따라서 결과 집합을 만드는 순서가 바뀔 수 있고 위의 두 서버와 같이 정반대의 결과가 나올 수도 있는 것이다.

사실 이 query는 이런 원리를 알고 보면 다음과 같이 수정해면 간단하게 원하는 결과를 얻을 수 있는 것이다. 처음부터 왜 저렇게 ROWNUM을 붙였는지 잘 모르게뜸 @ㅅ@

SELECT SUB.USER_ID, SUB.LOGIN_TIME
FROM (SELECT U.USER_ID, H.LOGIN_TIME
--, ROWNUM AS RNUM 이 분분은 사실 필요 없기 때문에 제거
FROM USER U, LOGIN_HIST H
WHERE U.USER_ID = H.USER_ID
ORDER BY H.LOGIN_TIME DESC
AND u.user_name = '바보') SUB
WHERE ROWNUM = 1;