SQL Server 2000에서 인덱스의 null 값 처리(2/2)

SQL Server 2000에서 인덱스의 null 값 처리

오라클에서는 인덱스를 구성하고 있는 모든 컬럼의 값이 null인 로우는 인덱스에 저장이 되지 않지만, SQL Server 2000에서는 모든 컬럼의 값이 null인 로우도 인덱스에 저장이 된다. 즉, null도 하나의 값으로 인식하고 인덱스에서 관리하고 있는 것이다.

SQL Server 2000에서 null을 바라보는 관점은 오라클과는 매우 다르다. 오라클에서 null과의 비교는 is null 혹은 is not null을 통해서만 가능하지만, SQL Server 2000에서는 ansi_nulls 옵션을 적용하면 equal 연산자로도 비교가 가능하다.

set ansi_nulls off 를 실행하면 where col1 = null 과 같은 형태의 비교가 가능해진다. 이러한 방식이 가능하기는 하지만 ANSI 표준이 아니므로, 결코 권장되는 사항이 아님을 명심해야 한다.

물론, set ansi_nulls on 을 다시 실행하면 오라클과 마찬가지로 is null 또는 is not null 형태의 비교만 가능하다.

따라서, 이러한 특성을 잘 활용하면 null 값을 포함한 데이터도 인덱스를 통해서 액세스가 가능하며, 인덱스 페이지의 저장 공간도 조금은 절약할 수 있다.

* null 값을 포함하는 인덱스 페이지의 저장 공간에 대한 내용은 차후에 좀 더 깊이 있게 연구하도록 하겠다.

/*
** 테스트 테이블 및 인덱스 생성
*/

create table 전표 (
전표번호 int not null,
결재상태 varchar(1) null
)

alter table 전표 add constraint 전표_pk primary key nonclustered(전표번호)
create index 전표_idx1 on 전표(결재상태)

전표 테이블에서 결재상태 컬럼의 값이 ‘결재완료’,’미결’ 의 두 가지 값만 가지고 있으며, 99% 이상이 ‘결재완료’이며 ‘미결’은 극히 일부분이라고 가정해 보자.

대용량 데이터베이스 솔루션 1권의 ‘NULL을 사용한 비교’ 부분을 보면, 오라클의 경우 ‘결재완료’를 null 값으로 대체하여 인덱스의 저장 공간도 절약하고, ‘결재완료’ 데이터 조회시 인덱스를 경유하지 않고 테이블을 전체 스캔하도록 유도하는 일석이조의 효과를 얻을 수 있다.

SQL Server 2000에서는 null값도 인덱스에 저장되므로 오라클과는 다른 방식으로 접근해야 한다. null값도 인덱스의 저장 공간을 차지하므로 공간 절약의 효과를 크게 기대하기는 어렵다. 또한, where col1 is null 과 같은 조건도 인덱스를 사용할 수 있으므로 전체 스캔으로 유도하는 것도 간단한 일이 아니다.

* 인덱스 페이지의 저장 공간 절약에 대해서 간단히 말씀드리자면, int나 char 등의 고정길이 타입은 null값을 입력해도 저장 공간이 절약되지 않는다. varchar나 nvarchar 같은 가변길이 타입만 null 값의 효과를 볼 수 있다.

그러면, 간단한 테스트를 통해서 null이 인덱스에 미치는 영향을 확인해 보자.

1. 전표 테이블의 데이터 중 대부분을 차지하는 ‘결재완료’ 전표의 결재상태를 ‘Y’로 입력하고 ‘미결’ 전표는 null 값으로 입력해 보았다. (전체 100,000건 중 20건만 ‘미결’이고 나머지는 모두 ‘결재완료’이다.)

select * from 전표
where 결재상태 is null

실행계획은 다음과 같다.

Rows   Executes   StmtText
-----------   -----------   ----------------------------------------------
20   1   select * from 전표 where 결재상태 is null
20   1   |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([encore].[dbo].[전표]))
20   1   |--Index Seek(OBJECT:([encore].[dbo].[전표].[전표_idx1]), SEEK:([전표].[결재상태]=NULL) ORDERED FORWARD)

인덱스를 사용해서 테이블을 액세스했음을 확인할 수 있다. 이번에는 인덱스 및 데이터의 저장 공간을 확인해 보자.

sp_spaceused 전표
name   rows   reserved   data    index_size   unused
-----------------------------------------------------------------------------
전표   100000   5384 KB   1872 KB   3408 KB   104 KB

인덱스 사이즈가 3408 KB 이다.

dbcc showcontig 명령어로 더 자세히 알아보자.

dbcc showcontig(전표, 전표_idx1)

DBCC SHOWCONTIG이(가) "전표" 테이블을 스캔하는 중...
테이블: "전표" (39671189); 인덱스 ID: 3, 데이터베이스 ID: 7
LEAF 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 235
- 스캔한 익스텐트..............................: 30
- 전환된 익스텐트..............................: 29
- 익스텐트 당 평균 페이지 수........................: 7.8
- 스캔 밀도[최적:실제].......: 100.00% [30:30]
- 논리 스캔 조각화 상태 ..................: 0.00%
- 익스텐트 스캔 조각화 상태 ...................: 0.00%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 11.3
- 평균 페이지 밀도(전체).....................: 99.86%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

2. 전표 테이블의 데이터 중 대부분을 차지하는 ‘결재완료’ 전표의 결재상태를 null로 입력하고 ‘미결’ 전표는 ‘N’으로 입력해 보았다. (전체 100,000만건 중 20건만 ‘미결’이고 나머지는 모두 ‘결재완료’이다.)

select * from 전표
where 결재상태 is null

Rows   Executes   StmtText
----------- ----------- ----------------------------------------------
99980   1   select * from 전표 where 결재상태 is null
99980   1   |--Table Scan(OBJECT:([encore].[dbo].[전표]), WHERE:([전표].[결재상태]=NULL))

이번에는 당연히 테이블 전체 스캔으로 실행되었다.
is not null 조건도 인덱스를 사용할 수 있는지 확인해 보자.

select * from 전표
where 결재상태 is not null

Rows   Executes   StmtText
----------- ----------- ----------------------------------------------
20   1   select * from 전표 where 결재상태 is not null
20   1   |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([encore].[dbo].[전표]))
20    1   |--Index Seek(OBJECT:([encore].[dbo].[전표].[전표_idx1]), SEEK:([전표].[결재상태] IsNotNull) ORDERED FORWARD)

쿼리 옵티마이저가 인덱스의 데이터 분포도를 분석하여, is not null 조건임에도 불구하고 인덱스를 사용하였다. 어느 정도 예상한 결과였지만 똑똑한 쿼리 옵티마이저를 칭찬하고 싶다.
인덱스 및 데이터의 저장 공간을 확인해 보자.

sp_spaceused 전표

name   rows   reserved   data   index_size   unused
-----------------------------------------------------------------------------
전표   100000   4360 KB   1352 KB   2920 KB   88 KB

1번 테스트 결과에 비해서 데이터 및 인덱스 사이즈가 모두 감소하였다.

dbcc showcontig(전표, 전표_idx1)

DBCC SHOWCONTIG이(가) "전표" 테이블을 스캔하는 중...
테이블: "전표" (7671075); 인덱스 ID: 3, 데이터베이스 ID: 7
LEAF 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 174
- 스캔한 익스텐트..............................: 22
- 전환된 익스텐트..............................: 21
- 익스텐트 당 평균 페이지 수........................: 7.9
- 스캔 밀도[최적:실제].......: 100.00% [22:22]
- 논리 스캔 조각화 상태 ..................: 0.57%
- 익스텐트 스캔 조각화 상태 ...................: 9.09%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 49.4
- 평균 페이지 밀도(전체).....................: 99.39%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

테스트 결과를 보면, SQL Server 2000에서는 null 값을 활용하더라도 오라클에서의 一石二鳥(공간 절약과 테이블 전체 스캔으로의 유도)의 효과를 얻을 수는 없다.

다만, is null 또는 is not null 비교를 통해서도 인덱스를 사용할 수 있다는 점과 약간의 공간을 절약할 수 있다는 점에 만족해야 하겠다.

SQL Server에서 null값의 처리 방식을 이해함으로써, 경우에 따라 적절한 활용 방법을 선택하는 것이 최선일 것이다.

제공 : DB포탈사이트 DBguide.net