수안이의 컴퓨터 연구실
update나 delete시에도 select의 경우와 마찬가지로 원하는 대상집합을 정의하기 위해서 여러 테이블을 join할 수 있다. 대용량 데이터베이스 솔루션 2권에서는 이에 대해서 '확장 update문'이라는 형태로 설명하고 있다.
오라클과 SQL Server 2000 모두 이러한 형태의 SQL을 지원하지만, 문법의 차이가 상당하므로 이 둘의 차이점에 대해서 설명하고자 한다.
우선, update시 SQL문의 차이점에 대해서 알아보자.
2004년 2월에 가입한 고객에 대해서, 총주문회수와 총주문금액을 주문 테이블로부터 계산해서 고객 테이블에 반영하고자 한다.
< 오라클 9.2.0 >
실행결과는 다음과 같다.
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]
실행결과는 다음과 같다.
(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]
(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 목록에 나타나지 않습니다.
출처 : 엔코아 정보 컨설팅
오라클과 SQL Server 2000 모두 이러한 형태의 SQL을 지원하지만, 문법의 차이가 상당하므로 이 둘의 차이점에 대해서 설명하고자 한다.
우선, update시 SQL문의 차이점에 대해서 알아보자.
2004년 2월에 가입한 고객에 대해서, 총주문회수와 총주문금액을 주문 테이블로부터 계산해서 고객 테이블에 반영하고자 한다.
< 오라클 9.2.0 >
실행결과는 다음과 같다.
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]
실행결과는 다음과 같다.
(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]
(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 목록에 나타나지 않습니다.
출처 : 엔코아 정보 컨설팅
"MSSQL" 카테고리의 다른 글
- SQL Server 2000에서 varchar와 char 데이터 타입 (0)2007/05/21
- SQL 서버 2005 보안 (0)2007/05/21
- 새로운 SQL 서버 로그인 생성하기 (0)2007/05/21
- SQL Server 2000에서 update시 join의 활용 (0)2007/05/21
- Inside SQL Server Tip 갤러리 (0)2007/05/21
Leave your greetings.