DBCC PINTABLE

Microsoft® SQL Server™가 테이블의 페이지를 메모리에서 플러시하지 않고 고정될 테이블을 표시합니다.

구문

DBCC PINTABLE ( database_id , table_id )

인수

database_id

고정될 테이블의 데이터베이스 ID 번호입니다. 데이터베이스 ID를 확인하려면 DB_ID 함수를 사용합니다.

table_id

고정될 테이블의 개체 ID 번호입니다. 테이블 ID를 확인하려면 OBJECT_ID 함수를 사용합니다.

비고

DBCC PINTABLE은 필요할 때마다 테이블을 메모리로 읽어들이는 대신, 일반 Transact-SQL 문에 의해 테이블의 페이지가 버퍼 캐시에 저장되면 고정된 페이지로 표시합니다. SQL Server는 새 페이지를 읽어들일 공간이 필요하더라도 고정된 페이지를 플러시하지 않으며, 페이지의 업데이트 사항을 계속 기록하고 필요하면 업데이트된 페이지를 디스크에 다시 씁니다. 그러나 SQL Server는 DBCC UNPINTABLE 문으로 테이블이 고정 해제될 때까지 버퍼 캐시에서 사용 가능한 페이지의 복사본을 보관합니다.

DBCC PINTABLE은 자주 참조되는 작은 테이블을 메모리에 저장할 때 적합합니다. 이 방법을 사용하면 작은 테이블의 페이지를 메모리에 한 번 읽어들인 후 향후 데이터를 참조할 때는 디스크에서 읽어올 필요가 없습니다.

주의   DBCC PINTABLE은 성능을 향상시킬 수 있지만 주의해서 사용해야 합니다. 커다란 테이블을 고정할 경우 많은 용량의 버퍼 캐시를 사용하므로 다른 테이블에서 사용할 캐시가 부족하게 됩니다. 버퍼 캐시보다 용량이 큰 테이블을 고정하면 전체 버퍼 캐시를 채울 수도 있습니다. 이런 경우 sysadmin 고정 서버 역할의 구성원이 SQL Server를 종료했다 다시 시작한 다음, 테이블을 고정 해제해야 합니다. 너무 많은 테이블을 고정해도 이와 같은 문제가 발생할 수 있습니다.

결과 집합

결과 집합은 다음과 같습니다.

경고: 고정하는 테이블은 주의해서 다루어야 합니다. 고정된 테이블이 사용 가능한 데이터
캐시보다 크거나 커지면 서버를 다시 시작해야 테이블의 고정이 해제됩니다.
DBCC 실행이 완료되었습니다. 
DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오. 

사용 권한

DBCC PINTABLE 권한은 sysadmin 고정 서버 역할의 구성원에게 기본적으로 부여되며, 양도할 수 없습니다.

예제

다음은 pubs 데이터베이스에서 authors 테이블을 고정하는 예제입니다.

DECLARE @db_id int, @tbl_id int USE pubs SET @db_id = DB_ID('pubs') 
SET @tbl_id = OBJECT_ID('pubs..authors') DBCC PINTABLE (@db_id, @tbl_id) 

관련 항목

DBCC

메모리 아키텍처

DBCC UNPINTABLE

sp_tableoption

©1988-2000 Microsoft Corporation. All Rights Reserved.

SQL Server 2000에서 새로 지원되는 기능중에서 분할된 뷰라는 매우 환영할만한 기능이 있다. 한번쯤 들어 보았거나 실제 업무에서 사용하고 있는지 모르겠다. 분할된 뷰란 여러 테이블을 UNION ALL 절을 이용하여 하나의 논리적인 집합으로 만든 뷰이지만, 뷰를 통해 SELECT 구문을 실행하였을 때 UNION ALL 절에 포함되는 모든 테이블을 뒤지는 것이 아니라 SELECT 구문의 WHERE 절의 조건을 만족하는 테이블만을 선택적으로 뒤지게 하여 성능상의 이득을 얻을 수 있는 장점이 있다. 하지만 처음 선보이는 기능이라서 그런지 몰라도 제약이 너무나 많다. 심지어는 BOL에 있는 예제 마저도 제대로 동작하지 않는 경우가 있다. 그래서 이글에서 계속 어떤 제약이 있는지를 중점으로 알아 볼 것이다. 먼저 분할된 뷰를 생성하는 간단한 예제부터 살펴보자.

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 - 분할된 뷰를 생성하는 예제]

리스트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)

[리스트2 - Customer 분할 뷰를 통해 쿼리 할때의 실행계획]

결과를 잘 살펴보면 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)이 있어야 합니다.

테이블은 규칙은 대충 무시할만 하다. 계산된 컬럼에 인덱스만 걸지않고 PRIMARY KEY 제약 조건만 있으면 그외 이상한(?) 짓만 안 하면 될 것 같다. 계속 컬럼의 규칙을 보자.

열 규칙

• 각 구성원 테이블에 있는 모든 열은 SELECT 목록에 포함되어야 합니다.
• SELECT 목록에서 같은 열을 여러 번 사용할 수 없습니다.
• SELECT 목록에서 열을 한 번 이상 참조할 수 없습니다.
• 열은 SELECT 목록에서 같은 서수 위치에 있어야 합니다.
• 각 SELECT 문의 SELECT 목록에 있는 열은 유형(데이터 형식, 정밀도, 배율 및 정렬을 포함)이 같아야 합니다.

뭔가 말이 많다. 간단한 방법을 제시하겠다. 테이블을 만들때는 리스트1의 Customer_1 테이블과 같이 테이블 이름과 CHECK 조건만을 바꾸고 나머지는 그대로 그 내용을 복사하여 테이블을 생성하자. 뷰를 생성할때도 SELECT 절 다음에 테이블의 모든 컬럼을 순서대로 적되, 각 컬럼은 한번씩만 적어 우선 하나의 SELECT 구문을 완성한 뒤, FROM 절의 내용만 바꿔 Copy & Paste 하여 뷰를 생성하자. 그럼 모든 조건을 만족하게 된다.(SELECT * FROM TABLE도 하나의 방법일 수 있겠다.)

분할 열 규칙

• 각 기본 테이블에 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..

[리스트3 - 분할 컬럼을 기본 키에 포함시키지 않은 예제]

역시 실망 시키지 않은 결과이다. 분할 컬럼인 CustomerID를 기본 키에 포함시키지 않고 쿼리를 한 결과 분할된 뷰에 속한 3개의 테이블을 모두 참조하도록 실행 계획이 생성되었다. 분할된 뷰가 제대로 동작하는지 알아보는 방법중 하나가 바로 뷰를 통해 INSERT 구문을 실행하여 행을 추가해 보는 것이다. 리스트3에서 생성한 분활된 뷰를 통해 INSERT 구문을 실행할 경우 다음과 같은 에러를 만날 수 있다.

서버: 메시지 4436, 수준 16, 상태 12, 줄 1
분할 열을 찾을 수 없어 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

[리스트4 - 분할 컬럼을 기본 키에 포함시키지 않은 분할된 뷰의 쿼리]

실행계획을 보자. 이상한 일이 벌어졌다. 검색 조건을 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

[리스트5 - 매 분기별로 매출 정보를 저장하도록 테이블과 분할 뷰 생성]

뭔가 해냈다는 뿌듯한 느낌에 모씨는 이제 신나게 기존 데이터를 새로 생성한 분기별 테이블로 옮겨올 것이다. 그리고 데이터를 모두 옮겼으면 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:

[리스트6 - 리스트5에서 생성한 분할뷰를 통해 쿼리한 실행계획]

실행 결과를 확인한 모씨는 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

[리스트7 - 분할 컬럼에 DATATIME 형식을 사용하지 못 함을 테스트한 스크립트]

결국 리스트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 - 리스트5의 스크립트를 수정하여 실행한 결과]

일단 분할된 뷰로서 잘 동작은 한다. 그런데 또 하나 문제가 있다.(참 문제가 많다...ㅠㅠ) 리스트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 - 인덱스를 참조하는 방법이 다른 두 쿼리문]

리스트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

[리스트10 - 분기 갱신의 문제를 분할된 뷰로 해결]

똑똑한 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

[리스트11 - SQLMania.net의 기술문서를 저장하고 있는 테이블 생성 스크립트]

대충 보니 잘 동작 할 것처럼 보인다. 일단 왜 테이블을 나누게 되었는지 부터 얘기 하겠다. 한글이냐 영문이냐를 구분하는 컬럼인 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

[리스트12 - 분산 분할된 뷰의 생성]

분산 분활된 뷰와 비교와여 크게 다른 점은 없다. 다만 연결된 서버를 한번 등록해 줘야 작업이 동반되어야 한다 한번 등록된 연결된 서버는 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

제 1강 : 파티션드 테이블

전문가 강좌: 김민석

테이블을 만들고 데이터를 적제하고 어떻게하면 할당된 PGAE 에 많은 데이터를 넣어서 효율적으로 관리할까? 테이블 사이즈가 40G ~ 80G 가 되고 있는데, 풀스캔을 한번 타면 어떻게 될까? 생각만 해도 아찔한 순간입니다.. 모 사이트의 어떤서버를 통합하니 1 TERA 테이블이 생겼다. 인덱스 리빌딩을 해야 하는데... 이럴경우 관리자가 떠 안아야 할 부담은 거의 절정을 이루게된다. 이러한 대용량 테이블을 보다 효과적으로 관리하기 위해서 SQL 2005 에서는 PARTITIONED TABLE 이라는 기술을 선보이고 있습니다. 모두 아는 기능이고 해서 특별히 시간내기 어렵고, 기능을 실습해 보고 싶은 분을 위해서 간단하게 따라하기 식으로 글을 적어 보았습니다. 이미 다른 상용 DBMS 들은 다양한 테이블 분할방법 (RANGE PARTITIONED TABLE, LIST PARTITIONED TABLE, HASH PARTITIONED TABLE)을 선보이며, 실제로 운영하고 있습니다. 늦은 감이 없지는 않지만, 언제까지 2008년에 제품지원이 중단되는 2000만을 MSSQL 이라고 생각하고 있는 사람들도 우끼는 이야기 라고 생각합니다. 어떤 데이터베이스가 가장 멋진 데이터베이스가 아니라, 해당 제품을 가장 잘 핸들링 하는 사람이 있는 데이터베이스가 가장 좋은 데이터베이스 라고 개인적으로 생각합니다. SQL 2000 엔터프라이즈를 쓰면서 SCHEMA BINDING INDEXED VIEW 도 모르고, MSCS 도 써보지 않았다면?? 혹자는 어려워 못쓸 것이고, 혹자는 들어보지 못해서 못 쓸 것입니다. 간단하게 실습하고 긴요하게 쓰이길 바랍니다. 우리가 꿈꾸고 갈망하던 많은 부분이 MSSQL 2005 를 통해서 해결되어 가고 있고, 더해서 차기 활화산(카트마이)도 멋지게 타고 있으니 열심히 공부해 보도록 하자. 목차 PARTITIONED TABLE      PARTITIONED FUNCTION      PARTITIONED SCHEME      CREATE PARTITIONED TABLE      CHECK PARTITIONED INFORMATION      PARTITION MERGE      PARTITION SPLIT      PARTITION SWITCH EXAMPLE      TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE ON SQL 2005      http://msdn2.microsoft.com/en-us/library/aa964122.aspx USE MASTER GO -- DROP DATABASE PDB -- GO /*----------------------------------------------------------------------- PARTITIONED TABLE       PARTITIONED FUNCTION       PARTITIONED SCHEME       CREATE PARTITIONED TABLE       CHECK PARTITIONED INFORMATION       PARTITION MERGE       PARTITION SPLIT       PARTITION SWITCH       * PARTITIONED FUNCTION 분할된 테이블 및 인덱스를 만드는 첫번째 단계로 CREATE PARTITION FUNCTION 이다. -------------------------------------------------------------------------*/ CREATE PARTITION FUNCTION PARTITION_FUNCTION_NAME ( INPUT_PARAMETER_TYPE ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ BOUNDARY_VALUE [ ,...N ] ] ) [ ; ] 기준이 있어야 나누지!! 어떤 기준으로 나눌지를 정의하는 단계이다. 예를 들어 나는 SEQUENCE 를 기준으로 나누기를 원한다라고 생각 했다면, CREATE PARTITION FUNCTION MYRANGEPF1 (INT) AS RANGE LEFT FOR VALUES (1, 100, 1000); 값 COL1 <= 1 COL1 > 1 AND COL1 <= 100 COL1 > 100 AND COL1 <= 1000 COL1 > 1000 CREATE PARTITION FUNCTION MYRANGEPF2 (INT) AS RANGE RIGHT FOR VALUES (1, 100, 1000); 값 COL1 < 1 COL1 >= 1 AND COL1 < 100 COL1 >= 100 AND COL1 < 1000 COL1 >= 1000 /************************* [질문] 내 맘데로 넣으면 넌 어떻게 할꺼야? **************************/ -- 실행 CREATE DATABASE PDB GO USE PDB GO CREATE PARTITION FUNCTION MYRANGEPF3 (INT) AS RANGE LEFT FOR VALUES ( 1, NULL,1000, 100,-100); GO -- DROP PARTITION FUNCTION MYRANGEPF3 결과 경고: 파티션 함수 'MYRANGEPF3'의 범위 값 목록이 값을 기준으로 정렬되어 있지 않습니다. CREATE PARTITION SCHEME 작업 동안 파일 그룹에 파티션을 매핑하면 함수 'MYRANGEPF3'이(가) CREATE PARTITION SCHEME에서 참조된 경우 정렬된 경계 값을 사용합니다. -- 확인 SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE           FUNCTION_ID IN                    (                    SELECT FUNCTION_ID                    FROM SYS.PARTITION_FUNCTIONS                    WHERE NAME = ('MYRANGEPF3')                    ) 결과 FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -100 65536 3 1 1 65536 4 1 100 65536 5 1 1000 똑똑한 SQL 서버는 이 값들을 정렬해서 순서를 재배열 하여 사용하게 된다. 또한 NULL 은 우선순위 최상으로 소팅되는 되는 것을 알수 있다. 음 나누다 보니 보기 이상한데 하나더 나누어 보고 싶다면 어떻게 할까? 바꾸어 보자! /* 구문정보 ALTER PARTITION FUNCTION PARTITION_FUNCTION_NAME() {     SPLIT RANGE ( BOUNDARY_VALUE )    | MERGE RANGE ( BOUNDARY_VALUE ) } [ ; ] */ 생성된 모든 파티션을 확인 부터 해보자. 개별 파티션 함수에 대한 정보를 얻으려면 SELECT * FROM SYS.PARTITION_FUNCTIONS             --(파티션 함수정보) SELECT * FROM SYS.PARTITION_PARAMETERS               --(매개 변수 정보) SELECT * FROM SYS.PARTITION_RANGE_VALUES             --(경계구성) SELECT * FROM SYS.PARTITION_SCHEMES               --(파티션 구성정보) SELECT * FROM SYS.DATA_SPACES               --(파티션 구성정보) SELECT * FROM SYS.DESTINATION_DATA_SPACES              --(개별 파티션 구성정보) SELECT * FROM SYS.PARTITIONS             --(파티션 정보) SELECT * FROM SYS.TABLES              --(테이블 분할정보) SELECT * FROM SYS.INDEXES              --(인덱스 분할정보) SELECT * FROM SYS.INDEX_COLUMNS               --(인덱스 컬럼정보) -- SPLIT ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (-1000); GO SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE          FUNCTION_ID IN                  (                  SELECT FUNCTION_ID                  FROM SYS.PARTITION_FUNCTIONS                  WHERE NAME = ('MYRANGEPF3')                  ) 결과 FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -1000 65536 3 1 -100 65536 4 1 1 65536 5 1 100 65536 6 1 1000 알아서 잘 배열 되는 것을 관찰할 수 있다. -- MERGE ALTER PARTITION FUNCTION MYRANGEPF3 () MERGE RANGE (-1000); FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----------- 65536 1 1 NULL 65536 2 1 -100 65536 3 1 1 65536 4 1 100 65536 5 1 1000 정리 PARTITION_RANGE_VALUES + 1 개의 영역이 생기게 되고,기준값은 LEFT, RIGHT 의 지정에 따라 기준값의 위치가 다르게 속하게 되는 것을 알 수 있다. 예를들어 RIGHT 파티션을 만들고 경계값이 NULL 이 있다면, RIGHT 파티션의 경우 기준값을 포함하지 않는 더작은 영역을 만들게 된다. 그러나 NULL 보다 더 작은 값이 없으므로 첫번째 파티션은 비게 된다. DROP PARTITION FUNCTION MYRANGEPF3 GO CREATE PARTITION FUNCTION MYRANGEPF3 (INT) AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000); GO 참고 : 기준값을 정열해서 배치하면 에러 없음 5개로 기준값으로 나누었으니 다음과 같은 6개의 영역이 생긴다. 1                                     값       <     NULL    --                                   첫번째 기준값 보다 작은 값들 (빈파티션) 2    NULL      <=       값      <       -100       -- NULL                포함 기준값보다 더 큰 값들 3    -100       <=       값      <        1                         -- -100                   포함 다음기준값 보다 작은 값들 4    1                      <=     값       <           100                     -- 1                    포함 다음기준값 보다 작은 값들 5    100                   <=     값       <           1000        -- 100                    포함 다음기준값 보다 작은 값들 6                                     값       >=         1000        -- 1000                  포함 더 큰값들 다 /*----------------------------------------------------------------------- PARTITIONED TABLE           PARTITIONED FUNCTION           PARTITIONED SCHEME           CREATE PARTITIONED TABLE           CHECK PARTITIONED INFORMATION           PARTITION MERGE           PARTITION SPLIT           PARTITION SWITCH           * PARTITIONED SCHEME 파일그룹 매핑 구성표 만들기 (필수 선행작업: 파티션 함수 생성) -------------------------------------------------------------------------*/ /* 구문정보 CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) [ ; ] */ ALL 을 지정하면 하나의 파일그룹만을 지정 할 수 있고, 파티션 펑션의 기준값 보다 같거나 많은 개수를 정의해야 하며, 적을 경우 에러를 나타내며 종료 많은경우 NEXT USED 로 정의 됨 -- 실험 CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2) 결과 메시지 7707, 수준 16, 상태 1, 줄 2 연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다. 음~ 파일그룹이 있는지 없는지 검사하지도 않고 바로 에러! -- 설정하려는 파티션 펑션의 기준값 개수 확인 SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE           FUNCTION_ID IN                      (                      SELECT FUNCTION_ID                      FROM SYS.PARTITION_FUNCTIONS                      WHERE NAME = ('MYRANGEPF3')                      ) 65537 1 1 NULL 65537 2 1 -100 65537 3 1 1 65537 4 1 100 65537 5 1 1000 -- 음~ 최소 6개 만들고 더 만들면 다음파티션으로 쓰겠군... CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7) 결과 메시지 208, 수준 16, 상태 58, 줄 1 개체 이름 'FG1'이(가) 잘못되었습니다. 개수를 만족하고 나서 파일 그룹이 실제로 있는지 찾음 해당데이터 베이스에 파일그룹이 없으면 에러가 난다. 여기서 잠깐! 스토리지 이야기도 조금 하고 파일그룹 및 데이터 파일 핸들링 하는 방법에 대해서도 조금만 알아보자. 데이터 베이스에서 파일의 숫자 만큼 쓰래드가 생성 될 수 있기 때문에 파일을 여러개 만드는 것이 좋다. 클러스터 인덱스가 있는 데이터 파일을 파일그룹, 난클러스터 인덱스는 다른 디스크에 위치시키는 것이 절대적으로 유리하다. 파일그룹을 잘게 나누면 파일그룹별로 백업 복구도 가능하고 용량산정을 위해 다른 위치로 옮길때도 옮기기 좋은 단위가 된다는 것을 상기하자! 또한 로그파일[LDF] 는 분리된 다른 위치에 독립적으로 쓰게 하는것이 좋다. I/O 패턴이 다른 파일을 같은 디스크에 배치하는 것은 어떤경우에도 좋은 방법이 되지 못한다. 개인적으로 로그는 300G 10000RPM RAID 1 을 추천하고 싶다. 최대 테이블 사이즈가 300기가를 넘는다면 다른 방법을 구상하도록 한다. 또한 데이터 디스크는 RAID 1+0 를 추천한다. MMORPG 같은 경우나 빈번하고 짧은 트랜젝션이 많은 데이터베이스에서 쓰기스피드가 느린 RAID 5 는 CheckPoint시 트랜잭션 숫자를 감소시키는 주요 범인이 된다. 로컬백업을 한다면 로컬백업이 되는 위치는 백업시간을 단축하기 위해 RAID 0 에 위치시킨다. 두장의 RAID 5 로 구성된 디스크 두장에 백업 하는것 보다 두장의 RAID 0 로 백업한다면 훨씬 더 빠른 백업을 수행 할 수 있다. 그러나 디스크 장애로 잦은 RAID 리빌딩을 하기 싫다면 디스크 에러에 강한 RAID 1+0 로 구성하는 것이 바르다고 할 수 있다. 이런 디스크 문제가 빈번하게 일어난다면 스토리지 시스템을 도입하는 것을 고려해 보기 바란다. 스토리지 시스템은 자체 리드 캐쉬 라이트 캐쉬가 있기 때문에 메모리까지만 받아주면 스토리지 컨트롤러가 알아서 디스크에 쓸것이다. 최신 엔터프라이즈 스토리지는 컨트롤러 캐쉬가 16G에 이른다. -- 파일그룹 생성방법 ALTER DATABASE PDB ADD FILEGROUP FG1 -- 파일그룹에 간단한 파일 추가 ALTER DATABASE PDB ADD FILE ( NAME = PDB_DAT1, FILENAME = 'C:\FG1\PDB_DAT1.ndf') TO FILEGROUP FG1 -- 삭제방법 -- 1, 파일 삭제 ALTER DATABASE PDB REMOVE FILE PDB_DAT1 -- 2, 파일그룹 삭제 ALTER DATABASE PDB REMOVE FILEGROUP FG1 -- 좀더 자세하게 USE master GO ALTER DATABASE PDB ADD FILEGROUP FG1; ALTER DATABASE PDB ADD FILEGROUP FG2; ALTER DATABASE PDB ADD FILEGROUP FG3; ALTER DATABASE PDB ADD FILEGROUP FG4; ALTER DATABASE PDB ADD FILEGROUP FG5; ALTER DATABASE PDB ADD FILEGROUP FG6; ALTER DATABASE PDB ADD FILEGROUP FG7; ALTER DATABASE PDB ADD FILEGROUP FG8; GO -- 해당경로를 미리 만들어 두어야 한다. -- C:\ 드라이브에 FG1,FG2,FG3,FG4,FG5 -- G:\ 드라이브에 FG6,FG7,FG8 ALTER DATABASE PDB ADD FILE (     NAME = PDB_DAT1,     FILENAME = 'C:\FG1\PDB_DAT1.ndf', -- 테스트 이므로     SIZE = 5MB,     MAXSIZE = 100MB,     FILEGROWTH = 5MB ) --, -- 여러개의 파일로 분할 하고 싶은 경우 --( -- NAME = PDB_DAT2, -- FILENAME = 'C:\FG1\PDB_DAT2.ndf', -- SIZE = 5MB, -- MAXSIZE = 100MB, -- FILEGROWTH = 5MB --) TO FILEGROUP FG1 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT2,FILENAME = 'C:\FG2\PDB_DAT2.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG2 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3,FILENAME = 'C:\FG3\PDB_DAT3.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT4,FILENAME = 'C:\FG4\PDB_DAT4.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG4 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT5,FILENAME = 'C:\FG5\PDB_DAT5.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG5 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT6,FILENAME = 'G:\FG6\PDB_DAT6.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG6 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT7,FILENAME = 'G:\FG7\PDB_DAT7.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG7 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT8,FILENAME = 'G:\FG8\PDB_DAT8.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG8 -- 다시 하던일 계속하자! USE PDB GO CREATE PARTITION SCHEME PSMYSCHEME3 AS PARTITION MYRANGEPF3 TO (FG1,FG2,FG3,FG4,FG5,FG6,FG7,FG8) GO 결과 R1 파티션 구성표 'PSMYSCHEME3'이(가) 작성되었습니다. 파티션 구성표 'PSMYSCHEME3'에서 'FG7'은(는) 다음에 사용되는 파일 그룹으로 표시됩니다. 다음에 사용되는 파일 그룹 다음에 지정된 1 파일 그룹은 무시됩니다. 음~ 우리는 6개의 파일그룹만 필요한데, 더 만들어 두니 바로 다음 파일그룹은 NEXT USED 로 지정되고 나머지들은 무시되는 것이구나. -- 삭제방법 -- DROP PARTITION SCHEME PSMYSCHEME3 -- 해당디비의 상황 EXEC SP_HELPDB N'PDB' EXEC SP_HELPFILEGROUP; EXEC SP_HELPFILE; SELECT * FROM SYS.SYSALTFILES WHERE DBID = DB_ID('PDB') 여기서 잠깐! 데이터 베이스 미러링 같은 경우 피지컬 영역 구성이 다를수 있고 스토리지 용량도 보장 할 수 없고 이런 파티션 스킴을 고치는 작업은 미러링중에 미러 대상서버에 적용되지 않는다. /*----------------------------------------------------------------------- PARTITIONED TABLE         PARTITIONED FUNCTION         PARTITIONED SCHEME         CREATE PARTITIONED TABLE         CHECK PARTITIONED INFORMATION         PARTITION MERGE         PARTITION SPLIT         PARTITION SWITCH         * CREATE PARTITIONED TABLE 준비하는데 수고했습니다. 이제 파티션드 테이블을 만들어 보겠습니다. -------------------------------------------------------------------------*/ -- 두번째 작업에서 힘들게 만들었던 파일스키마 정의에 테이블을 매핑 시켜 만든다. IF OBJECT_ID('DBO.PT_LOG') IS NOT NULL DROP TABLE DBO.PT_LOG GO CREATE TABLE [dbo].[PT_LOG] (              PIDX INT -- 파티션 기준값         , IDX INT IDENTITY(1,1)         , DATA VARCHAR(6000) NULL ) ON PSMYSCHEME3 (PIDX) GO /*----------------------------------------------------------------------- PARTITIONED TABLE         PARTITIONED FUNCTION         PARTITIONED SCHEME         CREATE PARTITIONED TABLE         CHECK PARTITIONED INFORMATION         PARTITION MERGE         PARTITION SPLIT         PARTITION SWITCH         * CHECK PARTITIONED INFORMATION ,PARTITION MERGE, PARTITION SPLIT 데이터 삽입과 파티션 정보확인 및 MERGE SPLIT 해보도록 하겠습니다. NULL 은 어디에 있을까? 궁금하지 않나요? -------------------------------------------------------------------------*/ INSERT INTO PT_LOG (PIDX, DATA) VALUES (NULL, REPLICATE('A',6000)) GO 601 -- 인서트 601번 실행 INSERT INTO PT_LOG (PIDX, DATA) VALUES (-100, REPLICATE('A',6000)) GO 602 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1, REPLICATE('A',6000)) GO 603 INSERT INTO PT_LOG (PIDX, DATA) VALUES (100, REPLICATE('A',6000)) GO 604 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1000, REPLICATE('A',6000)) GO 605 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2000, REPLICATE('A',6000)) GO 606 SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1008],0)))          |--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))                 |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007])) **                    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6))))                        |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG])) 테이블 'PT_LOG'. 검색 수 6, 논리적 읽기 수 3621, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0. 별표 별표(**) 부분이 어떤 파티션을 읽었는지 보여주눈 부분이다. 상수 조인을 이용한 6회 읽은 것을 알 수 있다. SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과 3621 SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG WHERE PIDX IS NULL SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))          |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))                |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX] IS NULL) PARTITION ID:((2))) -- 파티션 아이디 2번에서만 읽은 것을 볼 수 있다. 아무리 봐도 넘 이쁜 넘이다. SET STATISTICS IO ON SET STATISTICS PROFILE ON SELECT COUNT(*) FROM PT_LOG WHERE          PIDX IS NULL OR          PIDX = 1 GROUP BY PIDX HAVING PIDX = 1 SET STATISTICS IO OFF SET STATISTICS PROFILE OFF 결과    |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))          |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))                |--Table Scan(OBJECT:([PDB].[dbo].[PT_LOG]), WHERE:([PDB].[dbo].[PT_LOG].[PIDX]=(1)) PARTITION ID:((4))) SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 partition_id   object_id      index_id      partition_number   hobt_id      rows PT_LOG 72057594040680448 53575229 0 1 72057594040680448 0 PT_LOG 72057594040745984 53575229 0 2 72057594040745984 601 PT_LOG 72057594040811520 53575229 0 3 72057594040811520 602 PT_LOG 72057594040877056 53575229 0 4 72057594040877056 603 PT_LOG 72057594040942592 53575229 0 5 72057594040942592 604 PT_LOG 72057594041008128 53575229 0 6 72057594041008128 1211 위와같이 RIGHT TYPE 에서는 NULL 보다 논리적으로 더 작은 것이 없으므로 제일 안쪽 파티션이 항상 비는 것을 알 수 있다. 음~ 배운게 있으니 1211 을 적당하게 다시 나누어 보아야 겠다. 파티션 기준값을 어떻게 나누었는지 다음 쿼리로 확인해 보자. SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE      FUNCTION_ID IN          (          SELECT FUNCTION_ID          FROM SYS.PARTITION_FUNCTIONS          WHERE NAME = ('MYRANGEPF3')          ) 결과 function_id boundary_id parameter_id value ----------- ----------- ----------- ----- 65541 1 1 NULL 65541 2 1 -100 65541 3 1 1 65541 4 1 100 65541 5 1 1000 2000으로 경계값을 더 나누어 보자 위에서 아래와 같은 데이터를 넣었다는것을 우리는 알고 있으므로 -- INSERT INTO PT_LOG (PIDX, DATA) VALUES (2000, REPLICATE('A',6000)) -- GO 606 -- 다음과 같은 쿼리를 실행했다. ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (2000); GO 결과 잘 나누어져 들어갔음을 확인 할 수 있다. FUNCTION_ID BOUNDARY_ID PARAMETER_ID VALUE ----------- ----------- ----------- ----- 65541 1 1 NULL 65541 2 1 -100 65541 3 1 1 65541 4 1 100 65541 5 1 1000 65541 6 1 2000 ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (3000); GO 결과 메시지 7707, 수준 16, 상태 1, 줄 1 연결된 파티션 함수 'MYRANGEPF3'은(는) 구성표 'PSMYSCHEME3'에 언급된 파일 그룹보다 많은 파티션을 생성합니다. 우리는 위에서 파티션 스키마를 만들때, 결과 R1 에서 NEXT USED 로 설정된 FG7 까지만 적용이 되고 FG8에는 무시되었던 것을 기억할 것입니다. 이런 결과 값이 나오는 테이블에서 2000 보다 큰 값이 들어갈 영역을 만들려고 했으나, 역시 무시되는 것을 알 수 있습니다. 그럼 NEXT USED로 다음 파일그룹이 사용될 영역만 다시 정의해 주면 될것을 생각할 수 있겠습니다. EXEC SP_HELP N'PT_LOG' 결과 Data_located_on_filegroup ------------------------- PSMYSCHEME3 -- 해당 테이블의 스키마 정보를 이용해서 어떤 파일그룹을 쓰고 있는지 조사 SELECT             A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,             B.DATA_SPACE_ID,             C.NAME FROM             SYS.DATA_SPACES A             INNER JOIN SYS.DESTINATION_DATA_SPACES B                     ON A.DATA_SPACE_ID = B.DATA_SPACE_ID             INNER JOIN SYS.PARTITION_SCHEMES C                     ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 NAME DATA_SPACE_ID IS_DEFAULT DATA_SPACE_ID NAME ---- ------------- ------------- ------------- ---- FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 -- 해당 디비의 파일그룹 조회 EXEC SP_HELPFILEGROUP 결과 groupname groupid filecount --------- ------- --------- PRIMARY 1 1 FG1 2 1 FG2 3 1 FG3 4 1 FG4 5 1 FG5 6 1 FG6 7 1 FG7 8 1 FG8 9 1 -- 해당 스킴의 파티션펑션 및 TYPE 정보 조회 SELECT             A.BOUNDARY_ID, A.VALUE,             B.NAME,B.TYPE_DESC,B.FANOUT, BOUNDARY_VALUE_ON_RIGHT FROM SYS.PARTITION_RANGE_VALUES A             INNER JOIN SYS.PARTITION_FUNCTIONS B             ON A.FUNCTION_ID = B.FUNCTION_ID             INNER JOIN SYS.PARTITION_SCHEMES C             ON B.FUNCTION_ID = C.FUNCTION_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 BOUNDARY_ID VALUE NAME TYPE_DESC FANOUT BOUNDARY_VALUE_ON_RIGHT ----------- ---------- --------- ------ ----------------------- 1 NULL MYRANGEPF3 RANGE 8 1 2 -100 MYRANGEPF3 RANGE 8 1 3 1 MYRANGEPF3 RANGE 8 1 4 100 MYRANGEPF3 RANGE 8 1 5 1000 MYRANGEPF3 RANGE 8 1 6 2000 MYRANGEPF3 RANGE 8 1 해당하는 스키마는 PRIMARY 파일 그룹과 FG8을 안쓰고 있다는 것을 알 수 있다. 우리는 FG8을 다음 파티션 그룹으로 쓰기로 하자. -- 용법 ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ] [ ; ] -- 실행 ALTER PARTITION SCHEME PSMYSCHEME3 NEXT USED FG8 GO 결과 명령이 완료되었습니다. -- 확인 SELECT              A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,              B.DATA_SPACE_ID,              C.NAME FROM              SYS.DATA_SPACES A              INNER JOIN SYS.DESTINATION_DATA_SPACES B                         ON A.DATA_SPACE_ID = B.DATA_SPACE_ID              INNER JOIN SYS.PARTITION_SCHEMES C                         ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' groupname groupid filecount --------- ------- --------- BOUNDARY_ID VALUE NAME TYPE_DESC FANOUT BOUNDARY_VALUE_ON_RIGHT FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 FG8 9 0 9 PSMYSCHEME3 잘 적용된것을 확인 할 수 있다. -- 펑션을 수정해 보도록 하자 ! ALTER PARTITION FUNCTION MYRANGEPF3 () SPLIT RANGE (3000); GO 잘 수행되는 것을 확인 할 수 있을 것이다. 이제는 다양한 값을 넣어 보겠습니다. INSERT INTO PT_LOG (PIDX, DATA) VALUES (NULL, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (-99, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (101, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (1001, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (2001, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (3000, REPLICATE('A',6000)) GO 3 INSERT INTO PT_LOG (PIDX, DATA) VALUES (3001, REPLICATE('A',6000)) GO 3 SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 groupname groupid filecount --------- ------- --------- PT_LOG 72057594041663488 117575457 0 1 72057594041663488 0 PT_LOG 72057594041729024 117575457 0 2 72057594041729024 604 PT_LOG 72057594041794560 117575457 0 3 72057594041794560 605 PT_LOG 72057594041860096 117575457 0 4 72057594041860096 606 PT_LOG 72057594041925632 117575457 0 5 72057594041925632 607 PT_LOG 72057594041991168 117575457 0 6 72057594041991168 608 PT_LOG 72057594042056704 117575457 0 7 72057594042056704 609 PT_LOG 72057594042122240 117575457 0 8 72057594042122240 6 SELECT             $PARTITION.MYRANGEPF3(PIDX) AS 'PARTITION ID',             MIN(PIDX) AS 'MIN',             MAX(PIDX) AS 'MAX',             COUNT(*) AS 'ROWCNT' FROM             PT_LOG GROUP BY $PARTITION.MYRANGEPF3(PIDX) ORDER BY 1 결과 PARTITION ID MIN MAX ROWCNT ------------ ----------- ----------- ----------- 2 NULL NULL 604 3 -100 -99 605 4 1 2 606 5 100 101 607 6 1000 1001 608 7 2000 2001 608 8 3000 3001 6 -- 파티션 머지 ALTER PARTITION FUNCTION MYRANGEPF3 () MERGE RANGE (1000); SELECT             A.NAME, A.DATA_SPACE_ID, A.IS_DEFAULT,             B.DATA_SPACE_ID,             C.NAME FROM             SYS.DATA_SPACES A             INNER JOIN SYS.DESTINATION_DATA_SPACES B                         ON A.DATA_SPACE_ID = B.DATA_SPACE_ID             INNER JOIN SYS.PARTITION_SCHEMES C                         ON B.PARTITION_SCHEME_ID = C.DATA_SPACE_ID WHERE             C.NAME = 'PSMYSCHEME3' 결과 FG6 이 없어진 것을 확인 할 수 있다. FG1 2 0 2 PSMYSCHEME3 FG2 3 0 3 PSMYSCHEME3 FG3 4 0 4 PSMYSCHEME3 FG4 5 0 5 PSMYSCHEME3 FG5 6 0 6 PSMYSCHEME3 FG6 7 0 7 PSMYSCHEME3 FG7 8 0 8 PSMYSCHEME3 FG8 9 0 9 PSMYSCHEME3 EXEC SP_HELPFILEGROUP 결과 PRIMARY 1 1 FG1 2 1 FG2 3 1 FG3 4 1 FG4 5 1 FG5 6 1 FG6 7 1 FG7 8 1 FG8 9 1 물리적 파일 그룹은 그대로 이다. DBCC SHOWFILESTATS 결과 1 1 35 22 PDB C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PDB.mdf 3 2 80 1 PDB_DAT1 C:\FG1\PDB_DAT1.ndf 4 3 80 78 PDB_DAT2 C:\FG2\PDB_DAT2.ndf 5 4 80 78 PDB_DAT3 C:\FG3\PDB_DAT3.ndf 6 5 80 78 PDB_DAT4 C:\FG4\PDB_DAT4.ndf 7 6 160 78 PDB_DAT5 C:\FG5\PDB_DAT5.ndf 8 7 160 78 PDB_DAT6 G:\FG6\PDB_DAT6.ndf 9 8 80 79 PDB_DAT7 G:\FG7\PDB_DAT7.ndf 10 9 80 2 PDB_DAT8 G:\FG8\PDB_DAT8.ndf 정확하게 예전과 같이 분할된 것을 볼 수 있다. /*----------------------------------------------------------------------- PARTITIONED TABLE             PARTITIONED FUNCTION             PARTITIONED SCHEME             CREATE PARTITIONED TABLE             CHECK PARTITIONED INFORMATION             PARTITION MERGE             PARTITION SPLIT             PARTITION SWITCH             * PARTITION SWITCH 이제 파티션 이동하기를 해보겠습니다. 대용량 로그 파일이 있을때 DELETE 로 지우면서 세월아 내월아 기다릴 수 없으니 한방에 날릴 수 있도록 다른 파티션으로 이동해 보도록 하겠습니다. 그리고 스키마 정보만 바꾸어서 -------------------------------------------------------------------------*/ EXEC SP_HELPFILEGROUP GO ALTER DATABASE PDB ADD FILEGROUP FG3_AUX; GO -- 폴더를 미리 만들고 ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT3_AUX,FILENAME = 'C:\FG3_AUX\PDB_DAT3_AUX.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP FG3_AUX IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_AUX] (                  PIDX INT -- 파티션 기준값              , IDX INT IDENTITY(1,1)              , DATA VARCHAR(6000) NULL ) ON FG3_AUX GO DBCC SHOWFILESTATS ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ; GO 메시지 4939, 수준 16, 상태 1, 줄 1 ALTER TABLE SWITCH 문이 실패했습니다. 테이블 'PDB.dbo.PT_LOG_AUX'은(는) 파일 그룹 'FG3_AUX'에 있고 테이블 'PDB.dbo.PT_LOG'의 파티션 3은(는) 파일 그룹 'FG3'에 있습니다. -- 파일 그룹이 다른 테이블은 스위칭 하지 못한다. IF OBJECT_ID('DBO.PT_LOG_AUX') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_AUX] (                  PIDX INT -- 파티션 기준값             , IDX INT IDENTITY(1,1)             , DATA VARCHAR(6000) NULL ) ON FG3 GO ALTER TABLE PT_LOG SWITCH PARTITION 3 TO [PT_LOG_AUX] ; GO -- 성공적으로 스위칭 된다. SELECT * FROM PT_LOG WHERE PIDX = -100 -- 하나도 없다. SELECT * FROM SYS.PARTITION_RANGE_VALUES WHERE             FUNCTION_ID IN                          (                          SELECT FUNCTION_ID                          FROM SYS.PARTITION_FUNCTIONS                          WHERE NAME = ('MYRANGEPF3')                          ) SELECT * FROM PT_LOG_AUX -- 결과값이 나온다. DBCC SHOWFILESTATS -- 결국 같은 파일 그룹에서 정의만 다르게 해서 다른 테이블로 빼버리는 형태이다. 결론적으로 부하가 없다는 말입니다. ALTER TABLE PT_LOG_AUX SWITCH TO [PT_LOG] PARTITION 3 ; GO 결과 메시지 4982, 수준 16, 상태 1, 줄 2 ALTER TABLE SWITCH 문이 실패했습니다. 원본 테이블 'PDB.dbo.PT_LOG_AUX'의 CHECK 제약 조건에서 대상 테이블 'PDB.dbo.PT_LOG'의 파티션 3에서 정의한 범위가 허용하지 않는 값을 허용합니다. SELECT OBJECT_NAME(OBJECT_ID), * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PT_LOG') ORDER BY PARTITION_NUMBER, INDEX_ID; 결과 PT_LOG    72057594039042048    2073058421    0    3    72057594039042048    0 실제 파티션이 비어 있음에도 FUNCTION 제약 조건이 단일 테이블에는 적용되어 있지 않기 때문에 다시 스위칭 하는것을 허용하지 않는것 같습니다. 꽁수를 부려라... -- 꽁수 시작 CREATE PARTITION FUNCTION MYRANGEPF3_AUX (INT) AS RANGE RIGHT FOR VALUES ( NULL, -100, 1, 100, 1000); GO CREATE PARTITION SCHEME PSMYSCHEME3_AUX AS PARTITION MYRANGEPF3 ALL TO (FG3) IF OBJECT_ID('DBO.PT_LOG_SRC') IS NOT NULL DROP TABLE DBO.PT_LOG_AUX GO CREATE TABLE [dbo].[PT_LOG_SRC] (                  PIDX INT -- 파티션 기준값              , IDX INT IDENTITY(1,1)              , DATA VARCHAR(6000) NULL ) ON PSMYSCHEME3_AUX(PIDX) GO SET IDENTITY_INSERT PT_LOG_SRC ON INSERT INTO PT_LOG_SRC (PIDX, IDX, DATA)              SELECT PIDX, IDX, DATA FROM PT_LOG_AUX SET IDENTITY_INSERT PT_LOG_SRC OFF ALTER TABLE PT_LOG_SRC SWITCH PARTITION 3 TO [PT_LOG] PARTITION 3 ; GO 원래대로 끼워넣기 성공 파티션 스위치에 대한 감을 잡으셨기를 바랍니다. 다음 기사는 MSDN 에 올라온 글입니다. 관심있는 독자는 읽어보시길 바랍니다. SAN DISK 환경에서 일별로 어마어마한 로그가 쌓인다는 가정하에 2개의 자동화 프로시져를 이용하여 특정일 이후의 데이터를 삭제하는 멋진 로직을 설명 하고 있습니다. SQL2000 에서는 SP_RENAME 을 이용한 일별로그를 분리하곤 하지만 다양한 관리의 부담을 보이고 있으나, 오늘 배운 파티셔닝 기술과 SQL AGENT 로 두개의 프로시져만 번갈아 부르면 자동으로 현재일로부터 과거 몇일까지의 데이터를 저장하고 있는 테이블을 만들 수 있습니다. EXAMPLE             HOW TO IMPLEMENT AN AUTOMATIC SLIDING WINDOW IN A PARTITIONED TABLE ON SQL 2005             http://msdn2.microsoft.com/en-us/library/aa964122.aspx 끝으로 이번에 장가 갑니다. 멀리서나마 축하해 주시면 감사하겠습니다. 또한 김연욱 선배님(SQL MVP), 김대우님(MS DPE팀), 김종균님(TECHDATA), 송혁님(SQL MVP),장홍창님 그리고 많은 우리 SQL OFFLINE STUDY 사람들이 도와줘서 이글을 쓰게 되었습니다. 감사합니다. HTTP://SQLER.PE.KR에 오시면 석이를 만날 수 있습니다. ^^; 부족한 글 끝까지 읽어 주셔서 감사합니다. 원문 : http://blog.naver.com/backs55/100035813367