Google AdSense (text)

hidden logo stop

Moving

거지 같은 이글루스 광고노출 정책이 싫어서,
새 보금자리(http://blog.leocat.kr/)로 이사감.

[Oracle] ROWNUM 사용 시 주의 Computer & Program

똑같은 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;


덧글

  • 초보개발자 2010/12/22 13:48 # 삭제 답글

    검색하다 들렀습니다. 좋은 정보 감사합니다. 저도 비슷한 쿼리로..결과가 좀 이상해서 검색했는데..역시 오라클 돌아가는 원리를 모르고 쿼리를 짠거였더군요. 감사합니다 ^^
  • Sigel 2010/12/23 14:27 #

    저희는 저거 보고 깜딱 놀랐었습니다. @ㅅ@
    별 의심 없이 쓰던게 정반대의 결과가 나와서.. =ㅅ=;;
  • Anima 2011/01/31 15:09 # 답글

    검색하다 들렀습니다.(2)
    연습해보다 rownum이 뒤죽박죽 섞여서 나오길래 왠가 했는데
    그런것이군요.. 감사합니다 !ㅎ
  • Sigel 2011/01/31 15:21 #

    운 좋으면 잘 나오고 운 나쁘면 섞여 나오니..
    처음부터 어떻게 동작하는지 알고 써야 할 것 같네요 :)
댓글 입력 영역

Google AdSense (text/image)