[MSSQL] 테이블, 인덱스 파티션 및 적용 방법

파티션

---

사전적인 의미로 칸막이, 분할 등을 의미합니다.

데이터베이스 관점에서 보면 테이블과 인덱스를 분할하여 저장하는 개념입니다.

테이블이나 인덱스를 정의할 때 컬럼 값 기준으로 분할함으로써 각 파티션이 물리적으로 다른 곳(파일그룹)에 위치하도록 할 수 있습니다.

1. SQL Server 는 현재 (2019) Range 파티션만 제공하며 파티션 키는 단일 컬럼만 가능합니다.

2. Range 파티션 정의 시 left (경계값보다 작거나 같은) 와 right (경계값보다 작은) 옵션을 지정할 수 있습니다.

3. 파티션을 N 개를 정의하면 N+1 번의 파티션이 자동으로 생성되며, N 파티션에 속하지 않은 모든 데이터가 들어갑니다.

파티션의 필요성

---

월별 1억 건씩 10년치의 명세서가 저장되어 있는 테이블에서 특정 거래월의 데이터를 액세스 하고자 합니다.

10년은 120개월 이므로 이 중 특정 거래월인 1개월은 수치상으로 0.83 % 의 데이터를 가지게 됩니다.

그러나 0.83 % 라고 해도 1억건의 데이터를 출력해야 합니다.

WHERE 조건으로 클러스터형 인덱스가 있다면 특정 월에 해당하는 범위만 읽고서 처리할 수는 있습니다.

그러나 만약 넌클러스터 인덱스의 랜덤액세스로 1억건을 가지고 오려면 얼마나 비효율 적일까요.

그렇다고 테이블은 120건을 Scan 해서 1억건을 가지고 오는 것 또한 너무 부담스럽습니다.

이럴 때 월별 파티션을 구성하여 특정 월에 해당하는 파티션만 스캔하도록 할 수 있습니다.

파티션 테이블 생성 절차

---

1. 파일 그룹을 생성합니다. (선택)

2. 파일을 파일 그룹에 추가합니다. (선택)

3. 파티션 함수 (Partition Function)를 생성합니다. (필수)

 - 파티션 컬럼의 타입을 지정합니다.

 - 분할 방법(Left, Right) 와 경계 값을 지정합니다.

 - 경계값을 지정합니다.

CREATE PARTITION FUNCTION pf_OrderDate ( datetime )
AS RANGE RIGHT
FOR VALUES (
   '1997' /* 1996년도 파티션 */
  ,'1998' /* 1997년도 파티션 */
  ,'1999' /* 1998년도 파티션 */
)
 

4. 파티션 구성표(Partition Schema)를 생성합니다. (필수)

 - 파티션 함수에서 정의한 각 파티션의 파일그룹 위치를 지정합니다.

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO ( [primary], [primary], [primary], [secondary] )
 

5. 파티션 테이블을 생성합니다.

 - 분할하고자 하는 테이블을 파티션 구성표에 매핑합니다.

 - ON 절에서 파티션 구성표와 파티션으로 나눌 컬럼을 지정합니다.

CREATE TABLE [dbo].[Orders_Range](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [nchar](5) NULL,
    [EmployeeID] [int] NULL,
    [OrderDate] [datetime] NULL,
    [RequiredDate] [datetime] NULL,
    [ShippedDate] [datetime] NULL,
    [ShipVia] [int] NULL,
    [Freight] [money] NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) NULL,
    [ShipCity] [nvarchar](15) NULL,
    [ShipRegion] [nvarchar](15) NULL,
    [ShipPostalCode] [nvarchar](10) NULL,
    [ShipCountry] [nvarchar](15) NULL
) ON ps_OrderDate ( OrderDate )    /* 파티션 구성표와 파티션 컬럼을 기술 */
GO
 

인덱스 파티션 생성 절차

---

1. 파일그룹을 생성합니다. (선택)

2. 파일을 파일 그룹에 추가합니다. (선택)

3. 파티션 함수 (Partition Function)를 생성합니다. (선택)

4. 파티션 구성표 (Partition Schema)를 생성합니다. (선택)

5. 파티션 인덱스를 생성합니다.

 - 1 ~ 4 단계는 위에 파티션 테이블을 만드는 방법과 동일하기 때문에 생략하도록 하겠습니다.

 - 일반적으로 인덱스를 생성하는 방법입니다.

 - 인덱스가 구성될 때 Orders_Range 의 파티션 정의와 동일하게 구성됩니다.

   ( 인덱스 생성 시 아무런 옵션을 지정하지 않는 경우 Base Table 의 파티션 정의를 따라갑니다. )

CREATE NONCLUSTERED INDEX Orders_Range_X01 ON Orders_Range (OrderDate)  

 - 옵션이 지정되었기 때문에 테이블의 파티션 정의를 따라가는게 아니라 primary 파일그룹에 모든 인덱스 데이터가 저장됩니다.

CREATE NONCLUSTERED INDEX Orders_Range_X02 ON Orders_Range (OrderDate) ON [primary]

 - 파티션 구성표를 정의하여 인덱스를 생성하는 방법입니다.

 - 테이블 파티션 정의와 독립적으로 다른 파티션을 매핑할 수 있습니다.

CREATE NONCLUSTERED INDEX Orders_Range_X03 ON Orders_Range (OrderDate)
ON ps_OrderDate (OrderDate)

실행 계획, 테이블과 인덱스의 파티션이 서로 다른 경우 등에 대해서는 타 포스트에서 다루도록 하겠습니다.