컴퓨토피아

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