ROWNUM 이야기

오라클에 보면 ROWNUM 가 있다.

임시 칼럼(Pseudocolumn) 인데...

이놈은 오라클 내부적으로 조회된 결과셋의 순위를 갖고 있는 칼럼이다.

보통은 ROWNUM 관련하여 가장 많이 하는 실수 중에 하나가 ORDER BY 와 함께 쓰는 경우.

그냥 간단하게 실행순서는 ROWNUM > ORDER BY 가 된다.

ROWNUM이 ORDER BY 보다 먼저 실행된다.

여기까지는... 왠만한 구글링 해보면 다 나오는 내용이고...

이번에 겪었던 문제는 ROWNUM이 실행계획에서 어떠한 역할을 하게 되느냐이다.

아래와 같은 테이블들이 있다고 가정해보자.

TAB_A TAB_B

COL1 COL2 COL3 COL1 COL5 COL6

A 1 AA A 11 111

B 1 BB A 22 222

A 33 333

위의 테이블을 아래와 같이 OUTER JOIN하게 되면 

SELECT *

  FROM TAB_A

  LEFT OUTER JOIN TAB_B ON (A.COL1 = B.COL1)

위와 같이 되고, 결과는 아래와 같이 되겠다.

EX1)

COL1 COL2 COL3 COL1 COL5 COL6

A 1 AA A 11 111

A 1 AA A 22 222

A 1 AA A 33 333

B 1 BB

보통은 쿼리를 짤때... 그리고 OUTER JOIN 을 하는 경우에는 JOIN 되는 테이블(TAB_B)의 추가 데이터도

보기 위해서 OUTER JOIN을 하기 때문에 위와 같은 JOIN은 아주 정상적이며, 틀리지도 않았고,

자주 사용되는 JOIN이다.

그런데... 한번씩 이런 경우가 생긴다.

TAB_B에 있는 데이터 중에서 TAB_A와 부합하는 데이터 중 아무거나 한건만 가져와도 되는 경우

즉...

EX2)

COL1 COL2 COL3 COL1 COL5 COL6

A 1 AA A 11 111

B 1 BB

COL1 COL2 COL3 COL1 COL5 COL6

A 1 AA A 22 222

B 1 BB

COL1 COL2 COL3 COL1 COL5 COL6

A 1 AA A 33 333

B 1 BB

위이 결과셋 3개 중 아무거나 상관없는 경우가 있을 수 있다.

보통은 이런 경우는... 위의 데이터셋처럼 TAB_B의 모든 데이터를 보겠다는 것이 아니라

TAB_B의 칼럼 중 일부 한두개 정도만 보는 경우일 것이다.(이번의 내 경우처럼)

EX1)과 EX2)는 결과적으로 중요한 차이점은 결과셋의 ROW수가 다르다는 것이다.

데이터나 개발할 결과의 성질에 따라 EX1)처럼 보여지는게 맞는 경우가 있고,

EX2)처럼 보여지는게 맞는 경우가 있을 것이다.

아무튼 EX2) 처럼 보여야 하는 경우 보통은 가장 먼저 떠오르는건 다음과 같은 SUB-QUERY

SELECT A.*

     , (SELECT MAX(B.COL1) FROM TAB_B B WHERE A.COL1 = B.COL1) AS B_COL1

     , (SELECT MAX(B.COL5) FROM TAB_B B WHERE A.COL1 = B.COL1) AS B_COL5

     , (SELECT MAX(B.COL6) FROM TAB_B B WHERE A.COL1 = B.COL1) AS B_COL6

  FROM TAB_A A

TAB_B의 아무거나 한건 이기 때문에 MAX대신 MIN을 써도 되겠지?

그런데 위의 방법은... 당연한 이야기이지만, TAB_A의 결과셋이 많거나,

TAB_B에서 갖고와야 할 COLUMN이 아주 많은 경우에는 좀 곤란하다.

(TAB_B가 100개의 칼럼을 갖고 있는데 그걸 다 갖고 오기 위해서 100개의 SUB-QUERY를 만든...

지옥에서 온 쿼리.... 거기다 TAB_1의 결과셋이 n건이라 하면

n * 100건의 추가 SUB-QUERY가 실행될터... ㄷㄷ)

그래서 SUB-QUERY를 사용하기보다 JOIN을 사용하게 된다.

아무거나 한건 이라는 데 착안해서 생각한 다음과 같은 JOIN

SELECT *

  FROM TAB_A A

  LEFT OUTER JOIN TAB_B B ON (A.COL1 = B.COL1 AND ROWNUM = 1)

오... 굳굳 아주 잘 작동된다.

실행계획을 돌려보아도 아주 깔끔한 실행계획을 보여준다.(물론 위의 예제는 실행계획이... 어찌해도 깔끔하다;;)

이번에 개발하며 봉착하게 된 문제.

다음과 같은 현상에 직면했다.

1. 개발계 DB에서 쿼리 테스트(만족할만한 빠른 속도 나옴)

2. 개발계 DB에서 실행계획 확인(깔끔)

3. 개발계 시스템에서 쿼리 작동확인(만족할만한 빠른 속도 나옴)

4. 운영계 DB에서 쿼리 테스트(만족할만한 빠른 속도 나옴)

5. 운영계 DB에서 실행계획 확인(깔끔)

6. 운영계 시스템에서 쿼리 작동확인(RANDOM하게 빠르거나 아주아주아주아주아주 느린 속도 나옴)

(최초 실행때만 느린 경우는 무조건 TABLE FULL SCAN을 탄다고 보면 된다. 그 이후부터 빠른 조회는, 최초 FULL SCAN으로 조회한 데이터를 메모리에 올려두고 그걸 사용하기 때문에 빠른거고... 지금은 이를테면 다음과 같은거지,

어떤때는 처음 열자마자 빠른데, 그 다음부터 느리고, 어떤때는 처음 열자마자 느린데, 그 다음부터 빠르고... 말 그대로 랜덤...)

6번과 같은 문제를 발견하였다.

OKJSP, GURUBEE 등 몇곳에 문의를 해보아도 제대로 된 답을 찾지 못한 상태로..

운영계 시스템에서 조회속도가 들쭉날쭉? 하거나 개발계만큼 나오지 않는 여러가지 이유가 있다.

내가 아는 정도로는...

-_-

일단 첫번째로... WAS 설정... JEUS 사용할 때 그런 경우가 있었는데, 그때는 JEUS에서 기술지원 나와서 해결해줘서 안타깝게 자세한 원인은 모름...

두번째는... 운영계 DB와 개발계 DB의 환경이 달라서 실행계획이 잘못 풀리는 경우... 이런경우가 일단 대부분이겠다.

그런데 나는 운영계 DB에서 실행계획을 확인했을때도 정상이었다. 운영계 DB툴에서 실행계획을 본 것은 실측 실행계획이 아니고, 실제 운영계에서 돌아갈때의 실행계획이 필요한데... 그건 분석도구 등을 이용해야 했고,

어떻게 하는지도 모른다. 전문 DB업체에서나 가능하겠지.

세번째는... 당연한 이야기이겠지만, 두번째랑 연관되는 문제인데, 쿼리 자체에 함정이 있는 경우다.

바로 위에 지금까지 설명한 ROWNUM 의 문제...

ROWNUM의 실행순서는 간단하게 표현해서

FROM > WHERE > ROWNUM > ORDER BY 정도 되겠다.

속도가 느린 경우는 TABLE FULL SCAN을 해버린 경우이다.

속도가 느리지 않은 경우는 TABLE FULL SCAN이 일어나지 않는 경우이다.

매번 실행할 때마다 옵티마이저가 실행계획을 세울텐데(이번 개발은 조회쿼리를 오라클 FUNCTION으로 만들어서

커서를 반환하는 형식이라 같은 쿼리가 매번 메모리로 올라가지 않는다) 그때마다 실행계획이 뒤죽박죽이 되는 케이스...

결국 TABLE FULL SCAN이 일어나는 원인은 WHERE가 없는 상태에서 ROWNUM을 사용하면, 순위를 메겨야 하기 때문에 기본적으로 오라클이 전체 TABLE FULL SCAN으로 데이터에 순위를 메겨주고, 그중에 한건을 갖고 오기 때문에 느린 것이다.

그렇다면, 개발계DB, 운영계DB에서 실행계획을 확인했을 땐 왜 제대로 된(그 테이블에 딱 하나 있는)

PK 인덱스를 잘 사용했던가?

그건 모르지... 정말 모르겠음;; 정확하게 제대로 알려주는 사람이 없었음;;

그냥 이 글을 그래도 써놓는건... 그리고 결론은...

ROWNUM의 실행순서를 알려주는 곳은 꽤 많다.

하지만 그 실행순서를 생각하면서 ROWNUM을 사용하지 않으면 TABLE FULL SCAN과 같은 재앙의 결과를 가져온다.

(TABLE FULL SCAN이 무조건 나쁜 것은 아니지만)

적어도 ROWNUM은 예측 가능한 보장된 결과를 얻어내기 위해서는 사용하지 말자.

뭐 이정도...

사실은 운영계에서 조회속도가 이렇게 뒤죽박죽인 적은 처음이었어서 이번에 꽤 많이 당황했음....

게다가 나보다 실력 좋은 분들이 많은 전문 사이트에서조차 명확한 원인을 찾기 힘들었으나...

그래도 거기 있는 실력자들이 '분명히 어딘가 TABLE FULL SCAN'이 쓸데없이 일어나고 있다 !

라는 예상 가능한 답변들을 주셔서... 머리속으로 '그런건가?'라고 생각만 하던 것을

확신할 수 있었고, 그럼으로 인해서 ROWNUM을 찾아낼 수 있었다는거...

결과적으로 쿼리는 최대한 COLUMN수를 줄이는 한도 내에서 SUB-QUERY로 만들어버렸다.

덕분에 원치않게 속도가 조금 더 느려지기는 했다. 쩝...

다음 번에는 ROWNUM과 비슷할것 같지만, 완전히 다른 기능의 ROW_NUMBER()와 관련된 경험담... ㄱㄱ