데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

qty의 범위가 어떻게 되나요?

예를 들어 qty의 범위가 1~10 정도면 별 문제가 없을 것입니다.

또한 qty의 범위가 10000 ~ 15000 정도라면

역시 너무 많은 행이 생성되기 때문에 성능을 높일 방법이 어차피 없을 것입니다.(원본 테이블 데이타가 많다면..)

그런데

만약 qty가 대부분 1~10 정도 이지만 예외적으로 1000이 되는 경우도 있다면

위의 sql은 level < 1001 이상이 되어야 하는데요...

그러면 성능에 문제가 생길 수도 있습니다.

물론 대부분의 경우 위의 마농님 말씀대로 하시는 것이 상대적으로 가장 성능이 좋습니다.

그런데 질문자님의 질문이 앞의 연속된 질문의 연장선 상에 있다면 order 테이블이 500000건 정도이고

원본 쿼리의 시도가 level < 10000 으로 되어 있는 것으로 보아 qty 데이타가 취할 수 있는 범위에 대하여 고려를 하셔야 할 것 같습니다.

제가 알기 쉽게 아래에 예제를 첨부하였습니다.

먼저 다음과 같이 테이블을 만듭니다.

create table t_temp

select

level id

, round(dbms_random.VALUE(1,5),0) qty

from dual

connect by

level <= 10000

그러면 테이블에는 1 ~ 5의 범위를 갖는 qty가 생성됩니다.(10000 행)

그리고 다음과 같이 level <= 5로 수행합니다.

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

select

a.id

,a.qty

,b.lvl rank

from t_temp a

, (

select

level lvl

from dual

connect by

level <= 5

) b

where b.lvl <= a.QTY

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

2015-04-10 15:07:33 842000

2015-04-10 15:07:35 078000

수행 시간이 2초가 안걸립니다.

다음은 level <= 1000로 실행합니다.

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

select

a.id

,a.qty

,b.lvl rank

from t_temp a

, (

select

level lvl

from dual

connect by

level <= 1000

) b

where b.lvl <= a.QTY

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

2015-04-10 15:08:00 577000

2015-04-10 15:08:03 778000

대략 3초가량 걸립니다.

그 다음은 아예 level <= 100000 으로 실행합니다.

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

select

a.id

,a.qty

,b.lvl rank

from t_temp a

, (

select

level lvl

from dual

connect by

level <= 100000

) b

where b.lvl <= a.QTY

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

2015-04-10 15:09:49 484000

2015-04-10 15:13:36 775000

수행 시간이 4분 가까이 걸립니다.

다음은 max(qty)를 먼저 구합니다.

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

select

a.id

,a.qty

,b.lvl rank

from t_temp a

, (

select

level lvl

from (

select

max(qty) max_qty

from t_temp s

)

connect by

level <= max_qty

) b

where b.lvl <= a.QTY

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

2015-04-10 15:14:39 324000

2015-04-10 15:14:40 782000

대략 2초가 안걸리지만 동일 대상에 두번 select 하는 것이 좀 꺼림직합니다.

만약 원본 테이블에 접근 속도가 현저히 느리다면 그리 좋은 방법이 아닐 수 있습니다.

다음은 collect 를 활용한 것입니다.

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

select

a.id

,a.qty

,b.column_value rank

from t_temp a

,TABLE(select collect(level) from dual connect by level<= a.qty) b

select

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')

from dual

2015-04-10 15:18:14 463000

2015-04-10 15:18:17 019000

2초 좀 넘게 걸립니다.

collect 는 트레이스를 떠보면 내부적으로 pl-sql 호출을 사용하여 절차적으로 수행됩니다.

그러므로 느려질 수 있지만

필요한 만큼만 대가를 지불하는 장점이 있습니다.

대략 정리하면

1) 만약 qty의 범위가 분산이 작고 익히 알려진 값이면 level < :cnt 스타일을 사용

2) 그렇지 않다면 collect 와 max(qty) 중 테스트 해보고 결정

정도가 되지 않을까 싶습니다.

김흥수(protokhs)님이 2015-04-10 10:52에 작성한 댓글입니다.
이 댓글은 2015-04-10 15:25에 마지막으로 수정되었습니다.