심심풀이 10탄 (인덱스를 타기위한 몸부림)

html 버젼으로 올리려구 했는데 안올라가네요.
이글을 보지말고 맨 아래에 있는 html로 된 첨부파일을 보시는것이 보기가 조금더 좋을듯...


경우에 따라서 sql-server에서 인덱스를 활용하기가 힘이드는때가 있다.
실행계획 특히나 그래픽실행계획을 대충봐서는 인덱스를 제대로 활용하는 것 같기는 하지만
조금만 자세히 들여다 보면 그렇지 않은 경우가 간혹 있다. 그러한 것을 보여주기 위한
심심풀이입니다. 아, 이런 경우도 있구나 하구 참고만하세요.

인덱스를 효율적으로 사용하기 위해서는 앞선컬럼들이 Equal조건이어야 한다.
like 나 between 과 같은 범위조건이 오게되면 효율적인 인덱스의 활용을 기대하기 어렵다.
그러나, 경우에 따라서는 이러한 경우에도 때에 따라서는 효율적인 인덱스 활용이 가능하다.
항상그런것은 아니다. 경우에 따라서 이다.
아래 예제는 어거지성 예제이므로 이렇게 사용할수도 있다는것을 참고만 하시길...

그동안 실행계획이 들어간것들에 대해서 어렵다는 댓글이 많아서 엄청나게
실행계획을 간단하게 처리하였습니다.
자세한 실행계획을 보실려면 직접쿼리분석기 상에서 확인해보세요.
(근데 아무리 생각해두 실행계획을 너무단순하게 줄여놓은듯...)

/* ============================================================================== */
복합인덱스에서 첫번째컬럼이 like나 between과 같은 범위검색 조건일 경우의
효율적인 인덱스활용에 대해서 이제부터 보도록 하지요...
/* ============================================================================== */
set statistics io off
set statistics profile off

--더미테이블이 필요하니까 만들고
--drop table dumy_no
create table dumy_no(no int not null primary key, no2 char(2))
create unique index idx on dumy_no(no2)
go
set nocount on
declare @i int
set @i = 1
while (@i < 100)
begin
   insert into dumy_no values(@i, right('0'+convert(varchar(2), @i),2))
   set @i = @i + 1
end
set nocount off

--역시나 필요하니까 만들구
--drop table dumy_dt
create table dumy_dt(dt char(8) not null primary key, dt1 datetime not null)
create unique index idx on dumy_dt(dt1)
go
set nocount on
declare @dt datetime
set @dt = '2000-01-01'
while (@dt <= '2003-12-31')
begin
insert into dumy_dt
       values (convert(varchar(8),@dt,112), @dt)
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
go
select * from dumy_dt
/* ------------------------------------------------------------------------------ */
--어거지성 샘플 테이블과 자료 만들고(어디까지나 테스트를 위한 어거지성 샘플임)
--drop table a
create table a(c1 char(8), c2 int, c3 int, c4 int, qty int)
go
--인덱스테스트가 주목적이므로 클러스트를 사용하지 않았다. pk도 없다.
create index idx on a(c1, c2, c3, c4)
go
set nocount on
declare @dt datetime
set @dt = '2002-01-01'
declare @i int, @j int, @k int
while(@dt <= '2002-04-30')
begin
   set @i = 1  
   while(@i <= 100)
   begin
      set @j = 1
      while(@j <= 10)
      begin
         set @k = 1
         while(@k <= 10)
         begin
            insert into a
               values(convert(varchar(8),@dt,112),
                      @i,@j,@k,
                      convert(int, rand()*10000))
            set @k = @k + 1
         end
         set @j = @j + 1
      end
      set @i = @i + 1
   end
   set @dt = dateadd(dd, 1, @dt)
end
set nocount off

select count(*) from a --120만건(만드는데 대략 5분정도 소요)
----------------------------------
set statistics io on
set statistics profile on

먼저 풀테이블 스캔을 통하여 page수 체크
select * from a
'a' 테이블. 스캔 수 1, 논리적 읽기 수 5129

/* ============================================================================== */
1) 첫번째 컬럼만 가지고 하는것은
/* ============================================================================== */
select * from a where c1 = '20020201' --1만건
  |--Table Scan(a)
'a' 테이블. 스캔 수 1, 논리적 읽기 수 5129

강제로 인덱스를 타게끔 하면
select * from a with(index(idx)) where c1 = '20020201' --1만건
  |--Bookmark Lookup
       |--Index Seek(a.idx)
'a' 테이블. 스캔 수 1, 논리적 읽기 수 10045

100만건중 1만건(1%)을 찾아오는데 인덱스를 활용하는것보다는 테이블스캔이 훨씬 효율이 더 좋다.
간혹 자료를 보면 10%, 5% 미만일경우는 인덱스를 활용하는것이 좋다는 이야기가 나오기도 하는데
절대적인것이 아니다. 즉, 상대적인 것이다.
데이터량이 적을 경우에는 상관이 없겠지만 100만건이상 또는 1억건이상의 대용량이 된다면
그러한 수치는 아무짝에도 쓸모가 없게된다. 풀스캔의 성능또한 막강한것이다.
무조건 풀스캔이 나쁜것은 절대루 아니다.
언제나 성능을 따질때는 절대적인 기준을 놓구서 이야기 할수 없다. 상대적인 것이다.

/* ============================================================================== */
2) 두번째 컬럼까지만 조건을 주어서 테스트
/* ============================================================================== */
--like + Equal 조건
declare @c1 varchar(8), @c2 int
set @c1 = '200202%'  --2월 한달치 자료를 보기위한조건...
set @c2 = 1
select * from a where c1 like @c1 and c2 = @c2

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
2800  1     |--Bookmark Lookup
2800  1        |--Nested Loops
1     1           |--Compute Scalar
1     1           |  |--Constant Scan
2800  1           |--Index Seek(a.idx)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3979

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1] > [Expr1008] AND [a].[c1] < [Expr1009]), 
   WHERE:([a].[c2]=[@c2] AND like([a].[c1], [@c1], NULL)) ORDERED FORWARD)

실행계획을 자세히 들여다 보면 index seek의 실행계획이 나오기는 했지만
첫번째 컬럼만을 사용했고 두번째컬럼은 filtering으로 처리되었다.
이런경우는 말이 index seek이지 거의 index scan이라고 봐도 무방할 정도이다.
여기에 함정이 있는것이다. 그래픽 실행계획을 볼때에 놓치기 쉬운 부분이다.
그래팩 실행계획을 볼때에도 꼼꼼히 seek조건을 확인해봐야한다.
/* ------------------------------------------------------------------------------ */
--between + Euqal 조건
declare @dt1 char(8), @dt2 char(8), @c2 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c2 = 1
select * from a where c1 between @dt1 and @dt2 and c2 = @c2

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
2800  1     |--Bookmark Lookup
2800  1        |--Index Seek(a.idx)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3938

like보다는 논리적읽기수가 조금 적다. 하지만 너무 많은 페이지를 읽었다.

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:(([a].[c1], [a].[c2]) >= ([@dt1], [@c2])
     AND ([a].[c1], [a].[c2]) <= ([@dt2], [@c2])), 
   WHERE:([a].[c2]=[@c2]) ORDERED FORWARD)

역시나 실행계획을 자세히 보면 index seek 을 하기는 했지만 정상적으로 두개의 컬럼을 가지고
index seek을 한것이 아니라 앞의 like와 같이 첫번째 컬럼만을 사용했고 두번째컬럼은
filtering으로 처리되었다.
즉, seek조건을 보면 c1컬럼을 조건처리하기는 했지만 c2컬럼을 조건으로 준 1만을
찾는것이 아니라 dt조건으로 준 20020201 부터 20020228 까지의 모든 데이터를
다 읽고나서 c2컬럼이 1인것을 filtering하고 있다.
그래서 논리적읽기수가 3938페이지나 된것이다.(역시나 원하는 실행계획이 아니다)
/* ------------------------------------------------------------------------------ */
-- in-subquary + Equal 조건
-- 물론, where c1 in ('20020201','20020202'...'20020228') 이런식으로
-- 28개를 나열할수도 있겠지만 좀 그렇다.

declare @dt1 char(8), @dt2 char(8), @c2 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c2 = 1
select * from a
where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 = @c2

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
2800  1     |--Bookmark Lookup
2800  1        |--Nested Loops
28    1           |--Clustered Index Seek(dumy_dt.pk),
2800  28          |--Index Seek(a.idx)

'a' 테이블. 스캔 수 28, 논리적 읽기 수 2928
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2

앞의 두가지 경우와 비교해보면 논리적읽기수가 1000페이지 이상 차이가 난다.

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[dumy_dt].[dt] AND [a].[c2]=[@c2]) ORDERED FORWARD)

실행계획을 자세히보면 첫번째컬럼과 두번째컬럼을 둘다 사용하여 정확히 원하는
조건에 해당하는것들만 index seek을 하였다. filtering을 하는 where조건이 없다.

그렇지만 경우에 따라서 in-subquary가 먼저읽히지 않고 나중에 읽히기도 한다.
그럴경우는 일반적인 조인구문을 사용하여 그것을 조정하던지 힌트등을 사용하여 조정해야한다.
/* ------------------------------------------------------------------------------ */
그래두 궁금한분들을 위해서 날짜컬럼에 28개의 조건을 대입해보면

declare @dt1  char(8), @dt2  char(8), @dt3  char(8), @dt4  char(8), @dt5  char(8), @dt6  char(8)
declare @dt7  char(8), @dt8  char(8), @dt9  char(8), @dt10 char(8), @dt11 char(8), @dt12 char(8)
declare @dt13 char(8), @dt14 char(8), @dt15 char(8), @dt16 char(8), @dt17 char(8), @dt18 char(8)
declare @dt19 char(8), @dt20 char(8), @dt21 char(8), @dt22 char(8), @dt23 char(8), @dt24 char(8)
declare @dt25 char(8), @dt26 char(8), @dt27 char(8), @dt28 char(8)
declare @c2 int
set @dt1  = '20020201'
set @dt2  = '20020202'
set @dt3  = '20020203'
set @dt4  = '20020204'
set @dt5  = '20020205'
set @dt6  = '20020206'
set @dt7  = '20020207'
set @dt8  = '20020208'
set @dt9  = '20020209'
set @dt10 = '20020210'
set @dt11 = '20020211'
set @dt12 = '20020212'
set @dt13 = '20020213'
set @dt14 = '20020214'
set @dt15 = '20020215'
set @dt16 = '20020216'
set @dt17 = '20020217'
set @dt18 = '20020218'
set @dt19 = '20020219'
set @dt20 = '20020220'
set @dt21 = '20020221'
set @dt22 = '20020222'
set @dt23 = '20020223'
set @dt24 = '20020224'
set @dt25 = '20020225'
set @dt26 = '20020226'
set @dt27 = '20020227'
set @dt28 = '20020228'
set @c2 = 1

select * from a
where c1 in (@dt1 ,@dt2 ,@dt3 ,@dt4, @dt5, @dt6, @dt7, @dt8, @dt9, @dt10,
             @dt11,@dt12,@dt13,@dt14,@dt15,@dt16,@dt17,@dt18,@dt19,@dt20,
             @dt21,@dt22,@dt23,@dt24,@dt25,@dt26,@dt27,@dt28)
and c2 = @c2

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
2800  1     |--Bookmark Lookup
2800  1        |--Nested Loops
28    1           |--Sort
28    1              |  |--Constant Scan
2800  28             |--Index Seek(a.idx)

'a' 테이블. 스캔 수 28, 논리적 읽기 수 2928

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[Expr1002] AND [a].[c2]=[@c2]) ORDERED FORWARD)

실행계획을 보면 앞의 in-subquary를 사용한 경우와 동일하다.
다만 쿼리문이 좀 그렇다는것만 빼고는...
조건으로 들어오는 달이 3월 이거나 또는 4월로 계속 바뀌면 어떻게 쿼리하나...
그렇다구해도 계속 동적쿼리를 사용한다면 뭐, 말리지는 않겠다.
/* ============================================================================== */
3) 세번째 컬럼까지 테스트해보자
  (3번째 조건이 Equal 조건이다. 이것을 사용하는것이 목적이다)
/* ============================================================================== */
위의 테스트내용을 계속이어받아서 첫번째 컬럼 조건은 in-subquary로 한다.
이번에는 두번째 조건이 문제가 되는것이다.
두번째 컬럼 조건을 어떻게 주어야 정확히 원하는 부분만 읽고서 잘 처리할까 ???
/* ------------------------------------------------------------------------------ */
-- between 사용
declare @dt1 char(8), @dt2 char(8)
declare @c21 int, @c22 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c3 = 9
select * from a
where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 between @c21 and @c22
and c3 = @c3

Rows  Exec  StmtText
----  ----  ----------------------------------------------------------------------------- 
1120  1     |--Hash Match
80    1        |--Clustered Index Seek(dumy_dt.pk)
4800  1        |--Table Scan(a)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 5129
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2

|--Table Scan(OBJECT:([tempdb].[dbo].[a]),
   WHERE:(([a].[c2]>=[@c21] AND [a].[c2]<=[@c22]) AND [a].[c3]=[@c3]))

table scan이 나왔다.
즉, table scan을 하면서 두번째컬럼과 세번째컬럼을 가지고 filtering을 하고있다. 
/* ------------------------------------------------------------------------------ */
-- in-subquary사용

declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c3 = 9
select * from a
where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 in (select no from dumy_no where no between @c21 and @c22)
and c3 = @c3

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120    1   |--Hash Match
4       1      |--Clustered Index Seek(dumy_no.pk)
28000   1      |--Hash Match
28      1         |--Clustered Index Seek(dumy_dt.pk)
120000  1         |--Table Scan(a)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 5129
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2
'dumy_no' 테이블. 스캔 수 1, 논리적 읽기 수 2

|--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:([a].[c3]=[@c3]))

말이 안나온다. a테이블을 죄다 읽었다.
세번재컬럼만을 가지고 filtering 하면서 120만건을 죄다 읽었다...
/* ------------------------------------------------------------------------------ */
-- c2조건이 몇개안되니까 그냥 in 조건으로 한번 해보자.

declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c23 int, @c24 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 2
set @c23 = 3
set @c24 = 4
set @c3 = 9
select * from a where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 in (@c21,@c22,@c23,@c24)
and c3 = @c3

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120  1     |--Bookmark Lookup
1120  1        |--Nested Loops
28    1           |--Clustered Index Seek(dumy_dt.pk)
1120  28          |--Nested Loops
112   28             |--Merge Interval
112   28             |    |--Sort
112   28             |         |--Compute Scalar
112   28             |              |--Concatenation
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                        |--Constant Scan
1120  112            |--Index Seek(a.idx)

'a' 테이블. 스캔 수 112, 논리적 읽기 수 1495
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[dumy_dt].[dt] AND [a].[c2] > [Expr1013] AND [a].[c2] < [Expr1014]), 
   WHERE:([a].[c3]=[@c3]) ORDERED FORWARD)

역시 나열하니까 제대로 읽었다. 그렇지만 여전히 세번째컬럼은 seek조건이 아니라
filtering조건으로 사용되었다.
/* ------------------------------------------------------------------------------ */
-- index hint를 사용해봤다.

declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c23 int, @c24 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 2
set @c23 = 3
set @c24 = 4
set @c3 = 9
select * from a with(index(idx))
where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 in (@c21,@c22,@c23,@c24)
and c3 = @c3

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120  1     |--Bookmark Lookup
1120  1        |--Nested Loops
28    1           |--Clustered Index Seek(dumy_dt.pk)
1120  28          |--Nested Loops
112   28             |--Merge Interval
112   28             |    |--Sort
112   28             |         |--Compute Scalar
112   28             |              |--Concatenation
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                   |    |--Constant Scan
28    28             |                   |--Compute Scalar
28    28             |                        |--Constant Scan
1120  112            |--Index Seek(a.idx)

'a' 테이블. 스캔 수 112, 논리적 읽기 수 1495
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[dumy_dt].[dt] AND [a].[c2] > [Expr1013] AND [a].[c2] < [Expr1014]), 
   WHERE:([a].[c3]=[@c3]) ORDERED FORWARD)

두번째컬럼에다가 상수값을 나열하고도 인덱스힌트를 사용하였지만 위와 동일하게 세번째컬럼을
제대로 활용하지 못했다.
/* ------------------------------------------------------------------------------ */
-- in-subquary를 사용하고 인덱스힌트를 사용하여보자.

declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c3 = 9
select * from a with(index(idx))
where c1 in (select dt from dumy_dt where dt between @dt1 and @dt2)
and c2 in (select no from dumy_no where no between @c21 and @c22)
and c3 = @c3

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120   1    |--Hash Match
4      1       |--Clustered Index Seek(dumy_no.pk)
28000  1       |--Bookmark Lookup
28000  1          |--Nested Loops
28     1             |--Clustered Index Seek(dumy_dt.pk)
28000  28            |--Index Seek(a.idx)

'a' 테이블. 스캔 수 28, 논리적 읽기 수 29280
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2
'dumy_no' 테이블. 스캔 수 1, 논리적 읽기 수 2

정말로 어처구니가 없이 많은 페이지를 읽었다.
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[dumy_dt].[dt]),  WHERE:([a].[c3]=[@c3]) ORDERED FORWARD)
두번째 컬럼은 어느조건에도 없다. 첫번째컬럼만을가지고 seek을 하면서
세번째컬럼만을 가지고 filtering하고 있다.

정말로 방법이 없는걸까 ?
/* ------------------------------------------------------------------------------ */
-- 이번에는 좀다른 꽁수를 부려보자.
(미리 더미테이블끼리 조인시켜서 인라인뷰로 활용 : 경우에 따라서는 유용한(?) 편법이다.)

declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c3 = 9
select * from (select dt, no from dumy_dt cross join dumy_no
                where dt between @dt1 and @dt2
                  and no between @c21 and @c22) x
inner join a
on  a.c1 = x.dt
and a.c2 = x.no
and a.c3 = @c3

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120    1   |--Hash Match
28      1      |--Clustered Index Seek(dumy_dt.pk)
4800    1      |--Hash Match
4       1         |--Clustered Index Seek(dumy_no.pk)
120000  1         |--Table Scan(a)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 5129
'dumy_no' 테이블. 스캔 수 1, 논리적 읽기 수 2
'dumy_dt' 테이블. 스캔 수 1, 논리적 읽기 수 2

dumy_dt와 dumy_no를 먼저 cross join시켜서 두개의 컬럼을 만들어두고서 join으로 유도하였다.
그러나, 의도한 실행계획이 안나오고 지멋대로 쿼리를 재해석해버렸다.

이럴때는 정말로 짜증이다... 으 웬수같은 sql-server...
/* ------------------------------------------------------------------------------ */
set forceplan on
go
declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c3 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c3 = 9
select * from (select dt, no from dumy_no cross join dumy_dt
                where dt between @dt1 and @dt2
                  and no between @c21 and @c22) x
inner join a
on a.c1 = x.dt
and a.c2 = x.no
and a.c3 = @c3
go
set forceplan off
go

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120  1     |--Bookmark Lookup
1120  1        |--Nested Loops
112   1           |--Sort
112   1           |    |--Nested Loops
4     1           |         |--Clustered Index Seek(dumy_no.pk)
112   4           |         |--Clustered Index Seek(dumy_dt.pk)
1120  112         |--Index Seek(a.idx)


'a' 테이블. 스캔 수 112, 논리적 읽기 수 1492
'dumy_dt' 테이블. 스캔 수 4, 논리적 읽기 수 8
'dumy_no' 테이블. 스캔 수 1, 논리적 읽기 수 2

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[dumy_dt].[dt] AND [a].[c2]=[dumy_no].[no] AND [a].[c3]=[@c3]) ORDERED FORWARD)

set forceplan on 옵션을 사용하고서야 간신히 제대로된 페이지스캔이 나왔다.
즉, 첫번째, 두번째, 세번째 컬럼을 모두다 정상적으로 사용했다.

/* ------------------------------------------------------------------------------ */
비교를 해보기 위해서 전체컬럼을 동적쿼리식으로 나열해보자.

set forceplan off
go
select * from a
where a.c1 in ('20020201','20020202','20020203','20020204','20020205','20020206',
               '20020207','20020208','20020209','20020210','20020211','20020212',
               '20020213','20020214','20020215','20020216','20020217','20020218',
               '20020219','20020220','20020221','20020222','20020223','20020224',
               '20020225','20020226','20020227','20020228')
and a.c2 in (1,2,3,4)
and a.c3 = 9

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
1120  1     |--Bookmark Lookup
1120  1        |--Nested Loops
28    1           |--Sort
28    1           |    |--Constant Scan
1120  28          |--Index Seek(a.idx)

'a' 테이블. 스캔 수 112, 논리적 읽기 수 1482

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[Expr1002] AND [a].[c2]=1 AND [a].[c3]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=2 AND [a].[c3]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=3 AND [a].[c3]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=4 AND [a].[c3]=9) ORDERED FORWARD)

seek조건을 보면 or조건으로 풀어서 실행을 했다.
논리적읽기수는 1482로 가장적다. 즉, 위에서 in-subquary로 사용한것보다 10page가 적은
읽기수이다. 그렇기는 하지만 역시 동적쿼리이다.
/* ============================================================================== */
4) 네세번째 컬럼까지 테스트해보자
/* ============================================================================== */
이것역시 위의 조건을 이어받아야 하므로 해볼것두 없이 그냥 위와 동일하게 처리
/* ------------------------------------------------------------------------------ */
set forceplan on
go
declare @dt1 char(8), @dt2 char(8), @c21 int, @c22 int, @c31 int, @c32 int, @d4 int
set @dt1 = '20020201'
set @dt2 = '20020228'
set @c21 = 1
set @c22 = 4
set @c31 = 1
set @c32 = 4
set @d4 = 9
select * from (select d3.dt, d1.no as no1, d2.no as no2
                 from dumy_no d1 cross join dumy_no d2 cross join dumy_dt d3
                where d3.dt between @dt1 and @dt2
                  and d1.no between @c21 and @c22
                  and d2.no between @c31 and @c32) x
inner join a
on a.c1 = x.dt
and a.c2 = x.no1
and a.c3 = x.no2
and a.c4 = @d4
go
set forceplan off
go

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
448   1     |--Bookmark Lookup
448   1        |--Nested Loops
448   1           |--Sort
448   1           |    |--Nested Loops
16    1           |         |--Nested Loops
4     1           |         |    |--Clustered Index Seek(dumy_no.pk)
16    4           |         |    |--Clustered Index Seek(dumy_no.pk)
448   16          |         |--Clustered Index Seek(dumy_dt.pk)
448   448         |--Index Seek(a.idx)

'a' 테이블. 스캔 수 448, 논리적 읽기 수 1867
'dumy_dt' 테이블. 스캔 수 16, 논리적 읽기 수 32
'dumy_no' 테이블. 스캔 수 5, 논리적 읽기 수 10

|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[d3].[dt] AND [a].[c2]=[d1].[no] AND [a].[c3]=[d2].[no] AND [a].[c4]=[@d4]) ORDERED
FORWARD)

/* ------------------------------------------------------------------------------ */
마지막으로 전체조건을 동적쿼리식으로 한번처리해보자.

set forceplan off
go
declare @dt1  char(8), @dt2  char(8), @dt3  char(8), @dt4  char(8), @dt5  char(8), @dt6  char(8)
declare @dt7  char(8), @dt8  char(8), @dt9  char(8), @dt10 char(8), @dt11 char(8), @dt12 char(8)
declare @dt13 char(8), @dt14 char(8), @dt15 char(8), @dt16 char(8), @dt17 char(8), @dt18 char(8)
declare @dt19 char(8), @dt20 char(8), @dt21 char(8), @dt22 char(8), @dt23 char(8), @dt24 char(8)
declare @dt25 char(8), @dt26 char(8), @dt27 char(8), @dt28 char(8), @dt29 char(8), @dt30 char(8)
declare @dt31 char(8)
declare @c21 int, @c22 int, @c23 int, @c24 int
declare @c31 int, @c32 int, @c33 int, @c34 int
declare @c41 int
set @dt1  = '20020201'
set @dt2  = '20020202'
set @dt3  = '20020203'
set @dt4  = '20020204'
set @dt5  = '20020205'
set @dt6  = '20020206'
set @dt7  = '20020207'
set @dt8  = '20020208'
set @dt9  = '20020209'
set @dt10 = '20020210'
set @dt11 = '20020211'
set @dt12 = '20020212'
set @dt13 = '20020213'
set @dt14 = '20020214'
set @dt15 = '20020215'
set @dt16 = '20020216'
set @dt17 = '20020217'
set @dt18 = '20020218'
set @dt19 = '20020219'
set @dt20 = '20020220'
set @dt21 = '20020221'
set @dt22 = '20020222'
set @dt23 = '20020223'
set @dt24 = '20020224'
set @dt25 = '20020225'
set @dt26 = '20020226'
set @dt27 = '20020227'
set @dt28 = '20020228'
set @c21 = 1
set @c22 = 2
set @c23 = 3
set @c24 = 4
set @c31 = 1
set @c32 = 2
set @c33 = 3
set @c34 = 4
set @c41 = 9
declare @sql varchar(8000)
set @sql = 'select * from a '
set @sql = @sql + ' where a.c1 in ('''  + @dt1  + ''',''' + @dt2  + ''','''
                    + @dt3  + ''',''' + @dt4  + ''',''' + @dt5  + ''','''
                    + @dt6  + ''',''' + @dt7  + ''',''' + @dt8  + ''','''
                    + @dt9  + ''',''' + @dt10 + ''',''' + @dt11 + ''','''
                    + @dt12 + ''',''' + @dt13 + ''',''' + @dt14 + ''','''
                    + @dt15 + ''',''' + @dt16 + ''',''' + @dt17 + ''','''
                    + @dt18 + ''',''' + @dt19 + ''',''' + @dt20 + ''','''
                    + @dt21 + ''',''' + @dt22 + ''',''' + @dt23 + ''','''
                    + @dt24 + ''',''' + @dt25 + ''',''' + @dt26 + ''','''
                    + @dt27 + ''',''' + @dt28 + ''')'
--if  월비교하여 29에서 31일까지 더 추가하는 루틴...
set @sql = @sql + ' and a.c2 in (' + convert(varchar(10), @c21) + ',' +
                                     convert(varchar(10), @c22) + ',' +
                                     convert(varchar(10), @c23) + ',' +
                                     convert(varchar(10), @c24) + ')'
set @sql = @sql + ' and a.c3 in (' + convert(varchar(10), @c31) + ',' +
                                     convert(varchar(10), @c32) + ',' +
                                     convert(varchar(10), @c33) + ',' +
                                     convert(varchar(10), @c34) + ')'
set @sql = @sql + ' and a.c4 = ' + convert(varchar(10), @c41)
exec(@sql)

아마도 이런식의 쿼리가 되지 않을까... 정말로 끔찍한 쿼리문이다.
실제로 각 변수가 대입되고 나면 아래와 같은 쿼리문이 완성되어서 실행될것이다.

select * from a
where a.c1 in ('20020201','20020202','20020203','20020204','20020205','20020206',
               '20020207','20020208','20020209','20020210','20020211','20020212',
               '20020213','20020214','20020215','20020216','20020217','20020218',
               '20020219','20020220','20020221','20020222','20020223','20020224',
               '20020225','20020226','20020227','20020228')
and a.c2 in (1,2,3,4)
and a.c3 in (1,2,3,4)
and a.c4 = 9

Rows  Exec  StmtText
----  ----  --------------------------------------------- 
448   1     |--Bookmark Lookup
448   1        |--Nested Loops
28    1           |--Sort
28    1           |    |--Constant Scan
448   28          |--Index Seek(a.idx)

'a' 테이블. 스캔 수 448, 논리적 읽기 수 1815
           
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]),
   SEEK:([a].[c1]=[Expr1002] AND [a].[c2]=1 AND [a].[c3]=1 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=1 AND [a].[c3]=2 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=1 AND [a].[c3]=3 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=1 AND [a].[c3]=4 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=2 AND [a].[c3]=1 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=2 AND [a].[c3]=2 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=2 AND [a].[c3]=3 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=2 AND [a].[c3]=4 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=3 AND [a].[c3]=1 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=3 AND [a].[c3]=2 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=3 AND [a].[c3]=3 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=3 AND [a].[c3]=4 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=4 AND [a].[c3]=1 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=4 AND [a].[c3]=2 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=4 AND [a].[c3]=3 AND [a].[c4]=9 OR
         [a].[c1]=[Expr1002] AND [a].[c2]=4 AND [a].[c3]=4 AND [a].[c4]=9) ORDERED FORWARD)

제대로 실행계획이 나왔다. 하지만 동적쿼리이고 정말로 끔직한 쿼리문을 사용해야한다...
/* ------------------------------------------------------------------------------ */
동적쿼리를 사용할경우는 특별히 힌트나 기타 다른 편법을 사용하지 않아도 제대로된
실행계획이 나온다. 모든 쿼리를 동적쿼리로 한다면야 할말이 없지만
적어도 동적쿼리를 사용하지 않을 생각이 있다면 일반적으로는 그냥 사용하여도 되지만
경우에 따라서는 위의 예에서와 같은 경우는 여러가지 편법이나
필요할경우 힌트까지도 사용하여 실행계획이 제대로 풀리게끔 해주어야 한다.

어떤 방법으로 쿼리할지는 개발자 맘이지만 그래두 한번쯤은 생각해봐야할 문제인듯...
/* ------------------------------------------------------------------------------ */