1 상황

2 결합인덱스의 컬럼 순서를 바꿔보자

3 만약 관계를 걸었다면?

4 결론

SQL Server에 다음과 같은 테이블 있다.

테이블명	행개수	페이지수
Code	38	2
Temp	244909	1291

테이블명	인덱스
Code	Seq
Temp	RegDate, Seq

* 모두 Clustered Index
* Temp 테이블과 Code 테이블은 명시적으로 참조무결성을 걸지 않은 상태(관계를 끊어놓은 상태)

--1
select * from temp
where Seq = 36
and RegDate between '2007-11-01' and '2007-11-30'
--결과 로우 수: 130
--Temp 테이블의 논리적 읽기 수 : 40

--1
select * from temp
where RegDate between '2007-11-01' and '2007-11-30'
--결과 로우 수: 7128
--Temp 테이블의 논리적 읽기 수 : 42

--인덱스 삭제 후 결합인덱스 생성
drop index temp.cix
create clustered index cix on temp(Seq, RegDate)

--1
select * from temp
where Seq = 36
and RegDate between '2007-11-01' and '2007-11-30'
--결과 로우 수: 130
--Temp 테이블의 논리적 읽기 수 : 4

--2
select * from temp
where RegDate between '2007-11-01' and '2007-11-30'
--결과 로우 수: 7128
--Temp 테이블의 논리적 읽기 수 : 1292 -> 풀스캔 하였다!

--3
select * from temp
where Seq in(select Seq from code)
and RegDate between '2007-11-01' and '2007-11-30'
--테이블 'temp'. 검색 수 38, 논리적 읽기 수 199
--테이블 'code'. 검색 수 1, 논리적 읽기 수 2
--결과 로우 수: 7128
--Temp 테이블의 논리적 읽기 수 : 199 -> 논리적 읽기 수가 많이 줄었다. 

결합인덱스의 첫 번째 컬럼이 쓰이지 않아서 생긴 풀스캔을 하였다. 결합인덱스의 첫 번째컬럼을 사용하게 끔 해서 풀스캔을 막았다. (이는 오라클9i 이상에서 지원하는 Index Skip Scan(결합 인덱스의 사용 참고)이라는 것을 흉내내 본 것이다. 어디서 이름이 잘도 가져가 붙이는지.. 떱..) 그러나 RegDate + Seq로 결합인덱스가 구성되었을 경우보다 약 5배의 I/O가 더 발생했다. 그래도 풀스캔보다는 훨씬 낫다!

--관계를 명시적으로 걸어보자.
ALTER TABLE temp ADD CONSTRAINT FK_1 FOREIGN KEY(Seq) REFERENCES code


select * from temp
where Seq in(select Seq from code)
and RegDate between '2007-11-01' and '2007-11-30'
--결과 로우 수: 7128
--Temp 테이블의 논리적 읽기 수 : 1292 -> 풀스캔 하였다! 
--아.. 띨띨하다.. 이래서 사람의 개입이 필요하다. 논리적으로는 맞는데 원하는 답은 아니다.

관계를 걸었을 때와 걸지 않았을 때 옵티마이저의 동작이 틀려진 이유가 뭘까? 당연히 관계 때문이다. 그렇다! 옵티마이저가 실행계획을 만드는데 관계도 필요한 정보인 것이다. 실행계획을 보면 아예 code 테이블을 접근조차 하지 않았다. 왜? 관계가 걸려있는데 code 테이블에 아무런 조건도 주지 않았으므로 논리적으로 읽을 필요가 없다는 것을 옵티마이저는 알고 있기 때문이다. 그러면 code 테이블을 읽도록 SQL을 변경해 보자. 어떻게? 의도적인 컬럼의 변형으로..

select * from temp
where Seq in(select Seq + 0 from code) --요기 고쳤어용
and RegDate between '2007-11-01' and '2007-11-30'
--테이블 'temp'. 검색 수 38, 논리적 읽기 수 199
--테이블 'code'. 검색 수 1, 논리적 읽기 수 2
--결과 로우 수: 7128
--Temp 테이블의 논리적 읽기 수 : 199

관계를 걸지 않았을 경우와 같은 동작을 한다. 왜냐하면 Seq + 0 로 의도적으로 컬럼을 변형시켰다. DBMS는 인덱스를 사용하지 못하는 테이블부터 읽으려고 하기 때문에 code 테이블부터 읽게 된다. (자세한 사항은 Loop Join을 참고하라.) 검색 수가 38이라는 것은 원하는 결과집합 7128건을 38개의 페이지를 콕콕 찍어서 모두 가져올 수 있었다는 것이다. 즉, Code테이블을 먼저 읽고 Temp테이블에서 Seq + RegDate로 생성된 인덱스를 이용하여 38번 Loop 를 돌았다(Nested Loop Join)는 뜻이다.

명시적인 관계(참조 무결성)는 옵티마이저의 실행계획에 참여한다. 위의 문제는 인덱스 설계가 잘못된 경우이거나, 변화된 환경에 대한 유지보수를 하지 않았기 때문에 나타난 문제다. 옵티마이저는 논리적으로 해석해서 Code테이블을 읽을 필요가 없다는 것을 알고 있었다. 결국은 옵티마이저가 실행계획을 만드는데 관계도 영향을 끼쳤다기 보다는 논리적으로 해석했을 때에 결과에 필요없는 부분을 제거한 것이라고 보는 것이 올바른 판단일 것이다. 그렇다고 관계가 성능에 악영향을 끼친다고 보면 안 된다. 왜냐하면 관계를 맺어주는 것이 무결성을 보장하기 위한 가장 비용이 적게드는 방법이기 때문이다. 만약 관계를 생략하면 어디선가 무결성을 보장하는 코드가 들어가야 한다. 이 코드는 데이터베이스와 멀어지면 멀어질수록 비용이 더 많이 들어간다. 결국 전체적인 시스템의 비용은 더 많이 필요로하게 되어 있다. 관계를 그냥 생략하는 것은 성능에 대한 논의할 가치조차 없다.