컴퓨토피아

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

SQL Server 2000에서 update시 join의 활용

update나 delete시에도 select의 경우와 마찬가지로 원하는 대상집합을 정의하기 위해서 여러 테이블을 join할 수 있다. 대용량 데이터베이스 솔루션 2권에서는 이에 대해서 "확장 update문"이라는 형태로 설명하고 있다.

오라클과 SQL Server 2000 모두 이러한 형태의 SQL을 지원하지만, 문법의 차이가 상당하므로 이 둘의 차이점에 대해서 설명하고자 한다.

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

create table 고객 (
고객번호 varchar(5) not null,
고객명 varchar(20),
가입일자 varchar(8),
총주문회수 int,
총주문금액 numeric(12)
)

create table 주문 (
주문번호 varchar(10) not null,
주문일자 varchar(8),
고객번호 varchar(5),
주문금액 numeric(12)
)

alter table 고객 add constraint 고객_pk primary key (고객번호)
create index 고객_idx1 on 고객(가입일자)

alter table 주문 add constraint 주문_pk primary key (주문번호)
create index 주문_idx1 on 주문 (고객번호)


/*
** 테스트용 데이터 등록
*/
insert into 고객 values("00001","홍길동","20040101",0,0)
insert into 고객 values("00002","이순신","20040201",0,0)
insert into 고객 values("00003","강감찬","20040201",0,0)

insert into 주문 values("A000000001","20040301","00001",15000)
insert into 주문 values("A000000002","20040315","00001",25000)
insert into 주문 values("A000000003","20040310","00002",35000)
insert into 주문 values("A000000004","20040331","00002",45000)

우선, update시 SQL문의 차이점에 대해서 알아보자.

2004년 2월에 가입한 고객에 대해서, 총주문회수와 총주문금액을 주문 테이블로부터 계산해서 고객 테이블에 반영하고자 한다.

< 오라클 9.2.0 >

update 고객 a
set (총주문회수, 총주문금액) = (select count(*), nvl(sum(b.주문금액), 0)
         from 주문 b
        where b.고객번호 = a.고객번호)
where a.가입일자 like "200402%"

실행결과는 다음과 같다.

Rows   Row Source Operation
---------- ---------------------------------------------------
  0   UPDATE
  2   TABLE ACCESS BY INDEX ROWID 고객
  2   INDEX RANGE SCAN 고객_IDX1 (고객_IDX1)
  2   SORT AGGREGATE
  2   TABLE ACCESS BY INDEX ROWID 주문
  2   INDEX RANGE SCAN 주문_IDX1 (주문_IDX1)


고객   고객명  가입일자  총주문회수  총주문금액
----- -------------------- -------- ---------- ----------
00001  홍길동  20040101  0  0
00002  이순신  20040201  2  80000
00003  강감찬  20040201  0  0

< SQL Server 2000 >

SQL Server 2000에서는 set (col1, col2) = (값1, 값2) 형태의 구문이 지원되지 않는다. 또한, "update table a"와 같이 테이블에 alias를 적용할 수도 없으므로 오라클과는 다른 형태의 SQL을 사용해야 한다.

[SQL #1]

update a
set 총주문회수 = b.총주문회수,
총주문금액 = b.총주문금액
from 고객 a,
(select b.고객번호, count(*) as 총주문회수, sum(b.주문금액) as 총주문금액
   from 주문 b
  group by b.고객번호
) b
where a.가입일자 like "200402%"
and b.고객번호 = a.고객번호

실행결과는 다음과 같다.

(1개 행 적용됨)

"고객" 테이블. 스캔 수 1, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0.
"주문" 테이블. 스캔 수 1, 논리적 읽기 수 5, 물리적 읽기 수 0, 미리 읽기 수 0.


Rows  Executes  StmtText
------------------------------------------------------------------------------
1  1 |--Table Update(OBJECT:([encore].[dbo].[고객]), SET:([고객].[총주문금액]=[Expr1006], [고객].[총주문회수]=[Expr1001]))
1  1 |--Compute Scalar(DEFINE:([Expr1006]=Convert([Expr1002])))
1  1   |--Top(ROWCOUNT est 0)
1  1   |--Merge Join(Inner Join, MERGE:([a].[고객번호])=([b].[고객번호]), RESIDUAL:([a].[고객번호]=[b].[고객번호]))
2  1     |--Sort(ORDER BY:([a].[고객번호] ASC))
2  1     | |--Bookmark Lookup(BOOKMARK:([Bmk1003]), OBJECT:([encore].[dbo].[고객] AS [a]))
2  1     |  |--Index Seek(OBJECT:([encore].[dbo].[고객].[고객_idx1] AS [a]), SEEK:([a].[가입일자] >= "200402" AND [a].[가입일자] < "200403"), WHERE:(like([a].[가입일자], "200402%", NULL)) ORDERED FORWARD)
2  1     |--Compute Scalar(DEFINE:([Expr1001]=Convert([Expr1013]), [Expr1002]=If ([Expr1014]=0) then NULL else [Expr1015]))
2  1      |--Stream Aggregate(GROUP BY:([b].[고객번호]) DEFINE:([Expr1013]=Count(*), [Expr1014]=COUNT_BIG([b].[주문금액]), [Expr1015]=SUM([b].[주문금액])))
4  1       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([encore].[dbo].[주문] AS [b]))
4  1         |--Index Scan(OBJECT:([encore].[dbo].[주문].[주문_idx1] AS [b]), ORDERED FORWARD)


고객번호  고객명   가입일자   총주문회수    총주문금액
------------------------------------------------------------------
00001   홍길동   20040101   0   0
00002   이순신   20040201   2   80000
00003   강감찬   20040201   0   0

(3개 행 적용됨)

또는, 다음과 같은 SQL도 가능하다.

[SQL #2]

update 고객
set 총주문회수 = b.총주문회수,
총주문금액 = b.총주문금액
from (select b.고객번호, count(*) as 총주문회수, sum(b.주문금액) as 총주문금액
   from 고객 a, 주문 b
  where a.가입일자 like "200402%"
   and b.고객번호 = a.고객번호
  group by b.고객번호
) b
where 고객.고객번호 = b.고객번호

(1개 행 적용됨)

"고객" 테이블. 스캔 수 2, 논리적 읽기 수 5, 물리적 읽기 수 0, 미리 읽기 수 0.
"Worktable" 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.
"주문" 테이블. 스캔 수 2, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0.


Rows Executes StmtText
------------------------------------------------------------------------------
1  1 |--Table Update(OBJECT:([encore].[dbo].[고객]), SET:([고객].[총주문금액]=[Expr1008], [고객].[총주문회수]=[Expr1002]))
1  1 |--Table Spool
1  1   |--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1003])))
1  1   |--Top(ROWCOUNT est 0)
1  1     |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[고객번호]))
1  1      |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1009]), [Expr1003]=If ([Expr1010]=0) then NULL else [Expr1011]))
1  1      | |--Stream Aggregate(GROUP BY:([b].[고객번호]) DEFINE:([Expr1009]=Count(*), [Expr1010]=COUNT_BIG([b].[주문금액]), [Expr1011]=SUM([b].[주문금액])))
2  1      |  |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([encore].[dbo].[주문] AS [b]))
2  1      |   |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[고객번호]))
2  1      |     |--Sort(ORDER BY:([a].[고객번호] ASC))
2  1      |     | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([encore].[dbo].[고객] AS [a]))
2  1      |     |  |--Index Seek(OBJECT:([encore].[dbo].[고객].[고객_idx1] AS [a]), SEEK:([a].[가입일자] >= "200402" AND [a].[가입일자] < "200403"), WHERE:(like([a].[가입일자], "200402%", NULL)) ORDERED FORWARD)
2  2      |     |--Index Seek(OBJECT:([encore].[dbo].[주문].[주문_idx1] AS [b]), SEEK:([b].[고객번호]=[a].[고객번호]) ORDERED FORWARD)
1  1      |--Index Seek(OBJECT:([encore].[dbo].[고객].[고객_pk]), SEEK:([고객].[고객번호]=[b].[고객번호]) ORDERED FORWARD)


고객번호 고객명   가입일자 총주문회수 총주문금액
------------------------------------------------------------------
00001 홍길동   20040101   0   0
00002 이순신   20040201   2   80000
00003 강감찬   20040201   0   0

(3개 행 적용됨)

첫 번째 SQL과 두 번째 SQL의 실행계획의 차이점은 다음과 같다.

첫 번째 SQL은 [주문_idx1] 인덱스를 Index Scan 했지만 고객 테이블은 단 1번만 액세스했다. 이 경우, 주문 테이블의 데이터량이 많지만 조건에 해당하는 고객이 적으면 불필요한 인덱스 페이지를 모두 스캔해야 하므로 비효율이 발생할 수 있다.

두 번째 SQL은 [주문_idx1] 인덱스를 Index Seek 했지만 고객 테이블을 두 번 액세스했다. 이 경우, 액세스 회수는 2번이지만 논리적 읽기 수는 첫 번째 SQL과 동일하므로 비효율이 발생했다고 보기는 어렵다.

주문 테이블의 데이터량이 많고 대부분의 데이터가 조건에 해당된다면, 첫 번째 SQL의 실행계획이 더 효율적일 수도 있다.

각 테이블의 데이터 분포 정도에 따라서 가장 효율적인 실행계획이 나오도록 적절한 형태의 SQL을 사용하면 된다.

* Index Scan : 인덱스에서 모든 행을 검색한다.
   오라클에서의 index full scan과 유사
* Index Seek : [SEEK:()] 조건자에 부합되는 행만 처리한다.
   오라클에서의 index unique scan 또는 index range scan과 유사
* 참고로 다음과 같이 SQL을 작성하면 구문 오류가 발생한다.

update 고객
set 총주문회수 = count(*),
총주문금액 = sum(b.주문금액)
from 고객 a, 주문 b
where a.가입일자 like "200402%"
and b.고객번호 = a.고객번호
group by b.고객번호

집계는 UPDATE 문의 SET 목록에 나타나지 않습니다.

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