1 개요

2 예제

다음과 같은 between join을 하는 쿼리가 있다고 하자.

select
	b.value_code
,	count(a.cust_key)
from tableA a
	inner join tableB b
		on a.val between b.begin_value and b.end_value
when @dt between appl_begin_dt and appl_end_dt
group by
	b.value_code		

tableA는 3천 만 건이고, tableB는 20건이다. 대용량 DB에서 between join은 서버의 사양이 좋건 나쁘건 nested loop join 이기 때문에 전체적인 처리가 끝날 때까지는 많은 시간을 필요로 한다. 이런 경우 동적쿼리를 이용하면 최적화 할 수 있다. 생각해보면 별 것 아니다.

declare 
	@bdt char(8)
,	@edt char(8)
,	@sql varchar(8000)
,	@col varchar(255)

set @bdt = '20101102'
set @edt = '20101103'
set @sql = ''
set @col = 'val'

select @sql = @sql + 
	'when ' + @col + ' between ' + convert(varchar(60), begin_value) + ' and ' + 
	convert(varchar(60), end_value) + ' then ''' + value_code + '''' + char(13)+char(10)
from tableB
where @bdt between appl_begin_dt and appl_end_dt
set @sql = 'case ' + @sql + ' end'

set @sql = '
select 
	' + @sql + ' value_code
,	count(distinct cust_key)
from tableA
where 1=1
and regdate >=' + quotename(@bdt, '''') + '
and regdate < ' + quotename(@edt, '''') + '
group by
	' + @sql
	
print @sql
exec (@sql) 

실제로 다음과 같은 문장으로 실행될 것이다. between join 하는 것보다 조넨 빠르지 않겠나?

select 
	case when umoney between 0 and 499999 then '8등급'
when val between 500000 and 4999999 then '7등급'
when val between 5000000 and 49999999 then '6등급'
when val between 50000000 and 499999999 then '5등급'
when val between 500000000 and 4999999999 then '4등급'
when val between 5000000000 and 49999999999 then '3등급'
when val between 50000000000 and 499999999999 then '2등급'
when val between 500000000000 and 9223372036854775807 then '1등급'
 end value_code
,	count(distinct cust_key)
from tableA
where 1=1
and a.regdate >='20101102'
and a.regdate < '20101103'
group by
	case when umoney between 0 and 499999 then '8등급'
when val between 500000 and 4999999 then '7등급'
when val between 5000000 and 49999999 then '6등급'
when val between 50000000 and 499999999 then '5등급'
when val between 500000000 and 4999999999 then '4등급'
when val between 5000000000 and 49999999999 then '3등급'
when val between 50000000000 and 499999999999 then '2등급'
when val between 500000000000 and 9223372036854775807 then '1등급'
 end