|
MS-SQL > 유용한 쿼리 모음.
집합(SET) : sql은 분명 집합을 이용한다. 커서사용을 자제하자.
교집합, 차집합, 합집합 - 예전에 초등학생 아님 중학생 시절 지겹게(?) 듣던 단어들이지요
/*==========================================================*/
먼저 샘플을 만들어두고
--drop table #a
--drop table #b
create table #a(id int)
create table #b(id int)
go
insert into #a values(1)
insert into #a values(2)
insert into #a values(3)
insert into #a values(4)
insert into #a values(5)
insert into #a values(6)
insert into #a values(7)
insert into #a values(8)
insert into #b values(2)
insert into #b values(4)
insert into #b values(6)
insert into #b values(8)
go
이제, 아주 간단히 보도록 하지요
/*==========================================================*/
1. 교집합 (intersect)
아쉽지만 이런함수는 sql에 없습니다.
(Analysis Services용으로는 있지만 t-sql함수는 아니지요)
그럼 이런경우는 어떻게 할까요 ?
-> in 이나 exists 를 이용하는겁니다. 또는 조인을 사용하지요.
/*==========================================================*/
예제 : 테이블1 과 테이블2 둘다 들어있는것은 ?
1. in 사용
select * from #a
where id in (select id from #b)
2. exists 사용
select * from #a x
where exists(select * from #b y where y.id=x.id)
3. 조인사용
select x.* from #a x
inner join #b y
on (y.id = x.id)
/*==========================================================*/
2. 차집합 (except, minus)
아쉽지만 이런함수는 sql에 없습니다.
(Analysis Services용으로는 있지만 t-sql함수는 아니지요)
그럼 이런경우는 어떻게 할까요 ?
-> not in 이나 not exists 를 이용하는겁니다. 또는 outer join을 사용하는것입니다.
/*==========================================================*/
예제2 : 테이블1 에는 있는데 테이블 2에는 없는 것은 ?
1. not in 사용
select * from #a
where id not in (select id from #b)
2. not exists 사용
select * from #a x
where not exists(select * from #b y where y.id=x.id)
3. outer join 사용
select x.* from #a x
left outer join #b y
on (x.id = y.id)
where y.id is null
/*==========================================================*/
3. 합집합 (union)
유일하게 지원되는 함수입니다.
/*==========================================================*/
예제 : 테이블1 에 있는것과 테이블 2에 있는것을 모두구할때는 ?
1. union 사용 : 중복제거를 위해 sort를 한다.
select * from #a
union
select * from #b
|--Sort(DISTINCT ORDER BY:([Union1004] ASC)) --> 중복제거를 위해서 sort를 한다.
|--Concatenation
|--Table Scan(OBJECT:([tempdb].[dbo].[#a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#b]))
2. union all 사용 : 중복허용
select * from #a
union all
select * from #b
|--Concatenation --> sort가 없다.
|--Table Scan(OBJECT:([tempdb].[dbo].[#a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#b))
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
여기까지 간단하게 집합의 개념을 봤고
이제 in 과 union 에 대하여 조금만 더 살펴보도록 하지요.
/*==========================================================*/
1. in : 주어진 값이 하위 쿼리 또는 목록에 있는 임의의 값과 일치하는지 확인합니다.
구문
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
ex) 회원테이블(아이디, 이름, 레벨)
1 홍길동 1
2 짱구 1
3 뿡뿡이 2
--drop table 회원
create table 회원 (아이디 int, 이름 varchar(10), 레벨 int)
create index idx on 회원(레벨)
go
insert into 회원 values(1, "홍길동", 1)
insert into 회원 values(2, "짱구", 1)
insert into 회원 values(3, "뿡뿡이", 2)
go
select * from 회원 where 레벨 in (1, 2)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[회원]))
|--Index Seek(OBJECT:([pubs].[dbo].[회원].[idx]),
SEEK:([회원].[레벨]=1 OR [회원].[레벨]=2) ORDERED FORWARD)
실헹계획을 보면 레벨 in (1, 2) 구문이 레벨=1 OR 레벨=2 와 같이 or연산으로 변경 되었다.
일반적으로 이러한 in 의 경우 or 연산으로 변경되어 실행된다.
그럼, 좀다른 방법으로
declare @lev1 int, @lev2 int
set @lev1 = 1
set @lev2 = 2
select * from 회원 where 레벨 in (@lev1, @lev2)
|--Table Scan(OBJECT:([pubs].[dbo].[회원]),
WHERE:(([회원].[레벨]=[@lev2]) OR [회원].[레벨]=[@lev1]))
예상했던 index seek 방식이 아니라 table scan 방식이다.
sql-server 가 Cost base 방식의 옵티마이져를 사용하다보니 이런결과가 나온다.
즉, 위 경우에 @lev1만을 가지고 쿼리를 한다면 index seek 방식으로 처리된다.
하지만 위 결과를 보면 단지 2개의 변수를 적용하자 table scan쪽으로 돌아선다.
왜 그럴까 ?
일반적으로 sql-server는 상수값이 쿼리에서 사용되면 정확한 통계정보를 사용하여
index를 사용할지 table scan으로 갈지 결정을 하지만 알수없는 변수값이 들어오면
그냥 평균값을 사용한다고 한다. 그래서 이경우 sql-server가 table scan을 선택한것이다.
이런경우에 힌트를 사용해야 정상적인 index 활용을 한다.
물론, 예제가 엉터리 이지만 실무에서도 한번 테스트 해보기 바란다.
여기 예제와 다르지 않을것이다.
하나더, 일반적으로 sql-server는 앞에서 부터 파싱을 하지만 변수를 사용한 in 의 경우는
반대로 뒤에서부터 파싱을 한다. 이것도 알아둘 필요는 있을듯.
그렇다고해서 in안에 나열된순서 또는 역순으로 정렬이되는것은 절대 아니다.
정렬된 순서를 원한다면 반드시 명시적으로 order by 를 써서 정렬순서를 지정해주어야한다.
declare @lev1 int, @lev2 int
set @lev1 = 1
set @lev2 = null
select * from 회원 where 레벨 in (@lev1, @lev2)
이경우는 어떻게 될까 ?
->
declare @lev1 int, @lev2 int
set @lev1 = null
set @lev2 = null
select * from 회원 where 레벨 in (@lev1, @lev2)
또, 이런 경우는 어떻게 될까 ?
각자 해보시길...
/*-----------------------------------------------------------------------*/
1-1. in-subquary
1. in-subquary의 경우 join문으로 해석이 된다.
2. in-subquary가 먼저 읽힐수도, 나중에 읽힐수도 있다
-> 인덱스 유무, 쿼리유형 등에 따라서 달라진다.
3. 고수가 아니라면 in-subquary 실행시 반드시 실행계획을 확인해보시길 권장합니다.
가끔 이놈이 생각한데로 작동을 안하는수가 있으니 조심...
/*-----------------------------------------------------------------------*/
상품테이블(상품코드, 상품명, 분류코드)
분류테이블(분류코드, 카테고리명, 등급)
이런식으로 되어 있을경우 상품테이블에서 카테고리등급이 "A"인것들의 목록을 보고싶다면
select * from 상품
where 분류코드 in (select 분류코드 from 분류 where 등급 = "A")
이렇게 쿼리를 하면 옵티마이저가
select 상품.*
from 상품 inner join 분류
on 상품.분류코드 = 분류.분류코드
and 분류.등급 = "A"
이런식으로 inner join으로 풀어서 처리를 한다.
이런식의 쿼리문은 대부분의 경우 in-subquary가 먼저 실행되는 조인문으로 해석이 되어
실행계획이 나올것으로 기대하지만 그렇지 않은 경우도 있다.
in-subquary의 경우 일반적으로 in-subquary가 먼저 읽혀서 main quary가 나중에 처리되기를
기대하고 쿼리문을 작성하게 된다.
일반적으로 이렇게 풀리지만 경우에 따라서는 그렇지 않고 main quary가 먼저 읽히면서
in-subquary를 나중에 filter조건으로 처리하는경우가 종종 발생한다.
레코드가 많지 않은경우에는 크게 성능상 문제가 되지 않겠지만 그렇지 않은 경우는
심각한 성능상의 저하를 가져온다. 이것을 주의해야 하는것이다.
그래서, 개발자들은 본인이 고수가 아니라면 실행계획을 확인하여야 한다는 것이다.
위 예제의 경우를 가지고 살펴본다면
in-subquary가 먼저 처리된다면
분류 테이블에서 등급이 "A"인 분류코드들만을 먼저 읽어서 그것을 가지고
상품테이블의 분류코드 인덱스를 사용한다면 최적일것이다.
main-quary가 먼저 처리된다면
상품테이블을 무조건 풀로 읽어서 또는 분류코드 인덱스를 풀로 읽으면서 각 row마다
분류 테이블에서 해당분류코드가 등급이 "A"인지 체크하여 걸러주는 형태로 처리된다.
이런경우 일반적으로 쿼리성능이 떨어지게 되는것이다.
그러면 조인문을 사용하지 왜 굳이 in-subquary를 사용했느냐 ?
위 샘플이 그다지 좋은 샘플이 아니라 그런것이고 아래 샘플을 보자.
use tempdb
go
set statistics profile off
set statistics io off
--예제1.
테스트를 위한 샘플 작성
--drop table 코드
create table 코드(상품코드 int not null, 상품명 varchar(40))
go
alter table 코드
add constraint pk_코드 primary key(상품코드)
go
set nocount on
declare @i int
set @i = 1
while (@i <= 50)
begin
insert into 코드 values(@i, convert(varchar(10), @i) )
set @i = @i + 1
end
set nocount off
go
select count(*) from 코드 --50
--drop table 매출마감
create table 매출마감(상품코드 int not null, 매출일자 char(8) not null, 수량 int)
go
alter table 매출마감
add constraint pk_매출마감 primary key (상품코드, 매출일자)
go
set nocount on
declare @i int, @j int
declare @dt datetime
set @i = 1
set @dt = "2000-01-01"
while (@dt < "2004-01-01") --4년치 샘플자료 만들구
begin
set @j = 1
while (@j <= 50)
begin
insert into 매출마감 values(@j, convert(varchar(8), @dt, 112), convert(int, rand() * 1000) )
set @j = @j + 1
end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
go
select count(*) from 매출마감 --73050
set statistics profile on
set statistics io on
--문제 : 2003 년 5월 마감내역 list ?
1. 일반적으로 그냥 아무생각없이 쿼리를 한다면
select * from 매출마감
where 매출일자 between "20030501" and "20030531"
|--Clustered Index Scan(OBJECT:([T1].[dbo].[매출마감].[pk_매출마감]),
WHERE:([매출마감].[매출일자]>=[@1] AND [매출마감].[매출일자]<=[@2]))
"매출마감" 테이블. 스캔 수 1, 논리적 읽기 수 299, 물리적 읽기 수 0, 미리 읽기 수 0.
index seek이 아니라 index scan방식으로 실헹계획이 풀렸다.
인덱스를 활용을 못한다. 첫번째 컬럼을 사용하지 못하기 때문이다.
2. 첫번째 컬럼을 in-subquary를 사용하여 인위적으로 공급함으로써
정상적으로 인덱스를 활용하게끔 한다.
select * from 매출마감
where 상품코드 in (select 상품코드 from 코드)
and 매출일자 between "20030501" and "20030531"
|--Nested Loops(Inner Join, OUTER REFERENCES:([코드].[상품코드]) WITH PREFETCH)
|--Clustered Index Scan(OBJECT:([T1].[dbo].[코드].[pk_코드]))
|--Clustered Index Seek(OBJECT:([T1].[dbo].[매출마감].[pk_매출마감]),
SEEK:([매출마감].[상품코드]=[코드].[상품코드]
AND [매출마감].[매출일자] >= "20030501"
AND [매출마감].[매출일자] <= "20030531") ORDERED FORWARD)
"매출마감" 테이블. 스캔 수 50, 논리적 읽기 수 112, 물리적 읽기 수 0, 미리 읽기 수 0.
"코드" 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.
정상적으로 인덱스를 잘 활용하였고 쿼리성능또한 훨씬 뛰어나다.
그러나, 이것은 테스트를 위하여 인위적으로 그렇게 한것이고 실무에서는 다양한 상황을
검토하여 사용여부를 판단해야한다. 정말 sql은 어렵다.
이예제의 경우도 샘플을 만들때 4년치를 만들지 않고 1년치만 만들어서 테스트를 해보면
1번 경우처럼 그냥 clustered index scan 을 하는것이 훨씬 효율이 더 좋다.
이예제를 보면 처음 2년정도는 그냥 쿼리하여 clustered index scan 을 유도하고
이후 데이터가 더 많이 쌓이게 되면 in-subquary를 활용하여 clustered index seek 을
유도하는 방식을 사용해야 쿼리효율을 일정하게 유지할수 있다.
이런것이 sql의 재미가 아닐까 ?
--예제2. : in-subquary를 사용하여 인덱스를 사용하게끔 유도한것이 오히려 효율을 떨어뜨리는 경우
테스트를 위한 샘플 작성
set statistics profile off
set statistics io off
--drop table t1
create table t1(dt char(8) not null, seq int not null, qty int, c1 char(100))
go
alter table t1
add constraint pk_t1 primary key (dt, seq)
go
set nocount on
declare @dt datetime
declare @i int, @j int
set @dt = "2000-01-01"
while (@dt < "2004-01-01")
begin
set @i = convert(int, rand() * 100)
set @j = 1
while (@j <= @i)
begin
insert into t1 values(convert(varchar(8),@dt,112),@j,
convert(int,rand()*1000),convert(varchar(10),@j))
set @j = @j + 1
end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
select count(*) from t1 --74007
--drop table dumy_dt
create table dumy_dt(dt char(8) not null, dt1 datetime)
go
alter table dumy_dt
add constraint pk_dumy_dt primary key(dt)
go
set nocount on
declare @dt datetime
set @dt = "2000-01-01"
while (@dt < "2004-01-01")
begin
insert into dumy_dt values( convert(varchar(8), @dt, 112), @dt )
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
--문제 : 1월 일별 자료중 seq 가 3 보다 작은 list ?
set statistics profile on
set statistics io on
select * from t1
where dt like "200301%"
and seq < 3
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt] >= "200301" AND [t1].[dt] < "200302"),
WHERE:([t1].[seq]<3 AND like([t1].[dt], "200301%", NULL)) ORDERED FORWARD)
"t1" 테이블. 스캔 수 1, 논리적 읽기 수 25, 물리적 읽기 수 0, 미리 읽기 수 0.
select * from t1
where dt like "200301%"
and seq in (1, 2)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt] >= "200301" AND [t1].[dt] < "200302"),
WHERE:(like([t1].[dt], "200301%", NULL) AND ([t1].[seq]=2 OR [t1].[seq]=1)) ORDERED FORWARD)
"t1" 테이블. 스캔 수 1, 논리적 읽기 수 25, 물리적 읽기 수 0, 미리 읽기 수 0.
select * from t1
where dt in (select dt from dumy_dt where dt like "200301%")
and seq in (1, 2)
|--Nested Loops(Inner Join, OUTER REFERENCES:([dumy_dt].[dt]) WITH PREFETCH)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[dumy_dt].[pk_dumy_dt]),
SEEK:([dumy_dt].[dt] >= "200301" AND [dumy_dt].[dt] < "200302"),
WHERE:(like([dumy_dt].[dt], "200301%", NULL)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt]=[dumy_dt].[dt] AND [t1].[seq]=1 OR
[t1].[dt]=[dumy_dt].[dt] AND [t1].[seq]=2) ORDERED FORWARD)
"t1" 테이블. 스캔 수 62, 논리적 읽기 수 207, 물리적 읽기 수 0, 미리 읽기 수 0.
"dumy_dt" 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.
위 결과를 보면 이경우는 오히려 in-subquary를 사용하여 index scan 을 index seek 로 유도한것이
오히려 쿼리 효율을 떨어뜨리는 경우이다.
즉, 상황에 따라 판단해야 하는것이다.
모든 경우에 효율적인 쿼리란 별루 없다.
/*-----------------------------------------------------------------------*/
마지막으로 정리를 해보자.
/*-----------------------------------------------------------------------*/
--먼저 샘플만들구
set statistics profile off
set statistics io off
set nocount on
go
drop table 급여
drop table 사원
drop table 직급
drop table 부서
go
create table 부서
( 부서코드 int not null primary key
, 부서명 varchar(40)
, 부서구분 int)
go
create index idx_부서구분 on 부서(부서구분)
create index idx_부서명 on 부서(부서명)
go
insert into 부서 values (1, "개발부", 2)
insert into 부서 values (2, "영업부", 3)
insert into 부서 values (3, "총무부", 1)
insert into 부서 values (4, "인터넷사업부", 2)
insert into 부서 values (5, "하드웨어부", 2)
go
create table 직급
( 직급코드 int not null primary key
, 직급명 varchar(40))
go
create index idx_직급명 on 직급(직급명)
go
insert into 직급 values(1, "사원")
insert into 직급 values(2, "대리")
insert into 직급 values(3, "과장")
insert into 직급 values(4, "차장")
insert into 직급 values(5, "부장")
insert into 직급 values(6, "이사")
go
create table 사원
( 사원번호 int not null
, 이름 varchar(10)
, 입사일자 char(8)
, 직급코드 int
, 부서코드 int)
go
--인덱스 테스트를 목적으로 nonclustered 로 잡았음.
alter table 사원
add constraint pk_사원 primary key nonclustered(사원번호)
create index idx_부서코드 on 사원(부서코드)
create index idx_직급코드 on 사원(직급코드)
create index idx_이름 on 사원(이름)
create index idx_입사일자 on 사원(입사일자)
alter table 사원
add constraint fk_부서 foreign key(부서코드)
references 부서(부서코드)
alter table 사원
add constraint fk_직급 foreign key(직급코드)
references 직급(직급코드)
go
insert into 사원 values (1, "짱구", "19990101", 1, 1)
insert into 사원 values (2, "맹구", "19990501", 1, 1)
insert into 사원 values (3, "뿡뿡이", "20000101", 1, 1)
insert into 사원 values (4, "개똥이", "20000101", 1, 3)
insert into 사원 values (5, "타잔", "20000301", 2, 1)
insert into 사원 values (6, "치타", "20000402", 2, 2)
insert into 사원 values (7, "난장이", "20000405", 2, 1)
insert into 사원 values (8, "백설이", "20001011", 3, 1)
insert into 사원 values (9, "응삼이", "20010304", 3, 4)
insert into 사원 values (10, "푸", "20010404", 4, 5)
insert into 사원 values (11, "티커", "20010404", 4, 1)
insert into 사원 values (12, "뽀빠이", "20021010", 5, 1)
insert into 사원 values (13, "올리버", "20030101", 1, 5)
go
create table 급여
( 사원번호 int not null
, 급여년월 char(6) not null
, 급여 int)
go
alter table 급여
add constraint pk_급여 primary key(사원번호, 급여년월)
alter table 급여
add constraint fk_급여 foreign key(사원번호)
references 사원(사원번호)
go
insert into 급여 values(1, "200212", 100)
insert into 급여 values(2, "200212", 150)
insert into 급여 values(3, "200212", 200)
insert into 급여 values(1, "200301", 100)
insert into 급여 values(2, "200301", 200)
insert into 급여 values(3, "200301", 400)
insert into 급여 values(4, "200301", 500)
insert into 급여 values(5, "200301", 100)
insert into 급여 values(6, "200301", 300)
insert into 급여 values(7, "200301", 100)
insert into 급여 values(8, "200301", 400)
insert into 급여 values(9, "200301", 100)
insert into 급여 values(10, "200301", 200)
insert into 급여 values(11, "200301", 100)
insert into 급여 values(12, "200301", 200)
insert into 급여 values(13, "200301", 100)
insert into 급여 values(1, "200302", 100)
insert into 급여 values(2, "200302", 600)
insert into 급여 values(3, "200302", 100)
insert into 급여 values(4, "200302", 500)
insert into 급여 values(5, "200302", 100)
insert into 급여 values(6, "200302", 400)
insert into 급여 values(7, "200302", 100)
insert into 급여 values(8, "200302", 200)
insert into 급여 values(9, "200302", 100)
insert into 급여 values(10, "200302", 300)
insert into 급여 values(11, "200302", 110)
insert into 급여 values(12, "200302", 100)
insert into 급여 values(13, "200302", 200)
insert into 급여 values(1, "200303", 200)
insert into 급여 values(2, "200303", 600)
insert into 급여 values(3, "200303", 130)
insert into 급여 values(4, "200303", 400)
insert into 급여 values(5, "200303", 100)
insert into 급여 values(6, "200303", 300)
insert into 급여 values(7, "200303", 100)
insert into 급여 values(8, "200303", 120)
insert into 급여 values(9, "200303", 100)
insert into 급여 values(10, "200303", 300)
insert into 급여 values(11, "200303", 150)
insert into 급여 values(12, "200303", 100)
insert into 급여 values(13, "200303", 100)
go
set nocount off
go
set statistics profile on
set statistics io on
/*-----------------------------------------------------------------------*/
1-1-1 in-subquary 가 먼저 읽히는 경우(일반적으로 원하는 형태)
/*-----------------------------------------------------------------------*/
select * from 사원
where 부서코드 in (select 부서코드 from 부서 where 부서구분 = 1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([부서].[부서코드]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[부서].[부서코드]) ORDERED FORWARD)
/*-----------------------------------------------------------------------*/
1-1-2. in-subquary 가 나중에 읽히는 경우
/*-----------------------------------------------------------------------*/
--create index idx_부서코드 on 사원(부서코드)
drop index 사원.idx_부서코드 --테스트를 위해서 강제적으로 인덱스를 지우고
go
select * from 사원
where 부서코드 in (select 부서코드 from 부서 where 부서구분 = 1)
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([사원].[부서코드]))
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1 AND [부서].[부서코드]=[사원].[부서코드]) ORDERED FORWARD)
select * from 사원
where 사원번호 in (select 사원번호 from 급여 where 급여년월 like "2002%")
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([사원].[사원번호]))
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[급여].[pk_급여]),
SEEK:([급여].[사원번호]=[사원].[사원번호]
AND [급여].[급여년월] >= "2002" AND [급여].[급여년월] < "2003"),
WHERE:(like([급여].[급여년월], "2002%", NULL)) ORDERED FORWARD)
이번에는 in-subquary가 나중에 읽혔네요.
즉, 원하는대로 작동을 안한것이지요. 이런경우는 엄청난 비효율을 가져옵니다.
인덱스를 하나만들구
create index idx_급연년월 on 급여(급여년월)
go
select * from 사원
where 사원번호 in (select 사원번호 from 급여 where 급여년월 like "2002%")
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([급여].[사원번호]))
|--Sort(DISTINCT ORDER BY:([급여].[사원번호] ASC))
| |--Index Seek(OBJECT:([tempdb].[dbo].[급여].[idx_급연년월]),
SEEK:([급여].[급여년월] >= "2002" AND [급여].[급여년월] < "2003"),
WHERE:(like([급여].[급여년월], "2002%", NULL)) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[급여].[사원번호]) ORDERED FORWARD)
이제 정상적으로 in-subquary가 먼저 읽혔지요.
인덱스라는 것이 이렇게 엄청난 차이를 가져오는 것입니다.
그렇다고, 이런식으로 인덱스를 무한정 만들수는 없는것이지요.
그래서, 인덱스를 만드는것을 전략이라고들 합니다.
그만큼 많은 경우의 수를 따져보고 쿼리유형을 면밀히 파악한다음
머리카락이 빠질정도로 고민해보고 만들어야하는것이지요.
/*==========================================================*/
2. union : 두 개 이상의 쿼리 결과를 모든 쿼리에 속하는 모든 행의 합으로 구성되는
하나의 결과 집합으로 결합합니다.
이것은 두 테이블의 열을 결합하는 조인을 사용하는 것과는 다릅니다.
UNION 연산자를 사용하여 두 개의 쿼리 결과 집합을 결합할 때는 다음 두 가지 기본 규칙을 따라야 합니다.
열의 개수와 순서가 모든 쿼리에서 동일해야 합니다.
데이터 형식이 호환되어야 합니다.
구문
{ < query specification > | ( < query expression > ) }
UNION [ ALL ]
< query specification | ( < query expression > )
[ UNION [ ALL ] < query specification | ( < query expression > )
[ ...n ] ]
/*-----------------------------------------------------------------------*/
2-1. 사용형태
1. 합집합(위에서 봤으므로 생략) - 온라인설명서에도 설명이 잘되어 있으니 참고
2. Full Outer Join의 대안
3. 실행계획의 분리
/*-----------------------------------------------------------------------*/
2-1-1. Full Outer Join의 대안
/*-----------------------------------------------------------------------*/
--drop table 예금내역
create table 예금내역(통장번호 int, 일련번호 int, 예금일자 char(10), 예금액 int)
insert into 예금내역 values (1, 1, "2003-06-01", 100)
insert into 예금내역 values (1, 2, "2003-06-03", 100)
insert into 예금내역 values (1, 3, "2003-06-05", 100)
insert into 예금내역 values (1, 4, "2003-06-07", 100)
go
--drop table 출금내역
create table 출금내역(통장번호 int, 일련번호 int, 출금일자 char(10), 출금액 int)
insert into 출금내역 values (1, 1, "2003-06-01", 50)
insert into 출금내역 values (1, 2, "2003-06-02", 50)
insert into 출금내역 values (1, 3, "2003-06-05", 50)
문제 : 위와 같이 테이블이 설계되어 있다고 하자. 물론 입금내역과 출금내역은 업무적으로
하나의 테이블로 설계를 했어야 했지만, 이미 저렇게 설계된대로 몇년을 사용해왔고, 설계변경이
힘이 들다는 가정하에서 일자별 입출금내역을 보자
1. Full Outer Join사용
select case when x.통장번호 is not null then x.통장번호 else y.통장번호 end 통장번호,
case when x.예금일자 is not null then x.예금일자 else y.출금일자 end 입출금일자,
isnull(x.예금액, 0) 예금액,
isnull(y.출금액, 0) 출금액
from 예금내역 x full outer join 출금내역 y
on x.통장번호 = y.통장번호
and x.예금일자 = y.출금일자
order by case when x.예금일자 is not null then x.예금일자 else y.출금일자 end
이런식으로 full outer join을 사용하면 되지만 경우에 따라서는 union 을 통해서도 가능하다.
2. Union 사용
select 통장번호, 입출금일자, sum(예금액) 예금액, sum(출금액) 출금액
from (
select 통장번호, 예금일자 as 입출금일자, 예금액, 0 as 출금액 from 예금내역
union all
select 통장번호, 출금일자 as 입출금일자, 0 as 예금액, 출금액 from 출금내역
) x
group by 통장번호, 입출금일자
order by 입출금일자
다양한 해결방법을 알고 경우에 따라서 적절히 사용하면 되겠네요.
/*-----------------------------------------------------------------------*/
2-1-2. 실행계획분리의 union
/*-----------------------------------------------------------------------*/
-- 예제1.
select * from 사원
where 입사일자 like "2000%"
and 부서코드 in (select 부서코드 from 부서 where 부서구분=1)
|--Nested Loops(Left Semi Join, WHERE:([사원].[부서코드]=[부서].[부서코드]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] >= "2000" AND [사원].[입사일자] < "200¼"),
WHERE:(like([사원].[입사일자], "2000%", NULL)) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
select * from 사원2
where 입사일자 like "2000%"
union
select * from 사원2
where 부서코드 in (select 부서코드 from 부서 where 부서구분=1)
|--Sort(DISTINCT ORDER BY:([Union1006] ASC, [Union1007] ASC,
[Union1008] ASC, [Union1009] ASC, [Union1010] ASC))
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원2]))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원2].[idx_입사일자]),
SEEK:([사원2].[입사일자] >= "2000" AND [사원2].[입사일자] < "200¼"),
WHERE:(like([사원2].[입사일자], "2000%", NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원2]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([부서].[부서코드]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원2].[idx_부서코드]),
SEEK:([사원2].[부서코드]=[부서].[부서코드]) ORDERED FORWARD)
예제가 별루 좋지못하지만 이런식으로도 각각의 인덱스를 사용하게끔 할수도있다는것 정도만 알고있자.
-- 예제2.(or를 사용한 안좋은 방법이다.)
사원정보를 보여주는 화면상에서 조건값으로 사원번호와 부서코드를 입력받는 화면이 있다고
가정을 하고 쿼리문을 만들어 보자.(둘중 하나만 입력된다. - 옵션박스나 탭등을 이용한다고 가정)
1. 가장 편한 or를 사용한 방법
--drop proc p1
create proc p1(@사원번호 int, @부서코드 int)
as
select * from 사원
where 사원번호 = @사원번호
or 부서코드 = @부서코드
go
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]),
WHERE:([사원].[사원번호]=[@사원번호] OR [사원].[부서코드]=[@부서코드]))
이런, 셈플 데이터가 워낙에 적다보니 table scan을 하네요
데이터량이 많이 있다면 이렇게 안되고 아마도 사원번호인덱스를 이용하게끔 실행계획이
풀렸을 것이다. 그렇지만 다음번 쿼리시 사원번호를 null로 하고 부서코드를 입력한다면
어떻게 될것인가...?
2. union을 사용하여 실행계획 분리
drop proc p1 --지우고 다시만들자
go
create proc p1(@사원번호 int, @부서코드 int)
as
select * from 사원
where @사원번호 is not null
and 사원번호 = @사원번호
union all
select * from 사원
where @사원번호 is null
and 부서코드 = @부서코드
go
--사원번호가 넘어오면 무조건 pk를 사용하고,
--그렇지 않다면 부서코드 인덱스를 사용하게끔 실행계획을 분리하였다.
--이경우 union 이 아니라 union all 을 사용하였다.
--이런식의 쿼리문을 만들때 주의할것은 둘중하나만 참이여야 한다는 것이다.
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@사원번호]<>NULL)))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[@사원번호]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@사원번호]=NULL)))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[@부서코드]) ORDERED FORWARD)
declare @사원번호 int, @부서코드 int
set @사원번호 = null
set @부서코드 = 1
exec p1 @사원번호, @부서코드
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@사원번호]<>NULL)))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[@사원번호]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@사원번호]=NULL)))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[@부서코드]) ORDERED FORWARD)
두가지 경우다 원하는대로 정확히 실행계획이 분리되어 언제나 원하는 인덱스를 잘 사용한다.
3.동적쿼리 사용
drop proc p1 --지우고 다시만들자
go
create proc p1(@사원번호 int, @부서코드 int)
as
declare @sql varchar(1000)
set @sql = "select * from 사원 where "
set @sql = @sql +
case when @사원번호 is not null
then " 사원번호 = " + convert(varchar(10), @사원번호)
else " 부서코드 = " + convert(varchar(10), @부서코드)
end
--print @sql
exec (@sql)
go
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
SELECT * FROM [사원] WHERE [사원번호]=@1
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=Convert([@1])) ORDERED FORWARD)
declare @사원번호 int, @부서코드 int
set @사원번호 = null
set @부서코드 = 1
exec p1 @사원번호, @부서코드
SELECT * FROM [사원] WHERE [부서코드]=@1
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=Convert([@1])) ORDERED FORWARD)
이런식으로 동적쿼리문을 만들어 실행을 하면 매번 stored procedure를 재컴파일하며
해당하는 인덱스를 사용하여 원하는 결과를 얻을수 있다.
하지만 이런식이라면 stored procedure를 사용하는 효과를 제대로 볼수없다.
어떤 방식이 좋을지는 각자 판단하자.
-- 예제3. (요번에는 like를 사용한 안좋은 방법이다).
사원정보를 보여주는 화면상에서 조건값으로 사원이름과 입사월을 입력받는 화면이 있다고
가정을 하고 쿼리문을 만들어 보자.(둘중 하나만 입력된다. - 옵션박스나 탭등을 이용한다고 가정)
1. 가장 편한 like 방법
--drop proc p2
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
select * from 사원 where 이름 like @이름
or 입사일자 like @입사일자
go
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = "짱%"
set @입사일자 = null
exec p2 @이름, @입사일자
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]),
WHERE:(like([사원].[이름], [@이름], NULL) OR like([사원].[입사일자], [@입사일자], NULL)))
이런, 셈플 데이터가 워낙에 적다보니 table scan을 하네요
데이터량이 많이 있다면 이렇게 안되고 아마도 이름인덱스를 이용하게끔 실행계획이
풀렸을 것이다. 그렇지만 다음번 쿼리시 이름을 null로 하고 입사일자를 입력한다면
어떻게 될것인가...?
2. union을 사용하여 실행계획 분리
drop proc p2 --지우고 다시만들자
go
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
select * from 사원 where 이름 like @이름 and @이름 is not null
union all
select * from 사원 where 입사일자 like @입사일자 and @이름 is null
go
--이름이 넘어오면 무조건 이름인덱스를 사용하고,
--그렇지 않다면 입사일자인덱스를 사용하게끔 실행계획을 분리하였다.
--이경우 union 이 아니라 union all 을 사용하였다.
--이런식의 쿼리문을 만들때 주의할것은 둘중하나만 참이여야 한다는 것이다.
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = "짱%"
set @입사일자 = null
exec p2 @이름, @입사일자
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@이름]<>NULL)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@이름], NULL),
LikeRangeEnd([@이름], NULL),
LikeRangeInfo([@이름], NULL)))
| |--Compute Scalar(DEFINE:([Expr1009]=LikeRangeStart([@이름], NULL),
[Expr1011]=LikeRangeEnd([@이름], NULL),
[Expr1013]=LikeRangeInfo([@이름], NULL)))
| | |--Constant Scan
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_이름]),
SEEK:([사원].[이름] > LikeRangeStart([@이름], NULL)
AND [사원].[이름] < LikeRangeEnd([@이름], NULL)),
WHERE:(like([사원].[이름], [@이름], NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@이름]=NULL)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1026], [Expr1027],
LikeRangeInfo([@입사일자], NULL)))
|--Compute Scalar(DEFINE:([Expr1026]=Convert(LikeRangeStart([@입사일자], NULL)),
[Expr1027]=Convert(LikeRangeEnd([@입사일자], NULL)),
[Expr1020]=LikeRangeInfo([@입사일자], NULL)))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] > [Expr1026] AND [사원].[입사일자] < [Expr1027]),
WHERE:(like([사원].[입사일자], [@입사일자], NULL)) ORDERED FORWARD)
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = null
set @입사일자 = "2000%"
exec p2 @이름, @입사일자
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@이름]<>NULL)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@이름], NULL),
LikeRangeEnd([@이름], NULL),
LikeRangeInfo([@이름], NULL)))
| |--Compute Scalar(DEFINE:([Expr1009]=LikeRangeStart([@이름], NULL),
[Expr1011]=LikeRangeEnd([@이름], NULL),
[Expr1013]=LikeRangeInfo([@이름], NULL)))
| | |--Constant Scan
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_이름]),
SEEK:([사원].[이름] > LikeRangeStart([@이름], NULL)
AND [사원].[이름] < LikeRangeEnd([@이름], NULL)),
WHERE:(like([사원].[이름], [@이름], NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@이름]=NULL)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1026], [Expr1027],
LikeRangeInfo([@입사일자], NULL)))
|--Compute Scalar(DEFINE:([Expr1026]=Convert(LikeRangeStart([@입사일자], NULL)),
[Expr1027]=Convert(LikeRangeEnd([@입사일자], NULL)),
[Expr1020]=LikeRangeInfo([@입사일자], NULL)))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] > [Expr1026] AND [사원].[입사일자] < [Expr1027]),
WHERE:(like([사원].[입사일자], [@입사일자], NULL)) ORDERED FORWARD)
두가지 경우다 원하는대로 정확히 실행계획이 분리되어 언제나 인덱스를 잘 사용한다.
이런식의 union 을 사용한 실행계획 분리는 유용하니 잘 익혀두세요.
3.동적쿼리 사용
drop proc p2 --지우고 다시만들자
go
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
declare @sql varchar(1000)
set @sql = "select * from 사원 where "
set @sql = @sql +
case when @이름 is not null then " 이름 like """ + @이름 + """"
else "
교집합, 차집합, 합집합 - 예전에 초등학생 아님 중학생 시절 지겹게(?) 듣던 단어들이지요
/*==========================================================*/
먼저 샘플을 만들어두고
--drop table #a
--drop table #b
create table #a(id int)
create table #b(id int)
go
insert into #a values(1)
insert into #a values(2)
insert into #a values(3)
insert into #a values(4)
insert into #a values(5)
insert into #a values(6)
insert into #a values(7)
insert into #a values(8)
insert into #b values(2)
insert into #b values(4)
insert into #b values(6)
insert into #b values(8)
go
이제, 아주 간단히 보도록 하지요
/*==========================================================*/
1. 교집합 (intersect)
아쉽지만 이런함수는 sql에 없습니다.
(Analysis Services용으로는 있지만 t-sql함수는 아니지요)
그럼 이런경우는 어떻게 할까요 ?
-> in 이나 exists 를 이용하는겁니다. 또는 조인을 사용하지요.
/*==========================================================*/
예제 : 테이블1 과 테이블2 둘다 들어있는것은 ?
1. in 사용
select * from #a
where id in (select id from #b)
2. exists 사용
select * from #a x
where exists(select * from #b y where y.id=x.id)
3. 조인사용
select x.* from #a x
inner join #b y
on (y.id = x.id)
/*==========================================================*/
2. 차집합 (except, minus)
아쉽지만 이런함수는 sql에 없습니다.
(Analysis Services용으로는 있지만 t-sql함수는 아니지요)
그럼 이런경우는 어떻게 할까요 ?
-> not in 이나 not exists 를 이용하는겁니다. 또는 outer join을 사용하는것입니다.
/*==========================================================*/
예제2 : 테이블1 에는 있는데 테이블 2에는 없는 것은 ?
1. not in 사용
select * from #a
where id not in (select id from #b)
2. not exists 사용
select * from #a x
where not exists(select * from #b y where y.id=x.id)
3. outer join 사용
select x.* from #a x
left outer join #b y
on (x.id = y.id)
where y.id is null
/*==========================================================*/
3. 합집합 (union)
유일하게 지원되는 함수입니다.
/*==========================================================*/
예제 : 테이블1 에 있는것과 테이블 2에 있는것을 모두구할때는 ?
1. union 사용 : 중복제거를 위해 sort를 한다.
select * from #a
union
select * from #b
|--Sort(DISTINCT ORDER BY:([Union1004] ASC)) --> 중복제거를 위해서 sort를 한다.
|--Concatenation
|--Table Scan(OBJECT:([tempdb].[dbo].[#a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#b]))
2. union all 사용 : 중복허용
select * from #a
union all
select * from #b
|--Concatenation --> sort가 없다.
|--Table Scan(OBJECT:([tempdb].[dbo].[#a]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#b))
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
여기까지 간단하게 집합의 개념을 봤고
이제 in 과 union 에 대하여 조금만 더 살펴보도록 하지요.
/*==========================================================*/
1. in : 주어진 값이 하위 쿼리 또는 목록에 있는 임의의 값과 일치하는지 확인합니다.
구문
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
ex) 회원테이블(아이디, 이름, 레벨)
1 홍길동 1
2 짱구 1
3 뿡뿡이 2
--drop table 회원
create table 회원 (아이디 int, 이름 varchar(10), 레벨 int)
create index idx on 회원(레벨)
go
insert into 회원 values(1, "홍길동", 1)
insert into 회원 values(2, "짱구", 1)
insert into 회원 values(3, "뿡뿡이", 2)
go
select * from 회원 where 레벨 in (1, 2)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[회원]))
|--Index Seek(OBJECT:([pubs].[dbo].[회원].[idx]),
SEEK:([회원].[레벨]=1 OR [회원].[레벨]=2) ORDERED FORWARD)
실헹계획을 보면 레벨 in (1, 2) 구문이 레벨=1 OR 레벨=2 와 같이 or연산으로 변경 되었다.
일반적으로 이러한 in 의 경우 or 연산으로 변경되어 실행된다.
그럼, 좀다른 방법으로
declare @lev1 int, @lev2 int
set @lev1 = 1
set @lev2 = 2
select * from 회원 where 레벨 in (@lev1, @lev2)
|--Table Scan(OBJECT:([pubs].[dbo].[회원]),
WHERE:(([회원].[레벨]=[@lev2]) OR [회원].[레벨]=[@lev1]))
예상했던 index seek 방식이 아니라 table scan 방식이다.
sql-server 가 Cost base 방식의 옵티마이져를 사용하다보니 이런결과가 나온다.
즉, 위 경우에 @lev1만을 가지고 쿼리를 한다면 index seek 방식으로 처리된다.
하지만 위 결과를 보면 단지 2개의 변수를 적용하자 table scan쪽으로 돌아선다.
왜 그럴까 ?
일반적으로 sql-server는 상수값이 쿼리에서 사용되면 정확한 통계정보를 사용하여
index를 사용할지 table scan으로 갈지 결정을 하지만 알수없는 변수값이 들어오면
그냥 평균값을 사용한다고 한다. 그래서 이경우 sql-server가 table scan을 선택한것이다.
이런경우에 힌트를 사용해야 정상적인 index 활용을 한다.
물론, 예제가 엉터리 이지만 실무에서도 한번 테스트 해보기 바란다.
여기 예제와 다르지 않을것이다.
하나더, 일반적으로 sql-server는 앞에서 부터 파싱을 하지만 변수를 사용한 in 의 경우는
반대로 뒤에서부터 파싱을 한다. 이것도 알아둘 필요는 있을듯.
그렇다고해서 in안에 나열된순서 또는 역순으로 정렬이되는것은 절대 아니다.
정렬된 순서를 원한다면 반드시 명시적으로 order by 를 써서 정렬순서를 지정해주어야한다.
declare @lev1 int, @lev2 int
set @lev1 = 1
set @lev2 = null
select * from 회원 where 레벨 in (@lev1, @lev2)
이경우는 어떻게 될까 ?
->
declare @lev1 int, @lev2 int
set @lev1 = null
set @lev2 = null
select * from 회원 where 레벨 in (@lev1, @lev2)
또, 이런 경우는 어떻게 될까 ?
각자 해보시길...
/*-----------------------------------------------------------------------*/
1-1. in-subquary
1. in-subquary의 경우 join문으로 해석이 된다.
2. in-subquary가 먼저 읽힐수도, 나중에 읽힐수도 있다
-> 인덱스 유무, 쿼리유형 등에 따라서 달라진다.
3. 고수가 아니라면 in-subquary 실행시 반드시 실행계획을 확인해보시길 권장합니다.
가끔 이놈이 생각한데로 작동을 안하는수가 있으니 조심...
/*-----------------------------------------------------------------------*/
상품테이블(상품코드, 상품명, 분류코드)
분류테이블(분류코드, 카테고리명, 등급)
이런식으로 되어 있을경우 상품테이블에서 카테고리등급이 "A"인것들의 목록을 보고싶다면
select * from 상품
where 분류코드 in (select 분류코드 from 분류 where 등급 = "A")
이렇게 쿼리를 하면 옵티마이저가
select 상품.*
from 상품 inner join 분류
on 상품.분류코드 = 분류.분류코드
and 분류.등급 = "A"
이런식으로 inner join으로 풀어서 처리를 한다.
이런식의 쿼리문은 대부분의 경우 in-subquary가 먼저 실행되는 조인문으로 해석이 되어
실행계획이 나올것으로 기대하지만 그렇지 않은 경우도 있다.
in-subquary의 경우 일반적으로 in-subquary가 먼저 읽혀서 main quary가 나중에 처리되기를
기대하고 쿼리문을 작성하게 된다.
일반적으로 이렇게 풀리지만 경우에 따라서는 그렇지 않고 main quary가 먼저 읽히면서
in-subquary를 나중에 filter조건으로 처리하는경우가 종종 발생한다.
레코드가 많지 않은경우에는 크게 성능상 문제가 되지 않겠지만 그렇지 않은 경우는
심각한 성능상의 저하를 가져온다. 이것을 주의해야 하는것이다.
그래서, 개발자들은 본인이 고수가 아니라면 실행계획을 확인하여야 한다는 것이다.
위 예제의 경우를 가지고 살펴본다면
in-subquary가 먼저 처리된다면
분류 테이블에서 등급이 "A"인 분류코드들만을 먼저 읽어서 그것을 가지고
상품테이블의 분류코드 인덱스를 사용한다면 최적일것이다.
main-quary가 먼저 처리된다면
상품테이블을 무조건 풀로 읽어서 또는 분류코드 인덱스를 풀로 읽으면서 각 row마다
분류 테이블에서 해당분류코드가 등급이 "A"인지 체크하여 걸러주는 형태로 처리된다.
이런경우 일반적으로 쿼리성능이 떨어지게 되는것이다.
그러면 조인문을 사용하지 왜 굳이 in-subquary를 사용했느냐 ?
위 샘플이 그다지 좋은 샘플이 아니라 그런것이고 아래 샘플을 보자.
use tempdb
go
set statistics profile off
set statistics io off
--예제1.
테스트를 위한 샘플 작성
--drop table 코드
create table 코드(상품코드 int not null, 상품명 varchar(40))
go
alter table 코드
add constraint pk_코드 primary key(상품코드)
go
set nocount on
declare @i int
set @i = 1
while (@i <= 50)
begin
insert into 코드 values(@i, convert(varchar(10), @i) )
set @i = @i + 1
end
set nocount off
go
select count(*) from 코드 --50
--drop table 매출마감
create table 매출마감(상품코드 int not null, 매출일자 char(8) not null, 수량 int)
go
alter table 매출마감
add constraint pk_매출마감 primary key (상품코드, 매출일자)
go
set nocount on
declare @i int, @j int
declare @dt datetime
set @i = 1
set @dt = "2000-01-01"
while (@dt < "2004-01-01") --4년치 샘플자료 만들구
begin
set @j = 1
while (@j <= 50)
begin
insert into 매출마감 values(@j, convert(varchar(8), @dt, 112), convert(int, rand() * 1000) )
set @j = @j + 1
end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
go
select count(*) from 매출마감 --73050
set statistics profile on
set statistics io on
--문제 : 2003 년 5월 마감내역 list ?
1. 일반적으로 그냥 아무생각없이 쿼리를 한다면
select * from 매출마감
where 매출일자 between "20030501" and "20030531"
|--Clustered Index Scan(OBJECT:([T1].[dbo].[매출마감].[pk_매출마감]),
WHERE:([매출마감].[매출일자]>=[@1] AND [매출마감].[매출일자]<=[@2]))
"매출마감" 테이블. 스캔 수 1, 논리적 읽기 수 299, 물리적 읽기 수 0, 미리 읽기 수 0.
index seek이 아니라 index scan방식으로 실헹계획이 풀렸다.
인덱스를 활용을 못한다. 첫번째 컬럼을 사용하지 못하기 때문이다.
2. 첫번째 컬럼을 in-subquary를 사용하여 인위적으로 공급함으로써
정상적으로 인덱스를 활용하게끔 한다.
select * from 매출마감
where 상품코드 in (select 상품코드 from 코드)
and 매출일자 between "20030501" and "20030531"
|--Nested Loops(Inner Join, OUTER REFERENCES:([코드].[상품코드]) WITH PREFETCH)
|--Clustered Index Scan(OBJECT:([T1].[dbo].[코드].[pk_코드]))
|--Clustered Index Seek(OBJECT:([T1].[dbo].[매출마감].[pk_매출마감]),
SEEK:([매출마감].[상품코드]=[코드].[상품코드]
AND [매출마감].[매출일자] >= "20030501"
AND [매출마감].[매출일자] <= "20030531") ORDERED FORWARD)
"매출마감" 테이블. 스캔 수 50, 논리적 읽기 수 112, 물리적 읽기 수 0, 미리 읽기 수 0.
"코드" 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.
정상적으로 인덱스를 잘 활용하였고 쿼리성능또한 훨씬 뛰어나다.
그러나, 이것은 테스트를 위하여 인위적으로 그렇게 한것이고 실무에서는 다양한 상황을
검토하여 사용여부를 판단해야한다. 정말 sql은 어렵다.
이예제의 경우도 샘플을 만들때 4년치를 만들지 않고 1년치만 만들어서 테스트를 해보면
1번 경우처럼 그냥 clustered index scan 을 하는것이 훨씬 효율이 더 좋다.
이예제를 보면 처음 2년정도는 그냥 쿼리하여 clustered index scan 을 유도하고
이후 데이터가 더 많이 쌓이게 되면 in-subquary를 활용하여 clustered index seek 을
유도하는 방식을 사용해야 쿼리효율을 일정하게 유지할수 있다.
이런것이 sql의 재미가 아닐까 ?
--예제2. : in-subquary를 사용하여 인덱스를 사용하게끔 유도한것이 오히려 효율을 떨어뜨리는 경우
테스트를 위한 샘플 작성
set statistics profile off
set statistics io off
--drop table t1
create table t1(dt char(8) not null, seq int not null, qty int, c1 char(100))
go
alter table t1
add constraint pk_t1 primary key (dt, seq)
go
set nocount on
declare @dt datetime
declare @i int, @j int
set @dt = "2000-01-01"
while (@dt < "2004-01-01")
begin
set @i = convert(int, rand() * 100)
set @j = 1
while (@j <= @i)
begin
insert into t1 values(convert(varchar(8),@dt,112),@j,
convert(int,rand()*1000),convert(varchar(10),@j))
set @j = @j + 1
end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
select count(*) from t1 --74007
--drop table dumy_dt
create table dumy_dt(dt char(8) not null, dt1 datetime)
go
alter table dumy_dt
add constraint pk_dumy_dt primary key(dt)
go
set nocount on
declare @dt datetime
set @dt = "2000-01-01"
while (@dt < "2004-01-01")
begin
insert into dumy_dt values( convert(varchar(8), @dt, 112), @dt )
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
--문제 : 1월 일별 자료중 seq 가 3 보다 작은 list ?
set statistics profile on
set statistics io on
select * from t1
where dt like "200301%"
and seq < 3
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt] >= "200301" AND [t1].[dt] < "200302"),
WHERE:([t1].[seq]<3 AND like([t1].[dt], "200301%", NULL)) ORDERED FORWARD)
"t1" 테이블. 스캔 수 1, 논리적 읽기 수 25, 물리적 읽기 수 0, 미리 읽기 수 0.
select * from t1
where dt like "200301%"
and seq in (1, 2)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt] >= "200301" AND [t1].[dt] < "200302"),
WHERE:(like([t1].[dt], "200301%", NULL) AND ([t1].[seq]=2 OR [t1].[seq]=1)) ORDERED FORWARD)
"t1" 테이블. 스캔 수 1, 논리적 읽기 수 25, 물리적 읽기 수 0, 미리 읽기 수 0.
select * from t1
where dt in (select dt from dumy_dt where dt like "200301%")
and seq in (1, 2)
|--Nested Loops(Inner Join, OUTER REFERENCES:([dumy_dt].[dt]) WITH PREFETCH)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[dumy_dt].[pk_dumy_dt]),
SEEK:([dumy_dt].[dt] >= "200301" AND [dumy_dt].[dt] < "200302"),
WHERE:(like([dumy_dt].[dt], "200301%", NULL)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([T1].[dbo].[t1].[pk_t1]),
SEEK:([t1].[dt]=[dumy_dt].[dt] AND [t1].[seq]=1 OR
[t1].[dt]=[dumy_dt].[dt] AND [t1].[seq]=2) ORDERED FORWARD)
"t1" 테이블. 스캔 수 62, 논리적 읽기 수 207, 물리적 읽기 수 0, 미리 읽기 수 0.
"dumy_dt" 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.
위 결과를 보면 이경우는 오히려 in-subquary를 사용하여 index scan 을 index seek 로 유도한것이
오히려 쿼리 효율을 떨어뜨리는 경우이다.
즉, 상황에 따라 판단해야 하는것이다.
모든 경우에 효율적인 쿼리란 별루 없다.
/*-----------------------------------------------------------------------*/
마지막으로 정리를 해보자.
/*-----------------------------------------------------------------------*/
--먼저 샘플만들구
set statistics profile off
set statistics io off
set nocount on
go
drop table 급여
drop table 사원
drop table 직급
drop table 부서
go
create table 부서
( 부서코드 int not null primary key
, 부서명 varchar(40)
, 부서구분 int)
go
create index idx_부서구분 on 부서(부서구분)
create index idx_부서명 on 부서(부서명)
go
insert into 부서 values (1, "개발부", 2)
insert into 부서 values (2, "영업부", 3)
insert into 부서 values (3, "총무부", 1)
insert into 부서 values (4, "인터넷사업부", 2)
insert into 부서 values (5, "하드웨어부", 2)
go
create table 직급
( 직급코드 int not null primary key
, 직급명 varchar(40))
go
create index idx_직급명 on 직급(직급명)
go
insert into 직급 values(1, "사원")
insert into 직급 values(2, "대리")
insert into 직급 values(3, "과장")
insert into 직급 values(4, "차장")
insert into 직급 values(5, "부장")
insert into 직급 values(6, "이사")
go
create table 사원
( 사원번호 int not null
, 이름 varchar(10)
, 입사일자 char(8)
, 직급코드 int
, 부서코드 int)
go
--인덱스 테스트를 목적으로 nonclustered 로 잡았음.
alter table 사원
add constraint pk_사원 primary key nonclustered(사원번호)
create index idx_부서코드 on 사원(부서코드)
create index idx_직급코드 on 사원(직급코드)
create index idx_이름 on 사원(이름)
create index idx_입사일자 on 사원(입사일자)
alter table 사원
add constraint fk_부서 foreign key(부서코드)
references 부서(부서코드)
alter table 사원
add constraint fk_직급 foreign key(직급코드)
references 직급(직급코드)
go
insert into 사원 values (1, "짱구", "19990101", 1, 1)
insert into 사원 values (2, "맹구", "19990501", 1, 1)
insert into 사원 values (3, "뿡뿡이", "20000101", 1, 1)
insert into 사원 values (4, "개똥이", "20000101", 1, 3)
insert into 사원 values (5, "타잔", "20000301", 2, 1)
insert into 사원 values (6, "치타", "20000402", 2, 2)
insert into 사원 values (7, "난장이", "20000405", 2, 1)
insert into 사원 values (8, "백설이", "20001011", 3, 1)
insert into 사원 values (9, "응삼이", "20010304", 3, 4)
insert into 사원 values (10, "푸", "20010404", 4, 5)
insert into 사원 values (11, "티커", "20010404", 4, 1)
insert into 사원 values (12, "뽀빠이", "20021010", 5, 1)
insert into 사원 values (13, "올리버", "20030101", 1, 5)
go
create table 급여
( 사원번호 int not null
, 급여년월 char(6) not null
, 급여 int)
go
alter table 급여
add constraint pk_급여 primary key(사원번호, 급여년월)
alter table 급여
add constraint fk_급여 foreign key(사원번호)
references 사원(사원번호)
go
insert into 급여 values(1, "200212", 100)
insert into 급여 values(2, "200212", 150)
insert into 급여 values(3, "200212", 200)
insert into 급여 values(1, "200301", 100)
insert into 급여 values(2, "200301", 200)
insert into 급여 values(3, "200301", 400)
insert into 급여 values(4, "200301", 500)
insert into 급여 values(5, "200301", 100)
insert into 급여 values(6, "200301", 300)
insert into 급여 values(7, "200301", 100)
insert into 급여 values(8, "200301", 400)
insert into 급여 values(9, "200301", 100)
insert into 급여 values(10, "200301", 200)
insert into 급여 values(11, "200301", 100)
insert into 급여 values(12, "200301", 200)
insert into 급여 values(13, "200301", 100)
insert into 급여 values(1, "200302", 100)
insert into 급여 values(2, "200302", 600)
insert into 급여 values(3, "200302", 100)
insert into 급여 values(4, "200302", 500)
insert into 급여 values(5, "200302", 100)
insert into 급여 values(6, "200302", 400)
insert into 급여 values(7, "200302", 100)
insert into 급여 values(8, "200302", 200)
insert into 급여 values(9, "200302", 100)
insert into 급여 values(10, "200302", 300)
insert into 급여 values(11, "200302", 110)
insert into 급여 values(12, "200302", 100)
insert into 급여 values(13, "200302", 200)
insert into 급여 values(1, "200303", 200)
insert into 급여 values(2, "200303", 600)
insert into 급여 values(3, "200303", 130)
insert into 급여 values(4, "200303", 400)
insert into 급여 values(5, "200303", 100)
insert into 급여 values(6, "200303", 300)
insert into 급여 values(7, "200303", 100)
insert into 급여 values(8, "200303", 120)
insert into 급여 values(9, "200303", 100)
insert into 급여 values(10, "200303", 300)
insert into 급여 values(11, "200303", 150)
insert into 급여 values(12, "200303", 100)
insert into 급여 values(13, "200303", 100)
go
set nocount off
go
set statistics profile on
set statistics io on
/*-----------------------------------------------------------------------*/
1-1-1 in-subquary 가 먼저 읽히는 경우(일반적으로 원하는 형태)
/*-----------------------------------------------------------------------*/
select * from 사원
where 부서코드 in (select 부서코드 from 부서 where 부서구분 = 1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([부서].[부서코드]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[부서].[부서코드]) ORDERED FORWARD)
/*-----------------------------------------------------------------------*/
1-1-2. in-subquary 가 나중에 읽히는 경우
/*-----------------------------------------------------------------------*/
--create index idx_부서코드 on 사원(부서코드)
drop index 사원.idx_부서코드 --테스트를 위해서 강제적으로 인덱스를 지우고
go
select * from 사원
where 부서코드 in (select 부서코드 from 부서 where 부서구분 = 1)
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([사원].[부서코드]))
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1 AND [부서].[부서코드]=[사원].[부서코드]) ORDERED FORWARD)
select * from 사원
where 사원번호 in (select 사원번호 from 급여 where 급여년월 like "2002%")
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([사원].[사원번호]))
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[급여].[pk_급여]),
SEEK:([급여].[사원번호]=[사원].[사원번호]
AND [급여].[급여년월] >= "2002" AND [급여].[급여년월] < "2003"),
WHERE:(like([급여].[급여년월], "2002%", NULL)) ORDERED FORWARD)
이번에는 in-subquary가 나중에 읽혔네요.
즉, 원하는대로 작동을 안한것이지요. 이런경우는 엄청난 비효율을 가져옵니다.
인덱스를 하나만들구
create index idx_급연년월 on 급여(급여년월)
go
select * from 사원
where 사원번호 in (select 사원번호 from 급여 where 급여년월 like "2002%")
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([급여].[사원번호]))
|--Sort(DISTINCT ORDER BY:([급여].[사원번호] ASC))
| |--Index Seek(OBJECT:([tempdb].[dbo].[급여].[idx_급연년월]),
SEEK:([급여].[급여년월] >= "2002" AND [급여].[급여년월] < "2003"),
WHERE:(like([급여].[급여년월], "2002%", NULL)) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[급여].[사원번호]) ORDERED FORWARD)
이제 정상적으로 in-subquary가 먼저 읽혔지요.
인덱스라는 것이 이렇게 엄청난 차이를 가져오는 것입니다.
그렇다고, 이런식으로 인덱스를 무한정 만들수는 없는것이지요.
그래서, 인덱스를 만드는것을 전략이라고들 합니다.
그만큼 많은 경우의 수를 따져보고 쿼리유형을 면밀히 파악한다음
머리카락이 빠질정도로 고민해보고 만들어야하는것이지요.
/*==========================================================*/
2. union : 두 개 이상의 쿼리 결과를 모든 쿼리에 속하는 모든 행의 합으로 구성되는
하나의 결과 집합으로 결합합니다.
이것은 두 테이블의 열을 결합하는 조인을 사용하는 것과는 다릅니다.
UNION 연산자를 사용하여 두 개의 쿼리 결과 집합을 결합할 때는 다음 두 가지 기본 규칙을 따라야 합니다.
열의 개수와 순서가 모든 쿼리에서 동일해야 합니다.
데이터 형식이 호환되어야 합니다.
구문
{ < query specification > | ( < query expression > ) }
UNION [ ALL ]
< query specification | ( < query expression > )
[ UNION [ ALL ] < query specification | ( < query expression > )
[ ...n ] ]
/*-----------------------------------------------------------------------*/
2-1. 사용형태
1. 합집합(위에서 봤으므로 생략) - 온라인설명서에도 설명이 잘되어 있으니 참고
2. Full Outer Join의 대안
3. 실행계획의 분리
/*-----------------------------------------------------------------------*/
2-1-1. Full Outer Join의 대안
/*-----------------------------------------------------------------------*/
--drop table 예금내역
create table 예금내역(통장번호 int, 일련번호 int, 예금일자 char(10), 예금액 int)
insert into 예금내역 values (1, 1, "2003-06-01", 100)
insert into 예금내역 values (1, 2, "2003-06-03", 100)
insert into 예금내역 values (1, 3, "2003-06-05", 100)
insert into 예금내역 values (1, 4, "2003-06-07", 100)
go
--drop table 출금내역
create table 출금내역(통장번호 int, 일련번호 int, 출금일자 char(10), 출금액 int)
insert into 출금내역 values (1, 1, "2003-06-01", 50)
insert into 출금내역 values (1, 2, "2003-06-02", 50)
insert into 출금내역 values (1, 3, "2003-06-05", 50)
문제 : 위와 같이 테이블이 설계되어 있다고 하자. 물론 입금내역과 출금내역은 업무적으로
하나의 테이블로 설계를 했어야 했지만, 이미 저렇게 설계된대로 몇년을 사용해왔고, 설계변경이
힘이 들다는 가정하에서 일자별 입출금내역을 보자
1. Full Outer Join사용
select case when x.통장번호 is not null then x.통장번호 else y.통장번호 end 통장번호,
case when x.예금일자 is not null then x.예금일자 else y.출금일자 end 입출금일자,
isnull(x.예금액, 0) 예금액,
isnull(y.출금액, 0) 출금액
from 예금내역 x full outer join 출금내역 y
on x.통장번호 = y.통장번호
and x.예금일자 = y.출금일자
order by case when x.예금일자 is not null then x.예금일자 else y.출금일자 end
이런식으로 full outer join을 사용하면 되지만 경우에 따라서는 union 을 통해서도 가능하다.
2. Union 사용
select 통장번호, 입출금일자, sum(예금액) 예금액, sum(출금액) 출금액
from (
select 통장번호, 예금일자 as 입출금일자, 예금액, 0 as 출금액 from 예금내역
union all
select 통장번호, 출금일자 as 입출금일자, 0 as 예금액, 출금액 from 출금내역
) x
group by 통장번호, 입출금일자
order by 입출금일자
다양한 해결방법을 알고 경우에 따라서 적절히 사용하면 되겠네요.
/*-----------------------------------------------------------------------*/
2-1-2. 실행계획분리의 union
/*-----------------------------------------------------------------------*/
-- 예제1.
select * from 사원
where 입사일자 like "2000%"
and 부서코드 in (select 부서코드 from 부서 where 부서구분=1)
|--Nested Loops(Left Semi Join, WHERE:([사원].[부서코드]=[부서].[부서코드]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] >= "2000" AND [사원].[입사일자] < "200¼"),
WHERE:(like([사원].[입사일자], "2000%", NULL)) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
select * from 사원2
where 입사일자 like "2000%"
union
select * from 사원2
where 부서코드 in (select 부서코드 from 부서 where 부서구분=1)
|--Sort(DISTINCT ORDER BY:([Union1006] ASC, [Union1007] ASC,
[Union1008] ASC, [Union1009] ASC, [Union1010] ASC))
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원2]))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원2].[idx_입사일자]),
SEEK:([사원2].[입사일자] >= "2000" AND [사원2].[입사일자] < "200¼"),
WHERE:(like([사원2].[입사일자], "2000%", NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원2]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([부서].[부서코드]))
|--Index Seek(OBJECT:([tempdb].[dbo].[부서].[idx_부서구분]),
SEEK:([부서].[부서구분]=1) ORDERED FORWARD)
|--Index Seek(OBJECT:([tempdb].[dbo].[사원2].[idx_부서코드]),
SEEK:([사원2].[부서코드]=[부서].[부서코드]) ORDERED FORWARD)
예제가 별루 좋지못하지만 이런식으로도 각각의 인덱스를 사용하게끔 할수도있다는것 정도만 알고있자.
-- 예제2.(or를 사용한 안좋은 방법이다.)
사원정보를 보여주는 화면상에서 조건값으로 사원번호와 부서코드를 입력받는 화면이 있다고
가정을 하고 쿼리문을 만들어 보자.(둘중 하나만 입력된다. - 옵션박스나 탭등을 이용한다고 가정)
1. 가장 편한 or를 사용한 방법
--drop proc p1
create proc p1(@사원번호 int, @부서코드 int)
as
select * from 사원
where 사원번호 = @사원번호
or 부서코드 = @부서코드
go
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]),
WHERE:([사원].[사원번호]=[@사원번호] OR [사원].[부서코드]=[@부서코드]))
이런, 셈플 데이터가 워낙에 적다보니 table scan을 하네요
데이터량이 많이 있다면 이렇게 안되고 아마도 사원번호인덱스를 이용하게끔 실행계획이
풀렸을 것이다. 그렇지만 다음번 쿼리시 사원번호를 null로 하고 부서코드를 입력한다면
어떻게 될것인가...?
2. union을 사용하여 실행계획 분리
drop proc p1 --지우고 다시만들자
go
create proc p1(@사원번호 int, @부서코드 int)
as
select * from 사원
where @사원번호 is not null
and 사원번호 = @사원번호
union all
select * from 사원
where @사원번호 is null
and 부서코드 = @부서코드
go
--사원번호가 넘어오면 무조건 pk를 사용하고,
--그렇지 않다면 부서코드 인덱스를 사용하게끔 실행계획을 분리하였다.
--이경우 union 이 아니라 union all 을 사용하였다.
--이런식의 쿼리문을 만들때 주의할것은 둘중하나만 참이여야 한다는 것이다.
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@사원번호]<>NULL)))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[@사원번호]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@사원번호]=NULL)))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[@부서코드]) ORDERED FORWARD)
declare @사원번호 int, @부서코드 int
set @사원번호 = null
set @부서코드 = 1
exec p1 @사원번호, @부서코드
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@사원번호]<>NULL)))
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=[@사원번호]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@사원번호]=NULL)))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=[@부서코드]) ORDERED FORWARD)
두가지 경우다 원하는대로 정확히 실행계획이 분리되어 언제나 원하는 인덱스를 잘 사용한다.
3.동적쿼리 사용
drop proc p1 --지우고 다시만들자
go
create proc p1(@사원번호 int, @부서코드 int)
as
declare @sql varchar(1000)
set @sql = "select * from 사원 where "
set @sql = @sql +
case when @사원번호 is not null
then " 사원번호 = " + convert(varchar(10), @사원번호)
else " 부서코드 = " + convert(varchar(10), @부서코드)
end
--print @sql
exec (@sql)
go
declare @사원번호 int, @부서코드 int
set @사원번호 = 3
set @부서코드 = null
exec p1 @사원번호, @부서코드
SELECT * FROM [사원] WHERE [사원번호]=@1
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[pk_사원]),
SEEK:([사원].[사원번호]=Convert([@1])) ORDERED FORWARD)
declare @사원번호 int, @부서코드 int
set @사원번호 = null
set @부서코드 = 1
exec p1 @사원번호, @부서코드
SELECT * FROM [사원] WHERE [부서코드]=@1
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_부서코드]),
SEEK:([사원].[부서코드]=Convert([@1])) ORDERED FORWARD)
이런식으로 동적쿼리문을 만들어 실행을 하면 매번 stored procedure를 재컴파일하며
해당하는 인덱스를 사용하여 원하는 결과를 얻을수 있다.
하지만 이런식이라면 stored procedure를 사용하는 효과를 제대로 볼수없다.
어떤 방식이 좋을지는 각자 판단하자.
-- 예제3. (요번에는 like를 사용한 안좋은 방법이다).
사원정보를 보여주는 화면상에서 조건값으로 사원이름과 입사월을 입력받는 화면이 있다고
가정을 하고 쿼리문을 만들어 보자.(둘중 하나만 입력된다. - 옵션박스나 탭등을 이용한다고 가정)
1. 가장 편한 like 방법
--drop proc p2
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
select * from 사원 where 이름 like @이름
or 입사일자 like @입사일자
go
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = "짱%"
set @입사일자 = null
exec p2 @이름, @입사일자
|--Table Scan(OBJECT:([tempdb].[dbo].[사원]),
WHERE:(like([사원].[이름], [@이름], NULL) OR like([사원].[입사일자], [@입사일자], NULL)))
이런, 셈플 데이터가 워낙에 적다보니 table scan을 하네요
데이터량이 많이 있다면 이렇게 안되고 아마도 이름인덱스를 이용하게끔 실행계획이
풀렸을 것이다. 그렇지만 다음번 쿼리시 이름을 null로 하고 입사일자를 입력한다면
어떻게 될것인가...?
2. union을 사용하여 실행계획 분리
drop proc p2 --지우고 다시만들자
go
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
select * from 사원 where 이름 like @이름 and @이름 is not null
union all
select * from 사원 where 입사일자 like @입사일자 and @이름 is null
go
--이름이 넘어오면 무조건 이름인덱스를 사용하고,
--그렇지 않다면 입사일자인덱스를 사용하게끔 실행계획을 분리하였다.
--이경우 union 이 아니라 union all 을 사용하였다.
--이런식의 쿼리문을 만들때 주의할것은 둘중하나만 참이여야 한다는 것이다.
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = "짱%"
set @입사일자 = null
exec p2 @이름, @입사일자
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@이름]<>NULL)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@이름], NULL),
LikeRangeEnd([@이름], NULL),
LikeRangeInfo([@이름], NULL)))
| |--Compute Scalar(DEFINE:([Expr1009]=LikeRangeStart([@이름], NULL),
[Expr1011]=LikeRangeEnd([@이름], NULL),
[Expr1013]=LikeRangeInfo([@이름], NULL)))
| | |--Constant Scan
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_이름]),
SEEK:([사원].[이름] > LikeRangeStart([@이름], NULL)
AND [사원].[이름] < LikeRangeEnd([@이름], NULL)),
WHERE:(like([사원].[이름], [@이름], NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@이름]=NULL)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1026], [Expr1027],
LikeRangeInfo([@입사일자], NULL)))
|--Compute Scalar(DEFINE:([Expr1026]=Convert(LikeRangeStart([@입사일자], NULL)),
[Expr1027]=Convert(LikeRangeEnd([@입사일자], NULL)),
[Expr1020]=LikeRangeInfo([@입사일자], NULL)))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] > [Expr1026] AND [사원].[입사일자] < [Expr1027]),
WHERE:(like([사원].[입사일자], [@입사일자], NULL)) ORDERED FORWARD)
declare @이름 varchar(10), @입사일자 varchar(8)
set @이름 = null
set @입사일자 = "2000%"
exec p2 @이름, @입사일자
|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[사원]))
| |--Filter(WHERE:(STARTUP EXPR([@이름]<>NULL)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:(LikeRangeStart([@이름], NULL),
LikeRangeEnd([@이름], NULL),
LikeRangeInfo([@이름], NULL)))
| |--Compute Scalar(DEFINE:([Expr1009]=LikeRangeStart([@이름], NULL),
[Expr1011]=LikeRangeEnd([@이름], NULL),
[Expr1013]=LikeRangeInfo([@이름], NULL)))
| | |--Constant Scan
| |--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_이름]),
SEEK:([사원].[이름] > LikeRangeStart([@이름], NULL)
AND [사원].[이름] < LikeRangeEnd([@이름], NULL)),
WHERE:(like([사원].[이름], [@이름], NULL)) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([tempdb].[dbo].[사원]))
|--Filter(WHERE:(STARTUP EXPR([@이름]=NULL)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1026], [Expr1027],
LikeRangeInfo([@입사일자], NULL)))
|--Compute Scalar(DEFINE:([Expr1026]=Convert(LikeRangeStart([@입사일자], NULL)),
[Expr1027]=Convert(LikeRangeEnd([@입사일자], NULL)),
[Expr1020]=LikeRangeInfo([@입사일자], NULL)))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[사원].[idx_입사일자]),
SEEK:([사원].[입사일자] > [Expr1026] AND [사원].[입사일자] < [Expr1027]),
WHERE:(like([사원].[입사일자], [@입사일자], NULL)) ORDERED FORWARD)
두가지 경우다 원하는대로 정확히 실행계획이 분리되어 언제나 인덱스를 잘 사용한다.
이런식의 union 을 사용한 실행계획 분리는 유용하니 잘 익혀두세요.
3.동적쿼리 사용
drop proc p2 --지우고 다시만들자
go
create proc p2(@이름 varchar(10), @입사일자 varchar(8))
as
declare @sql varchar(1000)
set @sql = "select * from 사원 where "
set @sql = @sql +
case when @이름 is not null then " 이름 like """ + @이름 + """"
else "
|
|
||||
|
이메일 문의 : cak0280@nate.com Copyright 2000 By ENTERSOFT.KR All Rights Reserved. |