MS-SQL 분할 뷰, 파티션 개념 및 관리 방법

분할된 뷰(Partitioned View)

두 개 이상의 테이블을 union all로 결합하여 하나의 뷰(view)로 정의한 것을 말합니다. 분할된 뷰는 아래처럼 여러 테이블을 하나로 묶어서 정의하기 때문에 테이블의 추가/삭제될 때마다 뷰를 재생성해야 합니다. 따라서 파티션에 비해서는 관리에 어려움이 있으나 로컬 서버와 원격 서버에 따로 위치한 테이블을 하나로 묶어 조회하고자 할 때는 분할된 뷰가 유용합니다.

CREATE VIEW view_Orders AS   SELECT  * FROM    Orders_2022 WHERE   OrderDate >= CONVERT(datetime, '20220101', 112) AND     OrderDate <  CONVERT(datetime, '20230101', 112)   UNION ALL   SELECT  * FROM    Orders_2023 WHERE   OrderDate >= CONVERT(datetime, '20230101', 112) AND     OrderDate <  CONVERT(datetime, '20240101', 112)

 

파티션(Partition)

컬럼 값의 범위를 기준으로 테이블 및 인덱스를 둘 이상의 파일 그룹에 분할하여 저장하거나 단일 파일 그룹 내에 단위를 나누어 저장하는 방식을 말합니다. 단일 파일 그룹내에 여러 파일이 있는 경우는 비례 채우기 알고리즘(proportional fill algorithm)을 이용하여 데이터를 분산합니다. 각 행을 개별 파티션에 맵핑하도록 가로로 분할합니다.

 

분할의 이점

큰 테이블 또는 인덱스를 분할하면 다음과 같은 관리 효율성과 성능 이점이 있을 수 있습니다.

l  데이터 하위 집합을 빠르고 효율적으로 전송하거나 액세스할 수 있을 뿐만 아니라 데이터 컬렉션의 무결성을 유지할 수 있습니다.

l  하나 이상의 파티션에서 유지 관리 또는 데이터 보존 작업을 더 빠르게 수행할 수 있습니다. 파티셔닝된 하위 집합을 대상으로 지정하여 작업하는 것이 전체 테이블을 대상으로 하는 것 보다 효율적입니다.

l  자주 실행하는 쿼리 유형에 따라 쿼리 성능을 향상시킬 수 있습니다. 예를 들어 쿼리 옵티마이저는 분할 열이 테이블이 조인된 열과 같을 때 둘 이상의 분할된 테이블 간에 같은 조인 쿼리를 더 빠르게 처리할 수 있습니다.

 

파티션 테이블 생성절차

지금부터 파티션 테이블 생성절차를 실습해보도록 하겠습니다.

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

데이터베이스 우클릭 > 속성 > 파일그룹 탭에서 [파일 그룹 추가] > PARTITION1과 PARTITION2 추가

 

 

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

파일 탭에서 데이터베이스 파일을 추가합니다. 이때, 파일그룹 별로 데이터를 분할하여 관리할 경로를 지정합니다. 이번 경우에는 파일그룹 [PARTITION1]은 C:\PARTITION 경로에, [PARTITION2]는 D:\PARTITION 경로로 각각 지정하였습니다.

 

3.     파티션 함수를 생성한다.>>어떤 기준으로 분할할지

아래 구문을 통해 파티션 함수를 생성합니다. 범위 형식(LEFT 또는 RIGHT)은 파티션 함수의 경계 값을 결과 파티션에 배치하는 방법을 지정합니다.

l  LEFT 범위는 가장 높은 경계 값이 파티션 내에 포함됩니다.(기본값) ‘20210101’은 2020년도 파티션에 속하게 됩니다.

l  RIGHT 범위는 경계 값을 파티션에 포함하지 않습니다. ‘20210101’은 2021년도 파티션에 속하게 됩니다.

CREATE PARTITION FUNCTION Partition_FN_TB_PT ( CHAR(8) ) AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101') GO

 

만들어진 함수를 확인하는 구문은 아래와 같습니다.

/* 파티셔닝 함수는 아래 구문을 통해 확인 가능*/ /* fanout은 파티션의 개수, 지정한 파티션 외에 마지막 파티션이 자동으로 추가된다.    boundary_value_on_right 은 range옵션인 left(=0), right(=1)을 나타냄*/ SELECT * FROM SYS.partition_functions

/* 파티션 함수별 경계값은 아래 구문으로 확인 가능*/ SELECT * FROM SYS.partition_range_values

 

4.     파티션 구성표를 생성한다.>>각 파티션을 어디에 저장할지

아래 구문을 통해 파티션 구성표(SCHEME)를 생성합니다.

CREATE PARTITION SCHEME Partition_Scheme_TB_PT AS PARTITION Partition_FN_TB_PT TO ([PARTITION1], [PARTITION1], [PARTITION1], [PARTITION2], [PARTITION2])

l  2020년 이전, 2020년, 2021년은 PARTITION1에 저장

l  2022년, 2023년 이후는 PARTITION2에 저장

 

/*파티션 구성표 확인*/ SELECT * FROM SYS.partition_schemes

/*파티션이 각각 어느 파일 그룹에 위치하는지 확인*/ SELECT * FROM SYS.destination_data_spaces AS A INNER JOIN SYS.data_spaces  AS B ON A.data_space_id = B.data_space_id

 

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

CREATE TABLE Orders ( OrderID INT, CustomerID VARCHAR(64), OrderDate CHAR(8) ) ON Partition_Scheme_TB_PT( OrderDate )

테이블 생성 후 아래 구문을 이용하여 데이터를 입력합니다.

DECLARE @i INT, @date CHAR(8) SET @i = 1 WHILE (@i < 1000) BEGIN SET @date = '20200110' INSERT INTO Orders VALUES (@i, NEWID(), @date) SET @date = '20210210' INSERT INTO Orders VALUES (@i+1, NEWID(), @date) SET @i = @i + 2 END

각 파티션별로 몇 개의 데이터가 들어있는지 확인합니다.

SELECT  $partition.Partition_FN_TB_PT(OrderDate)  AS '파티션 번호'       ,  COUNT(*) FROM    Orders GROUP BY $partition.Partition_FN_TB_PT(OrderDate)

 

지금까지 분할된 뷰와 파티션에 대해서 실습을 통해 설명드렸습니다. 파티션 테이블뿐 아니라 파티션 인덱스 등 추가적인 내용에 대해서 궁금하시다면 아래 링크 참고 부탁드립니다.

참고링크: https://learn.microsoft.com/ko-kr/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16