데이터엔지니어 군고구마
  • Partition Truncate Table
    2017년 04월 14일 14시 36분 43초에 업로드 된 글입니다.
    작성자: DE 군고구마
    반응형

    들어가며

    SQL Server 2016 부터 파티션 단위로 Truncate가 가능 합니다. 


    다음의 블로그를 참고하여, 만들었으며 2016 기능을 사용해 보고자 포스트를 작성하였습니다.

    참고 : https://www.mssqltips.com/sqlservertip/4436/sql-server-2016-truncate-table-with-partitions/


    위의 블로그의 경우 Partition 테이블의 최대 장점이 스트라이프로 데이터를 넣는 것이므로, NDF 파일과 파일그룹을 여러개 생성해서 만들고 있는데요.

    이 부분은 제외하고, 단순히 파티션만 나눠서 Truncate 하는 부분만 포스팅 하고자 합니다. (기능만 보면 되므로) 

    제가 알기로 대용량의 DB를 관리 할 경우 테이블을 일자 또는 월별로 쪼개서 각각 파일그룹과 NDF를 생성하여, 기간이 일정 부분 지나면, 삭제하는 식으로 운영 하였다면

    이 Partition Truncate를 통하여, 한 테이블에 넣고 그냥 해당 파티션만 Truncate 날려버리면 되므로 운영 포인트가 많이 줄어 들게 될 것 같습니다. 



    시나리오


    Step 1 

    파일그룹 및 NDF 파일 생성 - 파티션용으로 새롭게 만듭니다.

    USE [master]

    GO

    -- 파일 그룹 생성

    ALTER DATABASE [TEST] ADD FILEGROUP [PARTITON_GROUP]

    GO

     

    -- NDF파일 생성

    ALTER DATABASE [TEST] ADD FILE ( NAME = N'PARTITION', FILENAME = N'C:\MSSQL_DATA\PARTITION_GROUP.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PARTITON_GROUP]

    GO


    Step 2

    파티션 함수 및 파티션 스키마 생성 

    USE TEST

    GO

    CREATE PARTITION FUNCTION PARTITION_FN(DATETIME) AS

      RANGE RIGHT FOR VALUES

      ('2017-01-01')

    GO

     

     

    CREATE PARTITION SCHEME PARTITION_SH AS

      PARTITION PARTITION_FN  TO

      (

       [PARTITON_GROUP],

       [PRIMARY] )

    GO


    Step 3

    테이블 생성 - 파티션 스키마 명을 기재하고, 어떤 값을 기준으로 나눌 것인지 명시 합니다.

    USE TEST

    GO

     

    CREATE TABLE TEST_Partition

    (

     USERID                INT

    ,Name                  VARCHAR(20)

    ,Reg_date              DATETIME

    )ON PARTITION_SH (Reg_date)

    GO


    Step 4

    파티션이 정상적으로 생성되었는지 확인

    SELECT  table_name             = object_name(PT.object_id)

            ,       index_name             = IDX.name

            ,       part_schema            = PS.name

            ,       part_function  = PF.name

            ,       boundary               = CASE WHEN PF.boundary_value_on_right = 1 THEN 'Right(<)' ELSE 'Left(=<)' END

            ,       boundary_value = ISNULL(CONVERT(nvarchar(100),RV.value,121),N'LargePartition')

            ,       PT.rows

            ,       AU.total_pages

            ,       AU.used_pages

            ,       PT.*

      FROM                 sys.allocation_units   AS AU

     INNER  JOIN    sys.partitions                AS PT

                                  ON      AU.container_id = PT.partition_id

     INNER  JOIN    sys.indexes                           AS IDX

                                  ON      PT.object_id   = IDX.object_id

                                  AND     PT.index_id            = IDX.index_id

     INNER  JOIN    sys.partition_schemes  AS PS

                                  ON      PS.data_space_id = IDX.data_space_id

      LEFT  JOIN    sys.partition_functions       AS PF

                                  ON      PS.function_id = PS.function_id

      LEFT  JOIN    sys.partition_range_values    AS RV

                                  ON      RV.function_id = PF.function_id

                                  AND     RV.boundary_id = PT.partition_number

     WHERE  PT.object_id = object_id('TEST_Partition')


    Step 5 

    아래의 스크린샷을 보면, 둘 다 Rows가 0으로 표시 됩니다. 현재 파티션에 들어있는 row가 없기 때문입니다.


    3개의 INSERT를 통해서, 데이터를 넣어보겠습니다.

    INSERT INTO TEST_Partition VALUES (1,'주형권01','2017-01-03 02:03')

    INSERT INTO TEST_Partition VALUES (2,'주형권02','2016-12-03 02:03')

    INSERT INTO TEST_Partition VALUES (6,'주형권03','2018-12-03 02:03')

    위의 Rows를 보시면 증가했음을 보실 수 있습니다. 부등호에 의해서 2016년도 날짜로 표기된 데이터만 파티션에 들어가고, 나머지는 LargePartition에 들어갑니다.

    현재가 2017-04-14일 이므로, 2016년도 데이터가 필요 없다고 판단되어, 2017-01-01 이전의 데이터를 Truncate 시켜야 한다고 가정 하면 다음과 같이 합니다.


    Step 6

    파티션 Truncate 로 데이터 삭제 

    - 아래의 Truncate 구문에서 어떤 파티션을 지울 것인지 지정하는 곳이 있습니다. 이것은 위의 파티션 테이블을 조회하는 쿼리에서 partition_Number로 조회 가능 합니다.

    TRUNCATE TABLE TEST_Partition WITH (PARTITIONS (1));

    GO


    현재 보면 Partiotion_number가 1인 2017-01-01 이전의 데이터는 row가 0건 입니다.


    실제로 데이터는 조회하면, 우리가 지우고자 했던 데이터만 지워졌음을 볼 수 있습니다. 

    이는 Truncate 테이블이 모든 데이터를 지우는 것에 비해 Partition Truncate는 지정된 파티션만 삭제 함을 알 수 있습니다.




    그 밖에...


    1. 파티션 Truncate 는 여러개의 Partition을 삭제 가능 합니다. 범위로 Ttruncate 됩니다. 아래와 같이 지정해주시면 됩니다. 

    TRUNCATE TABLE PartitionDemo2016 WITH (PARTITIONS (2 TO 3));

    GO


    반응형
    댓글