DATOR


SQL Server 2000에서 update시 join의 활용 SQL Fundamental


< 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.
고객번호

 


서버: 메시지 157, 수준 15, 상태 1, 2
집계는 UPDATE 문의 SET 목록에 나타나지 않습니다.

 

 

Leave Comments