1 기본적인 사항

2 코드 컬럼으로 자리수별로 의미를 가질 때

3 날짜형 컬럼의 경우

4 LIKE 사용과 데이터형

5 ISNULL()의 사용

6 SQL Server 2000 데이터 형식 선행 규칙

인덱스는 단일 컬럼 또는 복수 컬럼에 대해 생성되는 검색을 위한 물리적인 객체다. 인덱스는 해당 컬럼에 대해 생성된 것이다. 그래서 비교가 되는 컬럼에 계산을 한다든지 함수를 사용한다든지 하는 컬럼을 변형한 SQL을 작성하면 해당 인덱스를 사용하지 못한다. 즉, 계산되거나 함수의 결과가 인덱싱된 것이 아니라 순수한 컬럼에 대해서 인덱싱된 것이다. 대부분의 문제는 다음과 같은 경우에 발생한다.

• 코드 컬럼으로 자리수별로 의미를 가질 때
  
• 날짜형 컬럼의 경우
  
• LIKE 사용의 경우
  
• ISNULL()의 사용
  
  

코드가 주민번호와 같이 의미 있는 경우 Substring()함수를 사용하거나 Left(), Right()함수를 사용한다. 아래의 예제를 보라.

SELECT * FROM STUDENT
WHERE SUBSTRING(SNUM, 3, 3) = ‘550’

SNUM은 학번이다. 학번을 나타내는데 총 7자리 중 3 ~ 5 자리가 학과번호를 나타내는 경우이다. 만약 SUBSTRING(SNUM, 3, 3)로 계속 학과를 나타낸다면 현실적으로는 맞겠으나 데이터베이스 세계에서는 많은 문제의 소지가 있다. 인덱스는 SNUM에 대해서 생성된 것이지 SUBSTRING(SNUM, 3, 3)에 대해서 생성된 것이 아니다. Oracle의 경우는 함수 기반의 인덱스가 있어서 SUBSTRING(SNUM, 3, 3)에 대해서도 인덱스를 생성할 수 있으나 다른 대부분의 DBMS는 Oracle과 같은 지원을 하지 않는다. 이런 자리수 별로 의미가 있는 코드 컬럼에 대한 내용은 Packed Data Type를 참고하라.

만약 SUBSTRING(SNUM, 3, 3)이 아니라 SUBSTRING(SNUM, 1, 3)와 같은 경우는 LIKE로 바꾸면 된다.

--1
SELECT * FROM STUDENT
WHERE SUBSTRING(SNUM, 1, 3) = '550'

--2 이렇게 바꾸면 인덱스를 사용할 수 있다.
SELECT * FROM STUDENT
WHERE SNUM LIKE '550%'

그러나 이렇게 바꾸어서 인덱스를 제대로 사용할 때가 있고, 그렇지 못할 경우도 있을 것이다. 그 이유는 데이터형에 문제가 있거나 SNUM LIKE '550%' 조건의 데이터 분포도가 넓기 때문이다. 만약 SNUM이 숫자형 데이터 타입이라면 아래의 쿼리는 내부적으로 데이터 타입의 변형을 일으킨다. 그러므로 인덱스를 사용하지 못한다.

SELECT * FROM STUDENT
WHERE SNUM LIKE '550%'

--내부적인 변형이 일어난다.
SELECT * FROM STUDENT
WHERE CAST(SNUM, VARCHAR) LIKE '550%'

코드성 컬럼의 경우는 각각의 자리가 의미를 가질 때 문제가 될 경우가 많이 있다. 속성은 하나의 의미만 가져야 하는 것이 대부분의 경우 유리하다. 속성명 자체가 순서등의 의미를 가지는 것은 대부분 SQL을 복잡하게 만든다. 이것은 설계의 잘못이며, 컬럼의 변형으로 인덱스를 사용하지 못하는 것을 방지하기 위해 상대적으로 고려되어야 한다. 그러나 많은 부분이 불리한 점이 많이 있다.

날짜형 컬럼의 경우 은근히 많은 함수 연산을 한다. 예를 들어 특정월의 데이터를 조회할 경우 Left(), Substring()의 함수를 많이 사용한다. 아래와 같다.

SELECT * FROM TabA
WHERE LEFT(CONVERT(char(8), DT, 112), 8) = '200807'

--또는
SELECT * FROM TabA
WHERE CONVERT(char(8), DT, 112) LIKE '200807%'

날짜형 컬럼의 경우는 년, 월, 일, 시간 별로 계산을 할 경우가 많이 있다. 그러므로 함수를 씌우는 경우가 많다. 그러므로 인덱스를 사용하지 못하는 경우가 생기며, 특정 날짜가 의미가 있다면 년, 월, 일과 같이 복합속성인 경우 원자화를 시켜 설계하여야 한다. 설계상의 문제로 인해 튜닝을 해야 할 경우는 개발단계에 따라 다르지만 유지보수 단계라면 비용이 많이 든다.

WHERE SNUM = ‘12345’ 라고 조건을 제시하였다는 것은 SNUM이 문자형이라는 것을 암시한다. 그러나 이것은 확인을 해봐야 하는 사항이다. SNUM이 문자형이건 숫자형이건 인덱스를 사용한다. 왜냐하면 문자형이면 아무런 내부적인 변형이 없이 사용되기 때문이다. 그러나 숫자형이라면 조건의 오른쪽이 변화한다. 아래의 예를 보면 확실히 알 수 있다. ORDERID는 숫자형 컬럼이다. 물론 인덱스도 걸려 있다. MSSQL SERVER가 어떻게 처리하는지 살펴보자.

SELECT * FROM [ORDER DETAILS]
WHERE ORDERID = '10248'

인수를 보면 '@1'은 첫 번째 변수이며, 이 매개변수에 대해서 'CONVERT()' 함수를 사용했다는 것을 알 수 있다. 즉, 숫자형으로 내부적인 변환을 한 것이다. 그러나 ORDERID는 변화되지 않았으므로 인덱스가 잘 사용된다. 그러나 아래와 같이 LIKE를 사용하면 어떻게 될까?

SELECT * FROM [ORDER DETAILS]
WHERE ORDERID LIKE '10248%'

ORDERID가 CONVERT 된 것을 확인 할 수 있다. 그 이유는 LIKE 연산자 자체가 문자열에 대한 패턴매칭 연산자이기 때문이다. 그러므로 ORDERID가 내부적인 변형을 일으킨 것이다. 이것 역시 설계상의 문제이다.

요즘은 많이 없지만 WHERE ISNULL(ColA) = 'ABC'와 같은 형태의 SQL문이 예전에는 많이 존재했었다. 설계문제다. 역시 함수를 사용하였으므로 ColA에 생성된 인덱스는 사용하지 못한다. MS-SQL Server는 WHERE ColA IS NULL에 대해 인덱스를 제대로 사용할 수 있으며, Oracle Server는 저장 구조 자체(Sparse Column)가 인덱스를 사용할 수 없게 되어 있어서 WHERE ColA IS NULL은 인덱스를 사용할 수 없다.

비교시 내부적으로 변형이 일어나는 규칙이 있다. DBMS마다 비슷하며 SQL Server 2000은 다음과 같은 우선순위를 가진다. 이런 규칙으로 varchar형과 int형이 비교되면 varchar형이 내부적인 형변환을 일으킨다. SQL Server 도움말에서 '데이터 형식 선행 규칙'으로 검색하면 된다. SQL Server 2000 이후 버전에서는 데이터 형식이 추가 되었으므로 도움말을 통하여 확인하는 것도 정신건강에 좋다.

1. sql_variant(선행 규칙이 가장 높음)
   
2. datetime
   
3. smalldatetime
   
4. float
   
5. real
   
6. decimal
   
7. money
   
8. smallmoney
   
9. bigint
   
10. int
    
11. smallint
    
12. tinyint
    
13. bit
    
14. ntext
    
15. text
    
16. image
    
17. timestamp
    
18. uniqueidentifier
    
19. nvarchar
    
20. nchar
    
21. varchar
    
22. char
    
23. varbinary
    
24. binary(선행 규칙이 가장 낮음)