728x90
제 1강 : SQL Server 2005 .NET CLR 통합기능

이름 : 김종균
전) 벅스(주) 프로그래머, DBA
현) (주) 테크데이타 SQL Server Technical Support Engineer

개요

SQL 서버 2005에서는 .NET Framework2.0의 Common Language Runtime 기술을 이용하여 매우 향상된 데이터베이스 프로그램을 구현 할 수 있습니다. 이는 Microsoft Visual C#, Microsoft Visual Basic.NET, Microsoft Visual C++ 등의 CLR 언어를 통해서 저장프로시저, 사용자정의 함수, 트리거를 생성할 수 있을 뿐 아니라, 사용자 정의 데이터타입 및 집계의 생성을 가능하게 합니다.

TSQL의 그 능력의 한계

TSQL은 RDBMS만을 위한 언어이기 때문에 DML, DDL외의 프로그래밍 언어로서의 기능이 아주 미약합니다. TSQL에서 제공하는 함수는 제한적이며 (SQL2005에서 기능이 많이 추가되긴 하였지만), TSQL 로는 SQL 서버 외부 개체에 접근할 수 없습니다. 예를 들면 파일시스템 오브젝트를 통해서 파일의 내용을 읽고, 쓰는 등의 작업은 TSQL 로는 상상도 할 수 없습니다. 물론 SQL2000에서도 Extended Stored Procedure를 통해서 SQL외부 개체에 대한 핸들링을 할 수 있었으나, 상대적으로 어려운 C,C++프로그래밍에 대한 접근성과 in-process형태로 Sqlserver프로세스에서 실행되었기 때문에 자칫 확장 저장프로시져에 치명적인 코드결함이 있으면, SQL서버가 다운되는 경우도 있었습니다
하지만 CLR을 사용함으로써 SQL서버에서 기존에는 할 수 없었던 다양한 프로그래밍을 가능하게 합니다.

TSQL과 CLR의 몇 가지 장단점

  • TSQL은 인터프리터 방식이므로 느리다.
  • TSQL의 에러핸들링은 SQL2005에서 많이 향상되긴 했지만 비교적 좋지 않다. 
  • CLR은 컴파일 된 형태이므로 실행이 빠르다. 
  • CLR은 .NET프로그래밍 언어의 방대한 Class라이브러리를 사용하여 막강한 프로그래밍을 할 수 있다. 
  • TSQL은 대량의 데이터 액세스 혹은 데이터 조작 작업등에 유리하다. 
  • TSQL은 절차적 언어가 아니라 선언적 언어라서 문자열 조작, 데이터 포맷팅, 절차적 혹은 반복적인 로직에는 성능이 좋지 않다. 
  • CLR은 계산 집중적이거나 절차적,반복적 로직, 문자열 처리에서 좋은 성능을 나타낸다.

CLR 통합기능을 활용한 개발 절차

   1. SQL2005에서 CLR통합기능 옵션 활성화

   2. .NET Framework에서 지원하는 언어를 이용하여 클래스 작성

   3. 언어컴파일러를 통한 클래스 컴파일

   4. SQL서버에서 ASSEMBLY등록

   5. 등록한 ASSEMBLY 를 이용하여 사용자 개체 생성

.NET CLR통합기능 활성화

SQL2005는 보안상의 이유로 .NET CLR통합기능을 비활성화 해두고 있습니다. 따라서 CLR통합기능을 사용하기 위해서는 ‘Clr Enabled’ 라는 구성옵션을 활성화 해야 합니다. .NET CLR통합 기능을 사용할 수 있게 해주는 옵션을 활성화하는 방법은 두 가지가 입니다.

첫 번째 방법은, sp_configure 구성옵션 명령을 통해서 ‘clr enabled’ 라는 구성옵션을 ‘1’로 세팅해주는 것입니다. 이 옵션은 수정 즉시 적용되므로, SQL서버를 재 시작 할 필요가 없습니다.

SP_CONFIGURE 'clr enabled',1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE
GO
name minimum maximum config_value run_value
----------------------- ------------ ------------ ------------ ------------
clr enabled 0 1 1 1
lightweight pooling 0 1 0 0

유의해야 할 사항은 clr enabled옵션이 해제되게 되면 등록된 모든 ASSEMBLY가 unload되게 됩니다.
그리고 clr enabled옵션과 lightweight pooling옵션은 병행해서 사용이 불가능합니다.

두 번째 방법은, SQL서버 노출영역 구성에서 기능에 대한 노출영역을 구성하는 것 입니다. 아래 그림과 같이 clr enabled 옵션을 체크 해주면 됩니다.

SQL서버 노출영역 구성

예제1 – 사용자 정의 스칼라 함수 구현

그럼, CLR을 통한 간단한 사용자 정의 함수를 생성하고 SQL서버에 이식시켜 사용해보는 예제를 구현해보면서, CRL을 이용한 SQL프로그래밍에 대한 감을 잡아보겠습니다.

예제로 email주소를 정합성을 검사해주는 사용자함수 클래스를 C#으로 구현해 보겠습니다. 소스코드 작성을 위해서 메모장을 이용하셔도 무방합니다만.. 가능하시면 Visual Studio를 이용하시면 보다 더 훌륭한 개발 인터페이스를 통해서 더 빠르고 쉽게 코딩이 가능합니다. 여기서, c#코드에 대한 설명은 하지 않습니다.

개발 도구를 사용하여 아래 c#코드를 작성합니다.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static bool emailCheck(string email)
{
string pattern=@"^[a-z][a-z|0-9|]*([_][a-z|0-9]+)*([.][a-z|" + @"0-9]+([_][a-z|0-9]+)*)?@[a-z][a-z|0-9|]*＼.([a-z]" + @"[a-z|0-9]*(＼.[a-z][a-z|0-9]*)?)$";
Match match = Regex.Match(email, pattern, RegexOptions.IgnoreCase);

if(match.Success) return true;
else return false;
}
};

[소스1] emailCheck.cs

소스코드 작성이 완료되면 c#컴파일러를 통해 cs파일을 dll형태로 컴파일 합니다.

csc.exe /target:library /out:emailcheck.dll emailcheck.cs

참고. csc컴파일러의 위치는 .NetFramework이 설치된 폴더에 있습니다.
C:＼WINDOWS＼Microsoft.NET＼Framework＼v2.0.50727

자 이렇게 해서 emailcheck.dll 이라는 어셈블리 파일을 생성했습니다. 이 파일을 관리하시는 적절한 폴더에 복사 하십시요. 이 파일은 추후에 SQL2005의 ASSEMBLY로 등록하게 되면, 삭제해도 무관합니다.

다음 단계로 CLR사용자 함수 ASSEMBLY를 SQL서버에 ASSEMBLY로 등록합니다.

CREATE ASSEMBLY SQLER_UDF
FROM 'C:＼TEMP＼emailcheck.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE ASSEMBLY는 이미 .dll파일로 미리 컴파일된 ASSEMBLY를 SQL2005 내부에서 사용할 수 있도록 업로드 혹은 바인딩 하는 작업입니다.


다음 단계로 ASSEMBLY를 매핑하여 사용자 정의 함수를 생성합니다.

CREATE FUNCTION UDF_VALIDATEEMAIL (@email as nvarchar (100))
RETURNS bit
AS EXTERNAL NAME SQLER_UDF.MyFunctions.emailCheck
GO

사용자 개체 생성시EXTERNAL NAME 지정규칙은 다음과 같습니다.
[SQL Server Assembly Name].[Fully Qualified Path to Class].[Static Method Name]

자. 그럼 이제 생성한 스칼라 사용자 정의 함수를 사용해보겠습니다.

SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1@naver.com')     --1
SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1@naver')           --0
SELECT DBO.UDF_VALIDATEEMAIL ('bellvirus1.com')               --0
GO

상기 절차로 .NET 프로그램 코드를 통한 UDF생성을 간단하게 살펴보았습니다.

예제2 – 스칼라 함수, 저장프로시져 및 트리거 구현

다음 예제로 테이블의 전체 행 수를 반환하는 스칼라 함수와, 레코드 셋을 가져오는 저장프로시져, 데이터 삭제에 대해 로그를 기록하는 트리거를 Visual Basic .NET 으로 구현 해보겠습니다.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class MySQLClass
<SqlFunction(DataAccess:=DataAccessKind.Read)>_
Public Shared Function ReturnOrderCount() As Integer
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'ORDERCNT' FROM SALES.SALESORDERDETAIL", conn)
Return CType(cmd.ExecuteScalar(), Integer)
End Using
End Function


Public Shared Sub GetSalesOrderDetail()
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'ORDERCNT' FROM SALES.SALESORDERDETAIL", conn)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub


<Microsoft.SqlServer.Server.SqlTrigger()>_
Public Shared Sub writeDeleteLog()
Dim oTc As SqlTriggerContext
oTc = SqlContext.TriggerContext
If (oTc.TriggerAction = TriggerAction.Delete) Then
Dim oFi As New System.IO.StreamWriter("c:＼temp＼log.txt", True)
oFi.Write("something deleted !")
oFi.Close()
End If
End Sub

End Class

[소스2] MySQLClass.vb

코드 작성이 완료되면 컴파일러를 통해 DLL형태의 파일을 생성합니다.

vbc /target:library /out:c:＼temp＼MySQLClass.dll c:＼temp＼MySQLClass.vb

그런 다음에 SQL서버에서 ASSEMBLY로 등록합니다.
이번 예제에서는 ASSEMBLY생성시에 PERMISSION_SET옵션을 EXTERNAL_ACCESS를 지정하였는데, 그 이유는 writeDeleteLog 메서드가 SQL 외부 개체에 액세스 할 필요가 있기 때문입니다.

여기서 잠깐 PERMISSION_SET옵션에 대해 알아보고 넘어 가겠습니다.

  • SAFE (기본옵션) : 등록하게 되는 어셈블리에서 실행한 코드는 파일, 네트워크, 환경변수 또는 레지스트리와 같은 외부 시스템 리소스에 액세스할 수 없습니다.
  • EXTERNAL_ACCESS : 파일, 네트워크, 환경 변수 또는 레지스트리와 같은 외부 시스템 리소스에 액세스할 수 있습니다.
  • UNSAFE : SQL Server 인스턴스의 내부 리소스와 외부 리소스 모두에 제한 없이 액세스할 수 있습니다.
CREATE ASSEMBLY SQLER_CLASS
FROM 'C:＼TEMP＼MYSQLClass.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

생성 명령을 실행하였으나 아래와 같은 오류가 발생합니다.


메시지10327, 수준14, 상태1, 줄1 어셈블리'MySQLClass'에PERMISSION_SET = EXTERNAL_ACCESS에대한권한이없으므로어셈블리'MySQLClass'에대한CREATE ASSEMBLY가실패했습니다. 어셈블리는DBO(데이터베이스소유자)에게EXTERNAL ACCESS ASSEMBLY 권한이있고데이터베이스에TRUSTWORTHY 데이터베이스속성이있는경우또는어셈블리가현재인증서로서명되어있거나EXTERNAL ACCESS ASSEMBLY 권한이있는관련로그인을소유한비대칭키로서명되어있는경우에권한이부여됩니다.

사용자 정의함수나 저장프로시져에서 외부 개체에 접근하려면 Database의 TRUSTWORTHY 옵션을 활성화 해주어야 합니다. 아래 명령을 실행하여 외부 개체 접근을 허용해줍니다. 그런 다음 다시 ASSEMBLY를 생성해줍니다.

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY SQLER_CLASS
FROM 'C:＼TEMP＼MYSQLClass.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

SQLER_CLASS 어셈블리를 활용하여 사용자 정의 스칼라 함수를 생성합니다.

CREATE FUNCTION UDF_getOrderCount()
RETURNS INT
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.ReturnOrderCount
GO

생성한 함수를 실행하여 보겠습니다.

SELECT DBO.UDF_getOrderCount() AS orderCount
GO
orderCount
-----------
121317

SQLER_CLASS 어셈블리를 활용하여 저장프로시져를 생성합니다.

CREATE PROC UP_getSalesOrderDetail
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.GetSalesOrderDetail
GO

프로시져를 실행하여 보겠습니다.

EXEC UP_getSalesOrderDetail
GO

43659  1      4911-403C-98 1      776    1      2024.994
43659  2      4911-403C-98 3      777    1      2024.994
43659  3      4911-403C-98 1      778    1      2024.994
43659  4      4911-403C-98 1      771    1      2039.994
43659  5      4911-403C-98 1      772    1      2039.994
43659  6      4911-403C-98 2      773    1      2039.994
43659  7      4911-403C-98 1      774    1      2039.994
43659  8      4911-403C-98 3      714    1      28.8404
43659  9      4911-403C-98 1      716    1      28.8404

결과를 잘 반환해 줍니다.

다음으로 트리거를 생성하여 보겠습니다.
이 예제를 위해서 간단한 샘플 테이블을 생성하겠습니다.

CREATE TABLE TBL_TEST (idx int, name varchar(20))
GO
INSERT INTO TBL_TEST VALUES (1,'KIM')
INSERT INTO TBL_TEST VALUES (1,'LEE')
INSERT INTO TBL_TEST VALUES (1,'PARK')
GO

TBL_TEST테이블에 대해서 DELETE명령에 대한 트리거 생성합니다.

CREATE TRIGGER TRG_DELETE
ON TBL_TEST
FOR DELETE
AS EXTERNAL NAME SQLER_CLASS.MySQLClass.writeDeleteLog
GO

트리거가 잘 생성되었고, 실제로 데이터를 삭제하여 트리거가 정상적으로 동작하는지 살펴보겠습니다. 앞서 작성한 writeDeleteLog 메서드는 삭제 트리거가 발생하면 메모장에서 log.txt파일에 Something Deleted! 를 기록하게 됩니다.

DELETE FROM TBL_TEST WHERE idx = 1
GO

메모장에서 log.txt파일 확인

예제3. 사용자 정의 집계함수(Aggegation)의 구현

이번 예제에서는 각 행들의 특정 문자열 컬럼을 합해주는 함수를 만들어 보겠습니다.
이 함수는 숫자형 집계함수인 SUM()과 유사합니다 ^^
이번 예제는 온라인 도움말에서 제공하는 샘플을 사용합니다.

using System;
using System.IO;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace", Target =
"Microsoft.Samples.SqlServer")]

namespace Microsoft.Samples.SqlServer
{
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,       //use clr
serialization to serialize the intermediate result
IsInvariantToNulls = true,       //optimizer property
IsInvariantToDuplicates = false,       //optimizer property
IsInvariantToOrder = false,       //optimizer property
MaxByteSize = 8000)       //maximum size in bytes of persisted value
]
public classConcatenate : Microsoft.SqlServer.Server.IBinarySerialize
{
/// <summary>
///The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
intermediateResult = new StringBuilder();
}

       
/// <summary>
///Accumulate the next value, nop if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{

if (value.IsNull)
{
return;
}
intermediateResult.Append(value.Value).Append(',');
}

/// <summary>
///Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (intermediateResult != null && intermediateResult.Length > 0)
output = intermediateResult.ToString(0, intermediateResult.Length - 1);
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(intermediateResult.ToString());
}
}
}
[소스3] concat.cs

소스코드 작성이 완료되면 c#컴파일러를 통해 cs파일을 dll형태로 컴파일 합니다.

csc.exe /target:library /out:concat.dll concat.cs

concate.dll파일을 ASSEMBLY로 등록하고, 집계함수를 생성합니다.

CREATE ASSEMBLY STRINGHANDLES3
FROM 'C:＼TEMP＼concat.dll'

CREATE AGGREGATE [dbo].[Concatenate](@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME
STRINGHANDLES3.[Microsoft.Samples.SqlServer.Concatenate];
GO

테스트를 위한 샘플 테이블을 생성하고, 데이터를 몇 건 삽입합니다.

CREATE TABLETEST (IDX INT, NAME VARCHAR(10))
GO

INSERT INTO TEST VALUES (1,'AAA')
INSERT INTO TEST VALUES (2,'BBB')
INSERT INTO TEST VALUES (3,'CCC')
INSERT INTO TEST VALUES (4,'DDD')
INSERT INTO TEST VALUES (5,'EEE')
INSERT INTO TEST VALUES (6,'KKK')
GO

집계함수를 활용하여 쿼리를 실행합니다.

SELECT SUM(IDX)AS SUMIDX, dbo.concatenate(name) AS CONCATNAMES
FROM TEST
GO

SUMIDX      CONCATNAMES
---------  ----------------------------------
21          aaa,bbb,ccc,ddd,eee,kkk

(1개행적용됨)

예제4. 사용자 정의 테이블 값 함수의 구현

이번에는 특정 구분자를 이용하여 요소를 분리해주는 SPLIT함수를 구현해 보겠습니다.

using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public sealed class StringSplitter
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable Split(SqlString argument1, Char []
argument2)
{
string value;

if (argument1.IsNull)
value = "";
else
value = argument1.Value;

return value.Split(argument2);
}

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1021:AvoidOutParameters")]
public static void FillRow(Object obj, out string stringElement)
{
stringElement = (string)obj;
}
}
[소스4] Split.cs

소스를 컴파일 합니다.

csc.exe /target:library /out:split.dll split.cs

어셈블리를 등록합니다.
CREATE ASSEMBLY STRINGHANDLES
FROM 'C:＼TEMP＼split.dll'
GO

사용자 정의 함수를 생성합니다.

CREATE FUNCTION DBO.UDF_SPILT (@INPUT NVARCHAR (4000), @DELIMETER
NVARCHAR(10))
RETURNS TABLE (ELEMENT NVARCHAR (MAX))
AS EXTERNAL NAME STINGHANDLE.[ StringSplitter]. Split;
GO

실행테스트

SELECT * FROM DBO.UDF_SPILT 'MSSQL/EXCHANGE/OFFICE/VISTA' , >'/');
GO

element
-----------------
MSSQL
EXCHANGE
OFFICE
VISTA
(4개행적용됨)

마치며..

간단한 예제 중심으로 SQL서버2005 .NET CLR통합 기능에 대해서 살펴보았습니다. 이처럼, TSQL만으로는 불가능 했던 프로그래밍을 가능하게 TSQL에 날개를 달아주는 것이 .NET CLR입니다. 언제나 그렇듯이, 좋은 기능을 제대로 사용하지 않거나, 남용하면 역효과를 가져 올 수 있습니다. TSQL만으로 구현 가능한 것들은 TSQL로 구현하는게 바람직 할 것입니다. 주어진 상황에 대해서 TSQL과 CLR 중 어떠한 것을 적용해야 효율적인가 에 대한 많은 고민과 테스트 과정을 통해서 올바른 판단을 해야 할 것입니다.

감사합니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

쿼리 테크닉 - 2부  (0) 2008.05.07
SQL Sever에 별칭(Alias)을 달아보자  (0) 2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(1)  (0) 2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(2)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
728x90
제 1강 : 파티션드 테이블

이름: 김민석
편지: MINSOUK@HOTMAIL.COM
NHN WWW.NHNCORP.COM DBA

테이블을 만들고 데이터를 적제하고 어떻게하면 할당된 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에 오시면 석이를 만날 수 있습니다. ^^;
부족한 글 끝까지 읽어 주셔서 감사합니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

SQL Sever에 별칭(Alias)을 달아보자  (0) 2008.05.07
김종균 : SQL Server 2005 .NET CLR 통합기능  (0) 2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(2)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
728x90
제 2강 : 파티션드 테이블 인덱스

이름: 김민석
편지: MINSOUK@HOTMAIL.COM
NHN WWW.NHNCORP.COM DBA

전편에 이은 파티션드 인덱스에 대한 내용을 기술해 보고자 합니다. 파티션드 테이블은 로그성 데이터같이 시간순서로 계속 쌓이면서 일정 기간이 지나면 버려지는 데이터에 대해서 적합한 솔루션 입니다. 대표적인 예가 전편에 소개한 슬라이딩 윈도우입니다.

그러나 성능을 높이기 위한 OLTP 에서 파티션드 테이블을 고려하기 위해서는 많은 고민이 필요할 것 같습니다.

아래 TEST 는 다음 버전에서 수행 되었습니다.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)

1. 데이터 베이스가 있으면 삭제합니다.

IF EXISTS
       (
             SELECT *
             FROM MASTER. DBO. SYSDATABASES
             WHERE NAME = 'PDB'
       )
       BEGIN
             DECLARE @KILL_LIST VARCHAR (MAX )
             SET @KILL_LIST = ''

             SELECT @KILL_LIST =
                    (
                    SELECT 'KILL ' + CAST (SPID AS VARCHAR (10))+ '; '
                    FROM MASTER. DBO. SYSPROCESSES
                    WHERE DBID = DB_ID ('PDB' )
                    FOR XML PATH ('' )
                    )
                    -- PRINT @KILL_LIST
                    EXEC (@KILL_LIST)

                    DROP DATABASE PDB
       END
GO

2. 파일그룹을 생성합니다.

FG1-8 파티션드 테이블을 테스트 할 목적의 파일그룹이고, 나머지 두개는 일반 테이블을 만들어 테스트 할 목적입니다.

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;

ALTER DATABASE PDB
ADD FILEGROUP FG_DATA;
ALTER DATABASE PDB
ADD FILEGROUP FG_INDEX;

3. 파일그룹에 파일을 할당 합니다.

ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT1, FILENAME = 'C:\FG1\PDB_DAT1.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 = 'C:\FG6\PDB_DAT6.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG6
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT7, FILENAME = 'C:\FG7\PDB_DAT7.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG7
ALTER DATABASE PDB ADD FILE (NAME = PDB_DAT8, FILENAME = 'C:\FG8\PDB_DAT8.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG8

ALTER DATABASE PDB ADD FILE (NAME = PDB_DATA, FILENAME = 'C:\FG8\PDB_DATA.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG_DATA
ALTER DATABASE PDB ADD FILE (NAME = PDB_INDEX, FILENAME = 'C:\FG8\PDB_INDEX.NDF' , SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP FG_INDEX

-- PDB 실제파일이어떻게구성되어있는지확인합니다 .
SELECT * FROM MASTER. DBO. SYSALTFILES WHERE DBID = DB_ID ('PDB' )

4. 파티션 하지 않을 테이블과 파티션 할 테이블을 만들고, 파티션 스위칭을 위해 같은 스키마 구조의 테이블을 추가로 생성 합니다.

-- 파티션하지않고성능테스트할테이블을 FG_DATA 에할당합니다 .
CREATE TABLE [dbo]. [tblx](
       [productid] [int] NULL,
       [ReferenceOrderID] [int] NOT NULL,
       [ReferenceOrderLineID] [int] NOT NULL,
       [TransactionDate] [datetime] NOT NULL,
       [TransactionType] [nchar](1) NOT NULL,
       [Quantity] [int] NOT NULL,
       [ActualCost] [money] NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON FG_DATA

-- 파티션할테이블생성합니다 .
CREATE TABLE [dbo]. [tbly](
       [productid] [int] NULL,
       [ReferenceOrderID] [int] NOT NULL,
       [ReferenceOrderLineID] [int] NOT NULL,
       [TransactionDate] [datetime] NOT NULL,
       [TransactionType] [nchar](1) NOT NULL,
       [Quantity] [int] NOT NULL,
       [ActualCost] [money] NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON PSMYSCHEME3([productid])

-- 파티션스위칭을위해같은구조의스키마를가지고있는테이블을만들어둡니다 .
CREATE TABLE [dbo]. [tblZ](
       [productid] [int] NULL,
       [ReferenceOrderID] [int] NOT NULL,
       [ReferenceOrderLineID] [int] NOT NULL,
       [TransactionDate] [datetime] NOT NULL,
       [TransactionType] [nchar](1) NOT NULL,
       [Quantity] [int] NOT NULL,
       [ActualCost] [money] NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON PSMYSCHEME3([productid])

5. 파티션 펑션과 스키마를 만들어 줍니다.

-- 파티션펑션만들기
CREATE PARTITION FUNCTION MYRANGEPF3 (INT)
AS RANGE RIGHT FOR VALUES (0, 100, 200, 300, 400, 500, 600);
GO

CREATE PARTITION FUNCTION MYRANGEPF3_COPY (INT)
AS RANGE RIGHT FOR VALUES (0, 100, 200, 300, 400, 500, 600);
GO

-- 파티션스키마 만들기

CREATE PARTITION SCHEME PSMYSCHEME3
AS PARTITION MYRANGEPF3
TO (FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8)
GO

IF OBJECT_ID ('DBO.TBL_TEMP') IS NOT NULL
DROP TABLE TBL_TEMP
IF OBJECT_ID ('DBO.TBLX') IS NOT NULL
DROP TABLE TBLX
IF OBJECT_ID ('DBO.TBLY') IS NOT NULL
DROP TABLE TBLY
IF OBJECT_ID ('DBO.TBLZ') IS NOT NULL
DROP TABLE TBLZ
GO

6. 파티션 테스트를 위한 데이터를 카피해 옵니다.

SELECT A.* INTO PDB. DBO. TBL_TEMP
FROM
       (
       SELECT
             CASE
               WHEN PRODUCTID > 600 THEN PRODUCTID - 600
               ELSE PRODUCTID END PRODUCTID
             , REFERENCEORDERID
             , REFERENCEORDERLINEID, TRANSACTIONDATE, TRANSACTIONTYPE
             , QUANTITY, ACTUALCOST, MODIFIEDDATE
       FROM
             ADVENTUREWORKS . PRODUCTION. TRANSACTIONHISTORYARCHIVE
       ) A
GO

7. tblx 와 tbly 라는 테이블에 데이터를 삽입 합니다.

INSERT INTO TBLX
       SELECT * FROM TBL_TEMP
INSERT INTO TBLY
       SELECT * FROM TBL_TEMP

8. 위 절차를 수행한 후 데이터가 어떠한 파일 그룹에 위치해 있는지 확인 합니다.

DBCC SHOWFILESTATS

/*-----------------------------------------------------------------------
TBL_TEMP 는 파일그룹을 지정하지 않았으므로 PDB_PRIM 에 생성
TBLX 는 PDB_DATA 에
TBLY 는 지정한 파일 그룹에 나누어져서

1      1      160    93     PDB_PRIM     C:\FG0\FG0_PRI.MDF        
-- TBL_TEMP
3      2      80     1      PDB_DAT1     C:\FG1\PDB_DAT1.NDF        --
4      3      80     2      PDB_DAT2     C:\FG2\PDB_DAT2.NDF          |
5      4      80     33     PDB_DAT3     C:\FG3\PDB_DAT3.NDF          |
6      5      80     22     PDB_DAT4     C:\FG4\PDB_DAT4.NDF          |
7      6      80     15     PDB_DAT5     C:\FG5\PDB_DAT5.NDF          |
8      7      80     4      PDB_DAT6     C:\FG6\PDB_DAT6.NDF          |
9      8      80     8      PDB_DAT7     C:\FG7\PDB_DAT7.NDF          |
10     9      80     1      PDB_DAT8     C:\FG8\PDB_DAT8.NDF        --
-- TBLY, (TBLZ 영역정의 )
11     10     80     73     PDB_DATA     C:\FG8\PDB_DATA.NDF       
-- TBLX
12     11     80     1      PDB_INDEX    C:\FG8\PDB_INDEX.NDF
------------------------------------------------------------------------*/

9. SYSPARTITION META 정보확인

SELECT OBJECT_NAME (OBJECT_ID ), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID ('tbly' )
ORDER BY PARTITION_NUMBER, INDEX_ID;

SELECT OBJECT_NAME (OBJECT_ID ), *
FROM SYS.PARTITIONS
WHERE OBJECT_ID = OBJECT_ID ('tblZ' )
ORDER BY PARTITION_NUMBER, INDEX_ID;

10. 인덱스를 만들어 봅니다.

그러나 실수 할 수 있으니 주의 하시길 바랍니다. SQL Server 에서 Clustered index 라는 것은 데이터 구조 입니다. 이러한 Clustered index 가 없는 데이터 구조는 HEAP 이라는 데이터 구조를 가지게 되고 [Fileid, Pageid, slotid] 로 데이터에 접근하게 됩니다. 그러므로 인덱스와 파일 구조를 분리하기 위해서 Clustered index 를 index 파일 그룹에 위치시킨다면 모든 데이터가 인덱스 파일 그룹으로 옮겨가게 되는 현상이 생기게 됩니다. 반드시 클러스터 인덱스는 데이터 파일 그룹에 형성 하시길 바랍니다.

이제 파티션 되지 않은 tblx 에 Clustered index 를 만들어 보도록 하겠습니다.
만들고 난 후 DBCC SHOWFILESTATS 명령어로 확인해 볼 수 있습니다 .

CREATE CLUSTERED INDEX CL_TBLX
       ON TBLX(PRODUCTID, TRANSACTIONDATE)
       ON FG_DATA

11. 파티션드 테이블에도 Clustered index 를 만들어 줍니다.

IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'CL_TBLY' )
DROP INDEX TBLY. CL_TBLY
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLZ' ) AND NAME = 'CL_TBLZ' )
DROP INDEX TBLZ. CL_TBLZ
GO

-- 파티션 키로 분배되어 들어간 데이터 그대로 CLUSTERED INDEX 생성
CREATE CLUSTERED INDEX CL_TBLY
       ON TBLY(PRODUCTID, TRANSACTIONDATE)
       ON PSMYSCHEME3(PRODUCTID);

-- 동일인덱스 생성
CREATE CLUSTERED INDEX CL_TBLZ
       ON TBLZ(PRODUCTID, TRANSACTIONDATE)
       ON PSMYSCHEME3 (PRODUCTID);
GO

IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01')
DROP INDEX TBLY. NC_TBLY_01
GO
-- LOCAL NONCLUSTERED INDEX 생성
CREATE NONCLUSTERED INDEX NC_TBLY_01
       ON TBLY(TRANSACTIONDATE)
GO

12. 잠깐 용어 정리를 하도록 하겠습니다. 아직까지 SQL Server 2005 에서 관련 용어를 정의 하지 않았으므로, 먼저 이러한 기술을 도입한 다른 DBMS의 용어를 그대로 차용해 이 강좌에서 설명하도록 하겠습니다.

* LOCAL INDEX   : CØ 당 파티션에 해당 영역의 인덱스만 존재 하는 경우

* GLOBAL INDEX : 하나의 인데스 파일그룹이 모든 파티션을 통합한 인덱스를 가지는 경우

두 형태의 인덱스를 어떠한 상황에서 사용해야 하는가에 대해서 간단하게 설명하자면 LOCAL INDEX 의 경우 CLUSTERED INDEX 이든 NONCLUSTERED INDEX 이든 해당 파티션만의 억세스로 모든 억세스가 끝나는 경우이고 GLOBAL INDEX 의 경우는 찾고자 하는 데이터가 어떤 파티션에 있을지 모르는 상황에서의 인덱스를 정의 하시면 됩니다.그러나 LOCAL INDEX 와 GLOBAL INDEX 의 여부에 따라서 파티션 스위칭의 제한 사항이 있으므로 각자의 상황에 따라 적절한 인덱스를 사용하시면 됩니다.

13. 다음은 CLUSTERED INDEX 와 NONCLUSTERED INDEX 가 있는 상황에서의 물리적 상황을 조회하는 DMV 입니다.

SELECT
OBJECT_NAME (OBJECT_ID)
, INDEX_TYPE_DESC
, ALLOC_UNIT_TYPE_DESC -- , *
FROM
sys.dm_db_index_physical_stats
(db_id ( ), object_id ('tblY'), NULL, NULL, NULL);
GO

/* ---------------------------------------------------------------------------
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   CLUSTERED INDEX     IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA
tbly   NONCLUSTERED INDEX  IN_ROW_DATA

LOCAL NONCLUSTERED INDEX 가 생성 되었음
--------------------------------------------------------------------------- */

위의 결과를 출력해보면 CLUSTERED INDEX 의 파티션 된 개수와 NONCLUSTERED INDEX 의 상황을 볼 수 있습니다. 실제로 (결과값에서) 파티션드 키를 조건절에 넣지 못하는 상황에서 NONCLUSTERED INDEX 로 조회하는 상황이 발생한다면, NONCLUSTERED INDEX 모든 파티션에 접근해야 한다는 겁니다. 다음 테스트를 통해서 확인해 보도록 하겠습니다.

14. TEST

1. 원본테이블에서 2 개의 파티션에만 데이터가 있도록 가공

DELETE Y
--SELECT *
FROM TBLY Y
WHERE
       TRANSACTIONDATE = '20030708'
       AND PRODUCTID <> 106
       AND PRODUCTID <> 533
GO

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TBLY WHERE   transactionDate = '20030708'
GO
SET SHOWPLAN_TEXT OFF
GO
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1008]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
       |--Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007]))
        |         |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
       |         |--Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1007]))
       |--Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))

** Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)))) 부분을주의깊게보길바랍니다 . 모든 파티션에 접근하게 됩니다 .

IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01' )
DROP INDEX TBLY. NC_TBLY_01
GO

CREATE NONCLUSTERED INDEX NC_TBLY_01
       ON TBLY(TRANSACTIONDATE)
       ON FG_INDEX
GO

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM TBLY WHERE   transactionDate = '20030708'
GO
SET SHOWPLAN_TEXT OFF
GO

/*-------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1007]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
       |--Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
       |    |--Index Seek(OBJECT :([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))

** 이번에는 모든 파일그룹과 조인 하는 실행계획이 빠진 것을 확인 할 수 있습니다.

위 테스트는 파티션드 인덱스에서 매우 중요한 사항이므로 반드시 알고 있어야 할 사항입니다.

그렇다고 하나의 인덱스 파티션(GLOBAL INDEX)으로 만들 경우 파티션 스위칭시 하나의 인덱스 파일그룹으로 되어 있으면 해당하는 인덱스를 DISABLE 해두고 스위칭 후 다시 REBUILD 해야 합니다. REBUILD가 완료될 때 까지 해당하는 인덱스를 사용할 수 없다는 점을 알고 있어야 합니다.

15. TEST

ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO
--Msg 7733, Level 16, State 4, Line 1
--'ALTER TABLE SWITCH' statement failed. The table 'PDB.dbo.TBLY' is partitioned while index 'NC_TBLY_01' is not partitioned.
-- NC_TBLY_01 이파티셔닝되지않았으므로스위칭할수없다는에러메시지입니다 .

ALTER INDEX [NC_TBLY_01] ON [dbo]. [tbly] DISABLE
GO

-- 성공적으로실행완료
ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO

-- DISABLE 된 [NC_TBLY_01] 를다시리빌드함
ALTER INDEX [NC_TBLY_01] ON [DBO]. [TBLY] REBUILD
GO

-- NONCLUSTERED INDEX 를 LOCAL INDEX 로구성
IF EXISTS (SELECT * FROM SYSINDEXES WHERE ID = OBJECT_ID ('DBO.TBLY') AND NAME = 'NC_TBLY_01')
DROP INDEX TBLY. NC_TBLY_01
GO

CREATE NONCLUSTERED INDEX NC_TBLY_01
       ON TBLY(TRANSACTIONDATE)
GO

ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO
--Msg 4947, Level 16, State 1, Line 1
--ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PDB.dbo.TBLZ' for the index 'NC_TBLY_01' in target table 'PDB.dbo.TBLY' .
-- 소스파티션에없는인덱스구조가타겟에있으므로옮길수없다
CREATE NONCLUSTERED INDEX NC_TBLZ_01
       ON TBLZ(TRANSACTIONDATE)
GO

-- 스위칭성공
ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO

-- 이제는모두 LOCAL INDEX 이므로자유롭게타겟이비어있으면스위칭됩니다 .
ALTER TABLE TBLY SWITCH PARTITION 3 TO TBLZ PARTITION 3 ;
GO

ALTER TABLE TBLZ SWITCH PARTITION 3 TO TBLY PARTITION 3 ;
GO

  ** 인덱스재구성
특정 파티션 테이블을 온라인으로 리빌드하는 것은 문법상 불가능 합니다 .
이게 되면 참 좋을 것 같은데 , 아쉽습니다 .

-- 단일파일그룹테이블온라인리인덱스
ALTER INDEX ALL ON DBO. TBLX
REBUILD WITH (ONLINE = ON);

-- 특정파티션인덱스리빌드
ALTER INDEX CL_TBLY ON DBO. TBLY
REBUILD Partition = 3;

-- 파티션테이블을가지는테이블의온라인리인덱스
-- Online index operations are available only in SQL Server 2005 Enterprise Edition.

-- 파티션된테이블의모든인덱스온라인리빌드
ALTER INDEX ALL ON DBO. TBLY
REBUILD WITH (ONLINE = ON);

-- 클러스터인덱스만리빌드 (UNIQUE 일때는클러스터인덱스만리빌딩됩니다 .)
-- UNIQUE 제약조건이빠지면 NONCLUSTERED INDEX 도클러스터인덱스가리빌드될때같이됩니다 .
ALTER INDEX CL_TBLY ON DBO. TBLY
REBUILD WITH (ONLINE = ON);

-- 특정 NONCLUSTERED INDEX REBUILD
ALTER INDEX NC_TBLY_01 ON DBO. TBLY
REBUILD WITH (ONLINE = ON);

-- 파티션테이블의특정파티션만리빌드하면서 CPU 동시성을 1 로제한
ALTER INDEX NC_TBLY_01 ON DBO. TBLY
REBUILD Partition = 3 WITH (MAXDOP = 1)

마지막으로 파티션드 테이블을 온라인 리빌딩 할 수 있었고, 데이터가 많아지면 많이 느려지지만 무중단 시스템에서 점검 시간없이 인덱스 리빌딩이 가능하다는 결론을 얻을 수 있습니다. 실제 서비스 시스템에서 이것을 적용 할 지 못할지는 해당 DBA 가 업무특성에 따라 잘 결정해야 합니다.

다음은 SQL MVP 이신 김연욱 선배님이 테스트 해주신 자료 입니다. 참고 하시길 바랍니다.

et statistics profile on
set statistics io on
GO
SELECT * FROM TBLX WHERE   transactionDate = '20030708'
테이블 'tblx' . 검색수 1, 논리적읽기수 369
117    1      SELECT * FROM [TBLX] WHERE [transactionDate]= @1
117    1        | --Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [PDB].[dbo].[tblx].[productid], [PDB].[dbo].[tblx].[TransactionDate], [Expr1005]) WITH UNORDERED PREFETCH)
117    1             | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD)
117    117           | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=[PDB].[dbo].[tblx].[productid] AND [PDB].[dbo].[tblx].[TransactionDate]=[PDB].[dbo].[tblx].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

SELECT * FROM TBLY WHERE   transactionDate = '20030708'
테이블 'tbly' . 검색수 8, 논리적읽기수 289
117    1      SELECT * FROM [TBLY] WHERE [transactionDate]= @1
117    1        | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006], [Uniq1002], [PDB].[dbo].[tbly].[productid], [PDB].[dbo].[tbly].[TransactionDate], [Expr1008]) PARTITION ID:([PtnIds1006]) WITH UNORDERED PREFETCH)
0      0             | --Compute Scalar(DEFINE:([PtnIds1006]=RangePartitionNew([PDB].[dbo].[tbly].[productid],(1),(0),(100),(200),(300),(400),(500),(600))))
117    1             |     | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1007]) PARTITION ID:([PtnIds1007]))
8      1             |          | --Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
117    8             |          | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1007]))
117    117           | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=[PDB].[dbo].[tbly].[productid] AND [PDB].[dbo].[tbly].[TransactionDate]=[PDB].[dbo].[tbly].[TransactionDate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD PARTITION ID:([PtnIds1006]))


SELECT count (*) FROM TBLX WHERE   transactionDate = '20030708'
1      1      SELECT COUNT (*) FROM [TBLX] WHERE [transactionDate]= @1
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))
1      1             | --Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
117    1                  | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 2

SELECT count (*) FROM TBLY WHERE   transactionDate = '20030708'
1      1      SELECT COUNT (*) FROM [TBLY] WHERE [transactionDate]= @1
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
1      1             | --Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
117    1                   | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1009]) PARTITION ID:([PtnIds1009]))
8      1                       | --Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8))))
117    8                       | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1009]))
테이블 'tbly' . 검색수 8, 논리적읽기수 12

SELECT * FROM TBLX WHERE   productid = '111'
테이블 'tblx' . 검색수 1, 논리적읽기수 11
870    1      SELECT * FROM [TBLX] WHERE [productid]= @1
870    1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

SELECT * FROM TBLY WHERE   productid = '111'
테이블 'tbly' . 검색수 1, 논리적읽기수 10
870    1      SELECT * FROM [TBLY] WHERE [productid]= @1
870    1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:((3)))

SELECT * FROM TBLX WHERE   productid = '111' and transactionDate = '20030708'
4      1      SELECT * FROM [TBLX] WHERE [productid]= @1 AND [transactionDate]= @2
4      1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=CONVERT_IMPLICIT(int,[@1],0) AND [PDB].[dbo].[tblx].[TransactionDate]=CONVERT_IMPLICIT(datetime,[@2],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 3

SELECT * FROM TBLY WHERE   productid = '111' and transactionDate = '20030708'
4      1      SELECT * FROM [TBLY] WHERE [productid]= @1 AND [transactionDate]= @2
4      1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(111) AND [PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000') ORDERED FORWARD PARTITION ID:((3)))
테이블 'tbly' . 검색수 1, 논리적읽기수 3

SELECT count (*) FROM TBLX WHERE   productid in ('2' , '111' ) and transactionDate = '20030708'
1      1      SELECT count (*) FROM TBLX WHERE   productid in ('2' , '111' ) and transactionDate = '20030708'
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
1      1             | --Stream Aggregate(DEFINE:([Expr1007]=Count(*)))
4      1                  | --Index Seek(OBJECT:([PDB].[dbo].[tblx].[NC_TBLX_01]), SEEK:([PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)
테이블 'tblx' . 검색수 2, 논리적읽기수 4

SELECT count (*) FROM TBLY WHERE   productid in ('2' , '111' ) and transactionDate = '20030708'
1      1      SELECT count (*) FROM TBLY WHERE   productid in ('2' , '111' ) and transactionDate = '20030708'
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1012],0)))
1      1             | --Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
4      1                  | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1005]) PARTITION ID:([PtnIds1005]))
2      1                       | --Constant Scan(VALUES:(((2)),((3))))
4      2                       | --Index Seek(OBJECT:([PDB].[dbo].[tbly].[NC_TBLY_01]), SEEK:([PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[TransactionDate]='2003-07-08 00:00:00.000' AND [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1005]))
테이블 'tbly' . 검색수 4, 논리적읽기수 8


SELECT * FROM TBLX WHERE   productid IN ('2' , '111' )
테이블 'tblx' . 검색수 2, 논리적읽기수 15
875    1      SELECT * FROM TBLX WHERE   productid IN ('2' , '111' )
875    1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)

SELECT * FROM TBLY WHERE   productid IN ('2' , '111' )
테이블 'tbly' . 검색수 4, 논리적읽기수 20
875    1      SELECT * FROM TBLY WHERE   productid IN ('2' , '111' )
875    1        | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PARTITION ID:([PtnIds1004]))
2      1             | --Constant Scan(VALUES:(((2)),((3))))
875    2             | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1004]))


SELECT productid, count (*) FROM TBLX WHERE   productid IN ('2' , '111' ) group by productid
2      1      SELECT productid, count (*) FROM TBLX WHERE   productid IN ('2' , '111' ) group by productid
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
2      1             | --Stream Aggregate(GROUP BY:([PDB].[dbo].[tblx].[productid]) DEFINE:([Expr1007]=Count(*)))
875    1                  | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid]=(2) OR [PDB].[dbo].[tblx].[productid]=(111)) ORDERED FORWARD)
테이블 'tblx' . 검색수 2, 논리적읽기수 15


SELECT productid, count (*) FROM TBLY WHERE   productid IN ('2' , '111' ) group by productid
2      1      SELECT productid, count (*) FROM TBLY WHERE   productid IN ('2' , '111' ) group by productid
0      0        | --Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1013],0)))
2      1             | --Stream Aggregate(GROUP BY:([PDB].[dbo].[tbly].[productid]) DEFINE:([Expr1013]=Count(*)))
875    1                  | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1005]) PARTITION ID:([PtnIds1005]))
2      1                       | --Constant Scan(VALUES:(((2)),((3))))
875    2                       | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid]=(2) OR [PDB].[dbo].[tbly].[productid]=(111)) ORDERED FORWARD PARTITION ID:([PtnIds1005]))
테이블 'tbly' . 검색수 4, 논리적읽기수 20


SELECT * FROM TBLX WHERE   productid BETWEEN '90' AND '111'
2875   1      SELECT * FROM [TBLX] WHERE [productid]>= @1 AND [productid]<= @2
2875   1        | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tblx].[CL_TBLX]), SEEK:([PDB].[dbo].[tblx].[productid] >= CONVERT_IMPLICIT(int,[@1],0) AND [PDB].[dbo].[tblx].[productid] <= CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
테이블 'tblx' . 검색수 1, 논리적읽기수 25


SELECT * FROM TBLY WHERE   productid BETWEEN '90' AND '111'
2875   1      SELECT * FROM [TBLY] WHERE [productid]>= @1 AND [productid]<= @2
2875   1        | --Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))
2      1             | --Constant Scan(VALUES:(((2)),((3))))
2875   2             | --Clustered Index Seek(OBJECT:([PDB].[dbo].[tbly].[CL_TBLY]), SEEK:([PDB].[dbo].[tbly].[productid] >= (90) AND [PDB].[dbo].[tbly].[productid] <= (111)) ORDERED FORWARD PARTITION ID:([PtnIds1006]))
테이블 'tbly' . 검색수 2, 논리적읽기수 27

감사합니다. 이상으로 SQL 2005의 파티션드 테이블 인덱스 특성에 대해서 조금이나마 이해 할 수 있는 자료가 되었으면 합니다. 언제나 글을 적고 나면 저 당시 왜그리 지식의 수준이 그것밖에 되지 않았을까 부끄러울 때가 많습니다. 서로 돕고 마음의 문을 열고 지식을 공유할 때 그 지식이 아름다운 꽃이 되지 않을까 생각합니다. 윗 글에 대한 의문사항이나 질문 있으시면 언제나 연락 주시길 바랍니다. SQLER.PE.KR 에서 또 뵙겠습니다.

* 도움주신분들
- NHN ORACLE 엔지니어
- 김연욱 (SQL MVP)
- 송혁 (SQL MVP)
- 김경연 (http://www.enuri.com/) DBA

* 참고도서
- EFFECTIVE ORACLE
- SQL2005 BOOKS ONLINE

'데이터베이스 > SQL Server' 카테고리의 다른 글

김종균 : SQL Server 2005 .NET CLR 통합기능  (0) 2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(1)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1)  (0) 2008.05.07

+ Recent posts

티스토리툴바