CREATE TABLE Customer_1
(
CustomerID INT PRIMARY KEY CHECK (CustomerID < 100),
[Name] VARCHAR(30) NOT NULL,
Age TINYINT NOT NULL
)
CREATE TABLE Customer_2
(
CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 100 AND 199),
[Name] VARCHAR(30) NOT NULL,
Age TINYINT NOT NULL
)
CREATE TABLE Customer_3
(
CustomerID INT PRIMARY KEY CHECK (CustomerID > 199),
[Name] VARCHAR(30) NOT NULL,
Age TINYINT NOT NULL
)
GO
-- 뷰를 생성
CREATE VIEW Customer AS
SELECT * FROM Customer_1
UNION ALL
SELECT * FROM Customer_2
UNION ALL
SELECT * FROM Customer_3
GO
리스트1에서는 Customer_1, Customer_2, Customer_3 이렇게 3개의 테이블을 생성하여 CustomerID 컬럼의 값이 Customer_1 에는 100미만, Customer_2에는 100이상 199이하의 값, Customer_3에는 199 초과하는 행이 들어가도록 CHECK 제약 조건을 설정하였다. 이렇게 각각의 테이블에 중복 행이 발생하지 않게 적절하게 분할 열(여기서는 CustomerID)을 가지고 있는 테이블 UNION ALL 을 통해 하나의 논리적으로 합쳐 놓은 것이 분할 뷰인 것이다. Customer 뷰를 통해 CustomerID 컬럼의 값이 150 인 행을 검색하는 쿼리를 작성한 후 그 실행계획을 관찰해 보자.
SELECT * FROM Customer WHERE CustomerID = 150
GO
(1개 행 적용됨)
실행 계획
-------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Customer_2].[CustomerID]=[Customer_2].[CustomerID],
[Customer_2].[Name]=[Customer_2].[Name], [Customer_2].[Age]=[Customer_2].[Age]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Customer_2].[PK__Customer_2__2A164134]),
SEEK:([Customer_2].[CustomerID]=150) ORDERED FORWARD)
결과를 잘 살펴보면 Customer_2 테이블에 있는 PK__Customer_2__2A164134 만을 이용하여 Clustered Index Seek가 일어났음을 알 수 있다. Customer_1과 Customer_3의 내용은 전혀 참조하지 않았다. 이는 테이블 생성시 CustomerID 칼럼에 CHECK 제약조건을 만들었기 때문에 CustomerID = 150 인 행은 Customer_2 테이블에만 존재할 수 있음을 쿼리 최적화 프로그램은 알고 있기 때문에 위와같이 Customer_2 테이블만을 참조 하도록 실행 계획을 세운 것이다. 마찬가지로 CustomerID = 38 인 행을 검색하게 되면 Customer_1 테이블만을 참조하는 실행 계획을 세울 것이다. 하나의 테이블뿐 아니라 CustomerID IN(38, 150) 와 같이 2개의 테이블을 참조해야 할것으로 예상되는 쿼리 또한 예상대로 정확히 Customer_1과 Customer_2 테이블만 참조하도록 실행 계획을 세운다.
여기까지는 평범한 얘기들 뿐이다.. 이제부터 본격적으로 분할된 뷰의 고찰을 시작해 보자. 지금까지는 혹시나 분활된 뷰를 모르시는 분들을 위한 일종의 소개 페이지라고 생각된다..^^
넘어가기 전에 잠깐 다른 얘길 하자면, 본인은 분활된 뷰라는 말을 별로 안 좋아 한다. 분활된은 한글 형용사이고 뷰는 영어 명사다. 한글로 쓸꺼면 다 쓰던지...(다 한글로 쓰면.. 분할된 보기쯤 될려나??) 항상 본인은 어디가서 얘길할때 파티션드 뷰라고 말 한다. 분활된 뷰라는 얘긴 글을 쓸때만..ㅋㅋㅋ
분활된 뷰에 국한되는 얘기는 아니지만, 너무 억지로 한글로 바꿔 부를 필요는 없다고 본다. 처음부터 영어로 용어를 인지하는 것이 나중에 원서나 외국 사이트에서 글을 쉽게 읽게 해준다고 생각한다.. 뭐 그건 각자 알아서 할 일이고..각설하고,, 다시 주제로 돌아가자..^^
잠깐 BOL에 나와있는 제약 조건들을 살펴보자. BOL 색인에서 "분할된 뷰 만들기" 라는 항목을 찾으면 전체 내용을 볼 수 있다. 주 내용들은 테이블을 이렇게 만들어야 하고 이러면 안 된다, 컬럼은 이래야 하고 이래선 안 된다, 분할 열은 이래이래 해야한다는 식의 교회 목사님 설교와 같은 말들이다. 보통 이런 말들 할때는 졸기 쉽상인데, 다 피가되고 살이 되는 얘기니 유심히 보자. 지금은 대충 보더라도 나중에 분할된 뷰를 만들때는 꼭 하나하나 검토해 보길 바란다. 다음은 그 내용을 정리한 것이다.
테이블 규칙
- 구성원 테이블은 뷰에서 한 번 이상 참조할 수 없습니다.
- 구성원 테이블은 계산된 열에서 만들어진 인덱스를 가질 수 없습니다.
- 구성원 테이블은 같은 수의 열에 모두 PRIMARY KEY 제약 조건이 있어야 합니다.
- 구성원 테이블에는 동일한 ANSI 패딩 설정(SET ANSI_PADDIN)이 있어야 합니다.
열 규칙
- 각 구성원 테이블에 있는 모든 열은 SELECT 목록에 포함되어야 합니다.
- SELECT 목록에서 같은 열을 여러 번 사용할 수 없습니다.
- SELECT 목록에서 열을 한 번 이상 참조할 수 없습니다.
- 열은 SELECT 목록에서 같은 서수 위치에 있어야 합니다.
- 각 SELECT 문의 SELECT 목록에 있는 열은 유형(데이터 형식, 정밀도, 배율 및 정렬을 포함)이 같아야 합니다.
분할 열 규칙
- 각 기본 테이블에 CHECK 제약 조건에 의해 키 값이 수행되는 분할 열이 있습니다. 각 테이블에서 CHECK 제약 조건의 키 범위는 다른 테이블의 범위와 겹치지 않습니다. 분할 열에 제공된 값은 한 테이블로만 매핑되어야 합니다. CHECK 제약 조건에서는 BETWEEN, AND, OR, <, <=, >, >=, = 등의 연산자만 사용할 수 있습니다.
- 분할 열은 뷰에서 각 SELECT 문의 SELECT 목록에 같은 서수 위치에 있어야 합니다. 예를 들어, 분할 열은 항상 각 SELECT 목록에서 첫 번째 열 또는 두 번째 열 등이 되어야 합니다.
- 분할 열은 null을 허용하지 않습니다.
- 분할 열은 테이블의 기본 키의 일부여야 합니다.
- 분할 열은 계산된 열이 될 수 없습니다.
- 분할 열에는 하나의 제약 조건만 있어야 합니다. 제약 조건이 하나 이상 있으면 SQL Server가 모든 제약 조건을 무시하므로 뷰가 분할된 뷰인지 여부를 결정할 때 제약 조건을 고려하지 않습니다.
분할 컬럼의 규칙을 가장 유심히 보아야 할 것이다. 모 사이트에서 CHECK 제약 조건에서 사용할 수 있는 연산자중 BOL에서 명시되지 않은 IN 연산자를 사용할 수 있으니, 없으니 말이 많았는데 IN 연산자는 쿼리 최적화 프로그램에 의해 OR 구문으로 대치될 수 있음으로 사용이 가능하다. 직접 테스트한 결과이니 믿을 만 할것이다. 그리고 분할 컬럼은 기본 키(PRIMARY KEY)의 일부이어야 한다는 것을 주목하기 바란다. The Guru's Guide to SQL Server 라는 책을 보면 기본 키를 구성할 때 분할 컬럼의 그 순서까지도 제안하고 있는 정도이다. 이는 뒤에서 자세히 알아 보기로 하자. 여기서 말하고 싶은 사항은 정말 분할 컬럼이 기본 키의 일부이어야만 동작 할까라는 것이다. 당연히 그럴줄 알고 테스트를 해 보았는데, 재미있는 결과가 나왔다. 리스트1의 스크립트에서 분할 컬럼을 기본 키로 지정하지 않고 테스트를 해 보았다.
CREATE TABLE Customer_1
(
CustomerID INT CHECK (CustomerID < 100),
[Name] VARCHAR(30) NOT NULL PRIMARY KEY,
Age TINYINT NOT NULL
)
CREATE TABLE Customer_2
(
CustomerID INT CHECK (CustomerID BETWEEN 100 AND 199),
[Name] VARCHAR(30) NOT NULL PRIMARY KEY,
Age TINYINT NOT NULL
)
CREATE TABLE Customer_3
(
CustomerID INT CHECK (CustomerID > 199),
[Name] VARCHAR(30) NOT NULL PRIMARY KEY,
Age TINYINT NOT NULL
)
GO
CREATE VIEW Customer AS
SELECT * FROM Customer_1
UNION ALL
SELECT * FROM Customer_2
UNION ALL
SELECT * FROM Customer_3
GO
SELECT * FROM Customer WHERE CustomerID = 2
(실행계획)
StmtText
----------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<100 OR Convert([@1])=NULL)))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_1].[PK__Customer_1__56E8E7AB]), WH..
|--Filter(WHERE:(STARTUP EXPR((Convert([@1])<=199 AND Convert([@1])>=100) OR Convert([@1])=NULL)))
| |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_2].[PK__Customer_2__59C55456]), WH..
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])>199 OR Convert([@1])=NULL)))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_3].[PK__Customer_3__5CA1C101]), WH..
역시 실망 시키지 않은 결과이다. 분할 컬럼인 CustomerID를 기본 키에 포함시키지 않고 쿼리를 한 결과 분할된 뷰에 속한 3개의 테이블을 모두 참조하도록 실행 계획이 생성되었다. 분할된 뷰가 제대로 동작하는지 알아보는 방법중 하나가 바로 뷰를 통해 INSERT 구문을 실행하여 행을 추가해 보는 것이다. 리스트3에서 생성한 분활된 뷰를 통해 INSERT 구문을 실행할 경우 다음과 같은 에러를 만날 수 있다.
분할 열을 찾을 수 없어 UNION ALL 뷰 'Customer'을(를) 업데이트할 수 없습니다.
역시 분할 컬럼이 기본 키의 일부이어야지만 한다는 것을 입증하는 절망적인(?) 오류 메시지이다. 하지만 다음의 쿼리를 실행하여 계속 테스트해보자. 앞서 말한 재미있는 결과를 볼 수 있을 것이다.
SELECT * FROM Customer WHERE CustomerID = 2 OR CustomerID = 2
GO
(실행계획)
StmtText
----------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Customer_1].[CustomerID]=[Customer_1].[CustomerID], [Customer_1].[Name]=
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_1].[PK__Customer_1__56E8E7AB]), WHERE
SELECT * FROM Customer WHERE CustomerID IN(2,150)
GO
(실행계획)
StmtText
-----------------------------------------------------------------------------------------------------
|--Concatenation
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_1].[PK__Customer_1__56E8E7AB]), WHERE
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customer_2].[PK__Customer_2__59C55456]), WHERE
실행계획을 보자. 이상한 일이 벌어졌다. 검색 조건을 OR로 묶었더니 분할된 뷰로 동작을 하고 있다. CustomerID = 2 인 행은 Customer_1 테이블만 존재하고 CustomerID = 150 인 행은 Customer_2에만 존재할 수 있음을 쿼리 최적화 프로그램에서 인지하고 실행계획을 세웠다. 본인이 테스트한 환경은 SQL Server 2000 SP3a 이다. 다른 환경에서는 테스트를 해보지 않아 결과가 다르게 다르게 나올지는 모르겠으나, 분명 잘 하면 분할 컬럼을 기본 키에 포함되지 않아도 된다는 사실을 알 수 있었다. 그리고 Clustered Index Scan을 피하고자 한다면 각각의 테이블의 CustomerID 칼럼에 넌클러스터 인덱스를 하나씩 잡아주면 된다. 그러면 Index Seek가 일어날 것이다. 이제 쓸만한 예제를 가지고 어떤 경우에 분활된 뷰를 구성하면 어떤 이득이 있을지 알아보고, 어떤 제약이 있고, 그것을 어떻게 극복해 나갈것인가를 알아보도록 하자.
어떤 회사에 매출 정보를 저장해 놓은 테이블이 있다고 하자. 여기 근무하는 모씨는 매 분기 말이나 초가 되면 분기별 통계를 구하기 위해 바쁘다고 한다. 지금은 Sales 라는 테이블에서 모든 데이터를 관리하고 있는데, 점점 데이터는 늘어나는데 저장 필요한 데이터는 직전 분기 데이터 아니면 그해의 데이터들 뿐이고, 인덱스 관리에도 문제가 있고 해서 이를 매 분기별로 데이터를 쪼개어 분할된 뷰를 구성하기로 마음 먹었다. 매 년도마다 분기별로 테이블을 만들고 매출이 일어난 날짜를 저장하는 컬럼인 OrderDate 칼럼에 CHECK 제약 조건을 걸어 각 테이블에 들어갈 수 있는 행을 제한하였다. 그리고 이들 테이블을 UNION ALL 절로 모두 포함하는 뷰를 생성하여 일단 작업을 끝냈다. 모씨가 생성한 스크립트는 다음과 같다.
CREATE TABLE Sales2003_q1
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate)=2003 AND (DATEPART(mm, OrderDate) BETWEEN 1 AND 3)),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q1 PRIMARY KEY (OrderDate, OrderID)
)
CREATE TABLE Sales2003_q2
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate)=2003 AND (DATEPART(mm, OrderDate) BETWEEN 4 AND 6)),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q2 PRIMARY KEY (OrderDate, OrderID)
)
CREATE TABLE Sales2003_q3
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate)=2003 AND (DATEPART(mm, OrderDate) BETWEEN 7 AND 9)),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q3 PRIMARY KEY (OrderDate, OrderID)
)
CREATE TABLE Sales2003_q4
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK(DATEPART(yy, OrderDate)=2003 AND (DATEPART(mm, OrderDate) BETWEEN 10 AND 12)),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q4 PRIMARY KEY (OrderDate, OrderID)
)
GO
CREATE VIEW Sales AS
SELECT * FROM Sales2003_q1
UNION ALL
SELECT * FROM Sales2003_q2
UNION ALL
SELECT * FROM Sales2003_q3
UNION ALL
SELECT * FROM Sales2003_q4
GO
뭔가 해냈다는 뿌듯한 느낌에 모씨는 이제 신나게 기존 데이터를 새로 생성한 분기별 테이블로 옮겨올 것이다. 그리고 데이터를 모두 옮겼으면 SELECT 구문을 실행하여 그 실행계획을 관찰할 것이다. 우리도 2003년 10월 2일날 일어났던 모든 매출 정보를 쿼리해 보자.
SELECT * FROM Sales WHERE OrderDate >= '2003-10-02' AND OrderDate < '2003-10-03'
GO
(실행계획)
StmtText
-------------------------------------------------------------------------------------------
|--Concatenation
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sales2003_q1].[pk_Sales2003_q1]), SEEK:
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sales2003_q2].[pk_Sales2003_q2]), SEEK:
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sales2003_q3].[pk_Sales2003_q3]), SEEK:
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sales2003_q4].[pk_Sales2003_q4]), SEEK:
실행 결과를 확인한 모씨는 Sales2003_q4 테이블만 참조하는 실행 계획을 기대 하였으나 분할된 뷰를 구성하는 모든 테이블을 모두 참조하는 것을 보고 낙담하였을 것이다. 그래서 이래저래 해 보겠지만 결과는 같다. 그래서 결국 분할된 뷰의 도입을 포기하고 원래대로 돌아가며 가만히 있는게 가장 편하다고 푸념을 늘어 놓을지 모르겠다. (실은 본인이 이 비슷한 이유로 처음에 분할된 뷰의 도입에 실패하였다.ㅠㅠ) 분명 BOL에 제시한 모든 조건은 만족 하였다. 분할 컬럼의 조건으로는 AND와 BETWEEN 연산자만 사용 하였고, 분할 컬럼이 기본 키의 일부로 참여하고 있다. The Guru's Guide to SQL Server에서는 WHERE 절에 기본키를 구성하는 모든 컬럼을 조건으로 명시해 보라고 말하고 있지만, "AND OrderID = 100" 와 같은 식으로 WHERE 절에 조건을 추가 하여도 결과는 마찬가지 이다.
본인이 알아낸 이유는 2가지 이다. 먼저 분할 컬럼의 CHECK 제약 조건을 선언할때 분할 컬럼에 함수를 적용하거나 산술 연산을 수행하지 않아 분할 컬럼의 값을 변형시키는 일이 없도록 해야 한다는 것이다. 리스트5를 보면 DATEPART 함수를 이용하여 년도와 원을 구하는 부분을 볼 수 있다. 이를 확인하기 위해 본인은 리스트1의 스크립트를 약간 변형하여 테스트 하였다. CHECK 제약 조건에서 CustomerID 칼럼에 0을 더하도록 구성한 것이다. Customer_1 테이블의 경우라면 CHECK (CustomerID + 0 < 100) 이렇게 될 것이다. 0을 더하는 것은 절대 결과(범위)에 영향을 미칠 수 있는 요인이 아님을 분명히 하자. 이렇게 CHECK 제약 조건을 수정한 후 분할된 뷰를 다시 생성한 결과 참조 테이블을 한정짓지 못 하고 분할된 뷰에 속한 모든 테이블을 참조하는 실행 계획이 만들어 졌다.
2번째 이유는 분할 컬럼 자체에 DATETIME(SMALLDATETIME도 마찬가지) 형식을 사용하지 못 하는것 같다. DATETIME 형식을 분할 컬럼으로 사용하여 간단한 분할 뷰를 생성하여 결과를 관찰하였으나 제대로 동작하질 못 하였다. 혹시나 본인이 테스트가 잘 못 되었을 수 있음으로 테스트에 사용한 스크립트를 함께 올렸다. 다음이 그 스크립트이다.
CREATE TABLE t1
(
Orderdate DATETIME NOT NULL PRIMARY KEY
CHECK(Orderdate >= '2003-01-01' AND Orderdate < '2003-04-01'),
Data VARCHAR(20)
)
CREATE TABLE t2
(
Orderdate DATETIME NOT NULL PRIMARY KEY
CHECK(Orderdate >= '2003-04-01' AND Orderdate < '2003-07-01'),
Data VARCHAR(20))CREATE TABLE t3(Orderdate DATETIME NOT NULL PRIMARY KEY
CHECK(Orderdate >= '2003-07-01' AND Orderdate < '2003-10-01'),
Data VARCHAR(20)
)
CREATE TABLE t4
(
Orderdate DATETIME NOT NULL PRIMARY KEY
CHECK(Orderdate >= '2003-10-01' AND Orderdate < '2004-01-01'),
Data VARCHAR(20)
)
GO
CREATE VIEW t AS
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
UNION ALL
SELECT * FROM t3
UNION ALL
SELECT * FROM t4
GO
결국 리스트5에서 분할 컬럼을 바꿔야 한다는 얘기다. 어떤 방법이 좋을까? 가장 흔히 쓰는 방법이 컬럼을 2개 추가하는 것이다. 년도을 저장할 OrderYear(SMALLINT) 과 달을 저장할 OrderMonth(TINYINT) 컬럼이다. 그리고 새로 추가한 두 컬럼에 CHECK 제약 조건을 설정하고 두 컬럼 모두 기본 키에 포함시키면 모든것이 완벽해 진다. 아래는 리스트5를 수정하여 그 실행계획까지 모두 표시한 것이다.
CREATE TABLE Sales2003_q1
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate) = 2003 AND (DATEPART(mm, OrderDate) BETWEEN 1 AND 3)),
OrderYear SMALLINT NOT NULL CHECK(OrderYear = 2003),
OrderMonth TINYINT NOT NULL CHECK(OrderMonth BETWEEN 1 AND 3),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q1 PRIMARY KEY (OrderYear, OrderMonth, OrderID)
)
CREATE TABLE Sales2003_q2
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate) = 2003 AND (DATEPART(mm, OrderDate) BETWEEN 4 AND 6)),
OrderYear SMALLINT NOT NULL CHECK(OrderYear = 2003),
OrderMonth TINYINT NOT NULL CHECK(OrderMonth BETWEEN 4 AND 6),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q2 PRIMARY KEY (OrderYear, OrderMonth, OrderID)
)
CREATE TABLE Sales2003_q3
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate) = 2003 AND (DATEPART(mm, OrderDate) BETWEEN 7 AND 9)),
OrderYear SMALLINT NOT NULL CHECK(OrderYear = 2003),
OrderMonth TINYINT NOT NULL CHECK(OrderMonth BETWEEN 7 AND 9),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q3 PRIMARY KEY (OrderYear, OrderMonth, OrderID)
)
CREATE TABLE Sales2003_q4
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate) = 2003 AND (DATEPART(mm, OrderDate) BETWEEN 10 AND 12)),
OrderYear SMALLINT NOT NULL CHECK(OrderYear = 2003),
OrderMonth TINYINT NOT NULL CHECK(OrderMonth BETWEEN 10 AND 12),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q4 PRIMARY KEY (OrderYear, OrderMonth, OrderID)
)
GO
CREATE VIEW Sales AS
SELECT * FROM Sales2003_q1
UNION ALL
SELECT * FROM Sales2003_q2
UNION ALL
SELECT * FROM Sales2003_q3
UNION ALL
SELECT * FROM Sales2003_q4
GO
StmtText
----------------------------------------------------------------
SELECT * FROM Sales WHERE OrderYear = 2003 AND OrderMonth = 3
StmtText
----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Sales2003_q1].[OrderID]=[Sales2003_q1].[OrderID], [Sales2003_q1].
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Sales2003_q1].[pk_Sales2003_q1]), WHERE:
StmtText
---------------------------------------------------------------------------------
SELECT * FROM Sales WHERE OrderYear = 2003 AND OrderMonth = 3 AND OrderID = 1
StmtText
-----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Sales2003_q1].[OrderID]=[Sales2003_q1].[OrderID], [Sales2003_q1].
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Sales2003_q1].[pk_Sales2003_q1]), WHERE:
일단 분할된 뷰로서 잘 동작은 한다. 그런데 또 하나 문제가 있다.(참 문제가 많다...ㅠㅠ) 리스트8에서 두번째 실행한 쿼리를 보기 바란다.(WHERE 절에 OrderID = 1 조건이 추가된) 그리고 그 실행 계획을 보자. 뭔가 이상한 것이 없는가? 없다고 생각하면 인덱스를 좀더 공부하자..^^ 분명 문제가 있다. Sales2003_q1 테이블만을 참조 하기 했지만 그 방법에 문제가 있다. 좀더 이해하기 쉽게 또 다른 쿼리를 실행하여 비교해 보겠다.
StmtText
---------------------------------------------------------------------------------
SELECT * FROM Sales WHERE OrderYear = 2003 AND OrderMonth = 3 AND OrderID = 1
StmtText
-----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Sales2003_q1].[OrderID]=[Sales2003_q1].[OrderID], [Sales2003_q1].
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Sales2003_q1].[pk_Sales2003_q1]), WHERE:
StmtText
----------------------------------------------------------------------------------------
SELECT * FROM Sales2003_q1 WHERE OrderYear = 2003 AND OrderMonth = 3 AND OrderID = 1
StmtText
-----------------------------------------------------------------------------------------------
|--Filter(WHERE:(STARTUP EXPR(([@2]<=3 AND [@2]>=1) AND [@1]=2003)))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sales2003_q1].[pk_Sales2003_q1]), SEEK:
리스트9에서 두 쿼리문을 보자. FROM 절에서 기술한 개체만 다를 뿐 나머지 사항은 똑 같다. 결과는 어떠한가? 분할된 뷰를 통해 쿼리한 결과는 Clustered Index Scan가 일어났고 Sales2003_q1 테이블을 통해 직접 쿼리한 결과는 Clustered Index Seek가 일어났다. 쿼리 분석기를 통해 비교한 쿼리 비용만 해도 85.46% : 14.54% 로 분할된 뷰를 통해 쿼리한 결과가 너무 좋지 않다. 뭔가 인덱스를 잘 못 이용하고 있다는 느낌이 든다.
이유는 바로 분할된 뷰의 경우 쿼리 최적화 프로그램이 WHERE 절의 조건중 분할 컬럼에 관한 조건은 참조 테이블을 선택할 때만 사용하고 실제 해당 테이블에서 필요없는 조건들은 사용하지 않는 다는 것이다. 즉 Sales 분할된 뷰로 질의할때 WHERE 조건으로 "OrderYear = 2003 AND OrderMonth = 3 AND OrderID = 1" 이렇게 적었다. 이중 "OrderYear = 2003 AND OrderMonth = 3" 조건은 분할된 컬럼의 값들이므로 참조할 테이블만을 선택할 때 쓰인다. 2003년도 1분기에 해당하므로 Sales2003_q1 테이블만 참조하게 될 것이다. 이제 실제로 데이터를 검색하기 위해 Sales2003_q1 테이블로 가서는 모든 행이 "OrderYear = 2003"의 조건을 만족함을 알고 있기 때문에 또다시 검사할 필요없이 "OrderMonth = 3 AND OrderID = 1"을 만족하는 행을 찾게 되는 것이다. 실제 실행 계획 또한 다음과 같이 표시된다.
WHERE:([Sales2003_q1].[OrderMonth]=3 AND [Sales2003_q1].[OrderID]=1))
따라서 OrderMonth, OrderID 칼럼으로 구성된 인덱스가 없어서 이용할 인덱스가 없음으로 클러스터드 인덱스를 모두 뒤지라는(Clustered Index Scan) 실행 계획을 세운 것이다. 해결책은 간단하다. OrderMonth, OrderID 칼럼으로 구성된 복합 인덱스를 만들든지(칼럼의 순서는 상황에 따라 바뀌겠죠??), 테이블 생성시 기본 키의 순서를 OrderMonth, OrderID, OrderYear 순으로 바꾸면 된다.
Sales2003_q1 테이블을 직접 참조한 쿼리문의 경우 "OrderYear = 2003 AND OrderMonth = 3 AND OrderID = 1" 이라는 조건을 만족하는 인덱스가 있기 때문에(pk_Sales2003_q1) 이를 이용하여 Clustered Index Seek를 하도록 실행 계획을 세웠으니 적절한 실행 계획이 맞다.
이제 다시 모씨 얘기로 돌아오자. 모씨는 상사로 부터 한가지 지시를 받는다. 매출 정보를 최근 5분기만을 유지한채 나머지 데이터는 백업 서버로 옮겨 놓으라는 것이다. 만약 모씨가 분할된 뷰를 이용하지 않는다면 분기가 지날때 마다 테이블에서 지난 분기의 데이터를 찾아 백업 서버로 옮기고 지우는 작업을 해야할 것이다. 별로 어려운 작업은 아니지만 데이터의 조각화가 많이 일어 날것이 뻔하다. 한 테이블에 매분기마다 엄청난 데이터가 추가되고 삭제되니 조각화 많이 일어 날것이 분명하다. 하지만 분할된 뷰를 도입하였다면 다음과 같이 작업을 진행하였을 것이다.(2003년 4분기를 맞아 2002년 3분기의 내용을 지운다고 가정)
-- 2003년 4분기의 데이터를 저장할 테이블 생성
CREATE TABLE Sales2003_q4
(
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL
CHECK (DATEPART(yy, OrderDate) = 2003 AND (DATEPART(mm, OrderDate) BETWEEN 10 AND 12)),
OrderYear SMALLINT NOT NULL CHECK(OrderYear = 2003),
OrderMonth TINYINT NOT NULL CHECK(OrderMonth BETWEEN 10 AND 12),
Price MONEY NOT NULL,
CONSTRAINT pk_Sales2003_q4 PRIMARY KEY (OrderMonth, OrderID, OrderYear)
)
GO
-- 분할된 뷰의 갱신
ALTER VIEW Sales AS
SELECT * FROM Sales2002_q4
UNION ALL
SELECT * FROM Sales2003_q1
UNION ALL
SELECT * FROM Sales2003_q2
UNION ALL
SELECT * FROM Sales2003_q3
UNION ALL
SELECT * FROM Sales2003_q4
GO
-- 2002년 3분기의 데이터를 백업 서버로 옮김..
.
.
-- 2002년 3분기의 데이터를 삭제
DROP TABLE Sales2002_q3
GO
똑똑한 DBA라면 SQL Server Agent를 이용하여 매 분기 마지막날 새벽에 이러한 작업이 일어 나도록 작업을 자동화 해 놓았을 것이다.^^
마지막으로 이 SQLMania.net에서 쓰인 예를 살펴보고 다른 주제로 넘어가도록 하겠다. (이참에 광고나 해야지..) 이 사이트의 Tips & Articles라는 메뉴를 보면 SQL Server 관련 기술 문서가 있는 "기술문서(KB) - 영문/한글" 이라는 메뉴가 있다. 현재 한글 자료와 영문 자료가 각각 다른 테이블에 저장되어 있다. 그리고 이들은 분할된 뷰로 묶여져 있다. 먼저 테이블 생성 스크립트의 일부를 보자(전체 공개는 안되요~~~ 보안상..^^)
-- 한글 기술문서 목록
CREATE TABLE dbo.tblKblists_H
(
Idx int NOT NULL IDENTITY(1,1), -- 순번
hangul bit NOT NULL CHECK(hangul=1), -- 한글여부(0:영문/1:한글)
Num varchar(10) NOT NULL UNIQUE, -- 문서 번호(Qxxxxxx)
Title varchar(200) NOT NULL, -- 제목
CONSTRAINT pk_tblKblists_H PRIMARY KEY(Idx, hangul)
)
GO
-- 영문 기술문서 목록
CREATE TABLE dbo.tblKblists_E
(
Idx int NOT NULL IDENTITY(1,1), -- 순번
hangul bit NOT NULL CHECK(hangul=0), -- 한글여부(0:영문/1:한글)
Num varchar(10) NOT NULL UNIQUE, -- 문서 번호(Qxxxxxx)
Title varchar(200) NOT NULL, -- 제목
CONSTRAINT pk_tblKblists_E PRIMARY KEY(Idx, hangul)
)
GO
-- 분할된 뷰의 생성
CREATE VIEW vwKblists AS
SELECT * FROM dbo.tblKblists_H
UNION ALL
SELECT * FROM dbo.tblKblists_E
GO
대충 보니 잘 동작 할 것처럼 보인다. 일단 왜 테이블을 나누게 되었는지 부터 얘기 하겠다. 한글이냐 영문이냐를 구분하는 컬럼인 hangul만 가지고도 테이블 하나로 충분히 운영할 수 있는 문제였다. 고민의 시작은 기본 키(PRIMARY KEY)의 순서를 정하면서부터 시작되었다. 리스트11을 보면 Idx, hangul 순으로 기본 키가 구성되어 있다. 하지만 생각해 보자. 메뉴를 보면 한글 기술문서와 영문 기술문서로 나눠져 있어 사용자 들이 볼때는 둘 중 하나의 언어로 Idx 칼럼의 값을 역순으로 조회할 것이다. 쿼리문으로 쓰면 다음과 같을 것이다.
SELECT * FROM tblKblists WHERE hangul = 0 ORDER BY Idx DESC
물론 테이블 하나로 운영한다고 가정한 쿼리문이다. WHERE 절의 조건을 보면 hangul = 0 이라는 대단히 클 것으로 예상되는 범위로 Idx 칼럼을 역순으로 정렬하여 결과를 반환해야 할것으로 예상이 된다. 따라서 최적의 클러스터드 인덱스 구성은 (hangul, Idx DESC) 가 될 것이다. 하지만 위의 쿼리문은 기술 문서의 전체 리스트를 볼때만 유용하며, 기술 문서를 하나만 보고자 한다면 (하나의 기술 문서를 클릭했을 경우) WHERE 절에 Idx 컬럼만으로 검색이 이루어지기 때문에 hangul, Idx 순의 인덱스는 도움이 되지 않는다. 그래서 테이블 자체를 나눠 버리고 Idx, hangul 순으로 기본 키를 구성한 다음 분할된 뷰로 묶어 버린 것이다. 그러면 Idx, hangul 순의 인덱스 만으로도 모든 조건을 만족 할 수 있기 때문이다. (기본 키와 인덱스를 혼용하여 사용 하였는데, 기본 키를 이루는 칼럼이 클러스터드 인덱스로 구성되어 있음을 말 하는 것이다)
그런데 문제가 있다. 결정적으로 분할된 뷰로 동작할 것으로 예상으로 vwKblists 가 제대로 동작하지 않았다. WHERE hangul = 1 과 같이 질의를 하여도 2개의 테이블을 모두 참조하는 결과를 보였다. WHERE hangul = 1 AND Idx = 1 과 같이 기본 키를 이루는 모른 컬럼을 검색 조건에 등장 시켜도 결과는 똑같았다. 그래서 별짓(?) 다 했는데,, 결국 문제는 해결하였다..^^ 그 과정은 무지 힘들었다..
허무하게도 답은 간단했다. 분할 컬럼의 형식으로 bit 형식의 지정은 지원되지 않았다. hangul 컬럼의 형식만 tinyint로 바꾼 결과 분할된 뷰는 제대로 동작 하였다. 기타 모든 컬럼의 형식을 모두 테스트 해 보고 싶었지만, 시간이 허락하지 않아 해보지 못 했지만 만약 분할된 뷰가 제대로 동작하지 않는다면 컬럼의 형식을 한번 바꿔보기 바란다. 이제 분할된 뷰에 대한 것은 이쯤으로 접고 다음 페이지에서 분산 분할된 뷰에 대해서 알아 보도록 하겠다.
이번에는 분산 분할된 뷰(Distributed Partitioned View)에 대해서 알아보자. 지금까지는 알아본 분할된 뷰는 뷰에 종속되는 테이블이 모두 로컬 서버에 있었다. 하지만 분산 분할된 뷰는 뷰에 종속되는 테이블 중 적어도 하나가 리모트 서버에 존재하는 경우이다. 쉽게 말 하면 뷰를 통해 분산 질의를 한다는 것이다. 다만, 분할된 뷰의 경우에는 SQL Server 2000 Standard Edition 에서도 동작하지만 분산 분할된 뷰의 경우에는 SQL Server 2000 Enterprise Edition 이나 Developer Edition 에서만 동작한다는 것을 미리 알려둔다.
분산 분할된 뷰를 통해 분산 질의가 가능하다는 얘기는 무슨 뜻일까? 큰 포털 사이트에서 SQL Server를 이용하여 회원 정보를 관리하고 있다고 해보자. 회원 테이블이라는 곳은 로그인과 개인 정보 수정과 신규 가입을 받아 데이터를 저장해야 하는 곳이다. 따라서 이 테이블은 매우 수시로 참조되고 업데이트되고 추가되는 무지 바쁜 테이블이다. 아무리 잘 설계된 데이터베이스라도 시스템 성능상의 이유로 인해 한계를 가지기 마련이다. 그래서 서버를 추가하기로 했다. 기존 한 대로 운영되던 것을 두 대의 SQL Server을 이용하여 서비스 하기로 한 것이다. 하지만 DBMS 라는게 무엇인가? 웹서버처럼 L4 Switch 를 이용하여 사용자의 요청 패킷만 적절히 분산해 주면 되는 것이 아니다. 이 포털 사이트에 회원이 300만명쯤 된다고 하면, 이중 150만명은 Srv1 라는 SQL Server에서 나머지 150만명은 Srv2 라는 SQL Server에서 나눠 관리하게 하면 된다. 이렇게 하면 분명 서버 한대로 이 모든것을 관리하는 것 보다는 훨씬 속도적으로 빠를 적이 자명하다. 하지만 물리적으로 두 곳으로 나눠진 데이터를 논리적으로 하나로 합칠 필요가 있다. 그렇지 않으면 개발자가 데이터를 참조 하기위해서는 해당 데이터가 어느 서버에 위치하고 있는지 기억하고 있어야 하는 불편을 감수해야 한다. 이렇게 나눠진 데이터를 분산 분할된 뷰를 통해 하나의 논리 집합으로 만들 수 있는 것이다.
SQL Server 2000에서는 분산 분할된 뷰를 데이터의 파티셔닝을 지원하는데, 솔직히 말 하면 아직 기능이 미약하다. 앞서 알아본 분할된 뷰와 같이 분할될 컬럼에 CHECK 조건만으로 파티셔닝 가능한 규칙 기반의 파티셔닝 밖에 지원하지 않는다. 그나마 지원하는 CHECK 조건도 "~와 같다, ~같지 않다 " 정도의 연산자들 뿐이니 분명 한계가 있다. 만약 앞서 설명한 포털 사이트에서 150만명의 회원을 두 서버로 나눴다. 나눌 수 있는 기준은 분명 많이 있을 것이다. 복잡한 규칙을 세울수도 있겠지만 가장 쉽게 생각할 수 있는 방법은 등록일을 기준으로 가장 먼저 등록한 사람부터 시작해서 그 다음 순으로 150만명을 뽑아 나눌 수 있을 것이다. 그런데 문제가 있다. 먼저 가입한 150만명은 해당 포털 사이트에 싫증을 느끼고 잘 접속 하지 않는다. 다만 나중에 가입만 150만명은 이 포털 사이트에 대단히 광적이라 하루에도 10번씩 로그인 한다. 그럼 어떻게 되겠는가? 한쪽 서버는 놀고있고, 나머지 다른 서버 한대는 죽어라 일 하고 있을 것이다. 방법은? SQL Server 2000의 다음 버젼인 Yukon을 기다리는 수 밖에 없다..^^ 아니면 각자 환경에 맞게 알고리즘을 개발하여 적절히 로드를 분산시켜 주는 방법밖에 없다. Yukon에 대해 말할 수 없는 것이 아쉽지만, 기다려 보자. 그럼 어는 순간 우리 곁에 다가와 있을 것이다..
서론이 너무 길었다. 이제 분산 분할된 뷰를 본격적으로 알아 보자. 앞서 알아본 분할된 뷰를 이해하고 있다면 분산 분할된 뷰는 전혀 어렵지가 않다. 테이블 하나 이상만 원격 서버에 있다는 것 뿐이다. 그럼 리스트1의 간단한 회원 정보 테이블을 분산 분할된 뷰로 만들어 보자. 연결된 서버를 등록하고 하는 등의 추가된 작업들이 있는데, 이는 리스트12의 주석을 참고하자.
-- 먼저 연결된 서버를 등록한다.
-- S1\instance 에 위치한 SQL Server의 temped 데이터베이스를
-- SQL2라는 이름으로 연결된 서버를 등록했다.
EXEC sp_addlinkedserver @server='SQL2',@srvproduct='',
@provider='SQLOLEDB', @datasrc=''S1\instance'',
@catalog = 'tempdb'
-- SQL2 연결된 서버에 로그인할 사용자를 등록한다.
sp_addlinkedsrvlogin 'SQL2', 'false', NULL, 'sa', 'pwd'
-- 제대로 등록 되었는지 확인해 보자.
-- EM을 통해 보안->연결된 서버 에서 확인 하든지
-- Exec sp_linkedservers 저장 프로시저를 실행해 등록된 것을 확인하고
-- 직접 연결된 서버를 통해 질의해 보자.
-- 제대로 등록되었다면 뭐라뭐라 내용이 나온다.
SELECT * FROM SQL2.tempdb.dbo.sysindexes
-- 이제 로컬 서버와 원격 서버에 각각 테이블을 생성하고
-- 분산 분할된 뷰를 생성해 보자.
-- 로컬 서버에 해당 테이블 생성
CREATE TABLE Customer_1
(
CustomerID INT PRIMARY KEY CHECK (CustomerID < 10000),
[Name] VARCHAR(30) NOT NULL,
Age TINYINT NOT NULL
)
GO
-- 연결된 서버(SQL2)에 해당 테이블 생성
-- CREATE TABLE SQL2.tempdb.dbo.Customer_2 이렇게 쓰지 못 한다.
-- 쿼리 분석기에서 해당 서버로 직접 접속하여 만들자.
CREATE TABLE Customer_2
(
CustomerID INT PRIMARY KEY CHECK (CustomerID >= 10000),
[Name] VARCHAR(30) NOT NULL,
Age TINYINT NOT NULL
)
-- 다시 로컬서버에서 뷰를 생성
CREATE VIEW Customer AS
SELECT * FROM Customer_1
UNION ALL
SELECT * FROM SQL2.tempdb.dbo.Customer_2
GO
분산 분활된 뷰와 비교와여 크게 다른 점은 없다. 다만 연결된 서버를 한번 등록해 줘야 작업이 동반되어야 한다 한번 등록된 연결된 서버는 sp_dropserver 에 의해 제거되지 않는 한 계속 남아 있게 된다. 리스트12에서는 Customer_1 테이블에 회원 아이디가 10000 미만의 행이 들어가도록 하였고, 연결된 서버인 SQL2에 생성된 Customer_2에는 회원 아이디가 10000 이상인 행이 들어가도록 테이블을 만들었다. 그리고 회원 아이디가 13400 인 행을 Customer 분산 분할된 뷰를 통해 질의해 보자. 그 실행 계획은 아래와 같다.
[그림1 - 분산 분할된 뷰를 통해 질의한 쿼리의 실행 계획]
그림1을 보면 로컬 서버의 Customer_1은 참조되지 않고 리모트 쿼리가 일어난 것을 알 수 있다. 회원 아이디가 13400 인 행은 연결된 서버인 SQL2에 존재하기 때문에 분산 분할된 뷰가 제대로 동작하고 있음을 알 수 있다. 분산 분할된 뷰로 가장 잘 써먹을 수 있는 곳이 있다. SELECT 구문에서 LIKE 절을 사용하여 검색을 해야하는 상황이 있는데 여기서 어쩔 수 없이 테이블을 모두 스캔하여 그 결과를 나타내야 한다고 하면, 그냥 간단히 그 테이블을 반을 쪼개어 반은 로컬 서버에 나머지 반은 리모트 서버에 저장을 한 후, 이 둘을 분산 반할된 뷰로 묶으면 된다. 하지만 데이터의 행 수가 작으면 오히려 느려질 수 있으니 실제 적용전에 테스트를 해보는 것이 좋을 것이다.
(분산) 분할된 뷰를 통해 INSERT, UPDATE, DELETE 쿼리 또한 실행 가능하다. 적절한 분할 컬럼에 의해 삭제/변경/추가 될 행이 어느 한 테이블만을 참조해야 참조 해야할지 알 수 있기 때문이다. 하지만 여러 제약 조건들이 많다. 자세한 내용은 BOL을 직접 참조하기 바란다. BOL 색인에서 "분할된 뷰 만들기" 라는 제목으로 글을 찾으면 된다.
원문 : http://blog.naver.com/obelisk9/40034422871
'데이터베이스 > SQL Server' 카테고리의 다른 글
| SQL 알아야 할 11가지 팁 (0) | 2008.04.25 |
|---|---|
| 테이블을 메모리에 올리기 (0) | 2008.04.25 |
| SQL Server 2005의 Partitioned table 강좌 (0) | 2008.04.25 |
| SSIS에서의 ActiveX 스크립트 작업 처리 오류 (0) | 2008.04.25 |
| DTCPing.exe 활용 (0) | 2008.04.25 |