SQL Server 2005 移行の Enterprise Edition ではパーティションテーブルの利用が可能となりました。
パーティ初認ぐされたテーブルを使用することで大規模テーブルの大量データをアーカイブ、インデックスの一部メンテナンス、データのレンジによって使用するディスクを分散させる (パーティションをファイルグループで分けた場合) というようなことが可能となります。
SQL Server では SQL Server の評価版から製品版への移行 に記載しているような形でエディションを変更することができます。
エディション変更が可能なマトリックスは サポートされているバージョンとエディションのアップグレード に記載されており、Standard から Enterprise への変更も可能です。
Enterprise Edition に移行したタイミングでパーティショニングされたテーブルを使用したいということがあるかもしれません。
そこで、非パーティションテーブルをパーティションテーブルへ切り替える方法を少しまとめてみたいと思います。
今回は以下のテーブルを使用しています。
USE [PartitionTEST] GO CREATE TABLE [dbo].[BaseTable]( [Col1] [bigint] NOT NULL, [Col2] [datetime] NOT NULL, [Col3] [nvarchar](100) NULL, [Col4] [nvarchar](100) NULL, [Col5] [bigint] NULL, [Col6] [nchar](100) NULL, [Col7] [int] NULL, CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED ([Col1] ASC) ) ON [PRIMARY]
日付でパーティショニングをするために [datetime] 型のフィールドを用意しています。
ハッシュパーティショニングの検証をできるように最後に [int] 型のフィールドも設定しています。
このテーブルに対して、以下のクエリでデータを挿入します。
SET NOCOUNT ON Truncate Table BaseTable GO DECLARE @i bigint = 1 DECLARE @hash smallint DECLARE @date datetime = '2001/1/1' DECLARE @dateadd bigint = 1 WHILE (@i <= 10000000) BEGIN SET @hash = abs(convert(bigint,convert(varbinary, NEWID()))) % 10 + 1 INSERT INTO BaseTable VALUES(@i, DATEADD(day, @dateadd, @date), NEWID(), NEWID(), ABS(RAND() * 10000), NEWID(),@hash) SET @i += 1 IF @i % 1000 = 0 BEGIN SET @dateadd += 1 END END
最後のフィールドには 1 ~ 10 の数値をランダムで設定し、データを分散できるようにしています。
# パーティションをこの列で設定し、ディスクをファイルグループで複数に分散させた場合には、適度にデータが異なるディスクに配置されます。
このテーブル用のパーティション関数を作成します。
USE [PartitionTEST] GO -- パーティション関数の作成 CREATE PARTITION FUNCTION [BaseTable_PF] (datetime) AS RANGE RIGHT FOR VALUES ( '2002/1/1','2003/1/1','2004/1/1','2005/1/1','2006/1/1','2007/1/1','2008/1/1','2009/1/1', '2010/1/1','2011/1/1','2012/1/1','2013/1/1','2014/1/1','2015/1/1','2016/1/1','2017/1/1','2018/1/1','2019/1/1', '2020/1/1','2021/1/1','2022/1/1','2023/1/1','2024/1/1','2025/1/1','2026/1/1','2027/1/1','2028/1/1','2029/1/1', '2030/1/1')
次にパーティションスキーマを作成します。
今回は [PRIMARY] ファイルグループのみを使用しています。
# 本来であればパーティション用のファイルグループを用意するのが好ましいですが、今回は単純な構成で検証をするため。
-- パーティションスキーマの作成 CREATE PARTITION SCHEME [BaseTable_PS] AS PARTITION [BaseTable_PF] ALL TO ([PRIMARY]) GO
これで、パーティションテーブルを使用する下準備は整いました。
まずは現在のテーブルの状態を確認してみます。
SELECT OBJECT_NAME(sp.object_id) , si.name , sp.partition_number , sp.rows , sp.data_compression_desc , si.type_desc FROM sys.partitions sp LEFT JOIN sys.indexes as si ON si.object_id = sp.object_id AND si.index_id = sp.index_id WHERE sp.object_id IN (OBJECT_ID(N'dbo.BaseTable')) GO
現在はパーティションが単一のテーブル (非パーティションテーブル) としてテーブルが作成されています。
この状態から先ほど作成したパーティションスキーマをテーブルに割り当てるために以下のクエリを実行します。
ALTER TABLE [dbo].[BaseTable] DROP CONSTRAINT [PK_BaseTable] GO ALTER TABLE [dbo].[BaseTable] ADD CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED ( [Col1] ASC, [Col2] ASC )ON [BaseTable_PS](Col2) GO
一度、クラスター化されたプライマリキーの制約を削除し、再度パーティションスキーマを使用して再作成を実施しています。
これによりクラスター化インデックスがパーティションスキーマを使用して構成されますのでデータがパーティショニングされて再構築されます。
今回は主キー以外の項目でパーティショニングをしていますので、プライマリキー制約にはパーティショニングで使用する項目を追加する必要があります。
実行後にパーティションの状態を確認すると以下のようにパーティショニングされたテーブルに変わっていることが確認できます。
今回は PRIMARY ファイルグループのみを使用していますので、アーカイブ用のテーブルを作成し、
CREATE TABLE [dbo].[BaseTable_Archive]( [Col1] [bigint] NOT NULL, [Col2] [datetime] NOT NULL, [Col3] [nvarchar](100) NULL, [Col4] [nvarchar](100) NULL, [Col5] [bigint] NULL, [Col6] [nchar](100) NULL, [Col7] [int] NULL, CONSTRAINT [PK_BaseTable_Archive] PRIMARY KEY CLUSTERED ([Col1] ASC) ) ON [PRIMARY] GO
パーティションをスイッチします。
ALTER TABLE BaseTable SWITCH PARTITION 1 TO BaseTable_Archive
これで特定のパーティションのデータをパージすることができました。
スイッチしたパーティションを戻す場合、
ALTER TABLE BaseTable_Archive SWITCH PARTITION 1 TO BaseTable PARTITION 1
で戻すことができるのですが、先ほどのテーブルで戻そうとするとエラーになります。
警告: テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ の指定されたパーティション 1 が、ALTER TABLE SWITCH ステートメントで無視されました。テーブルがパーティション分割されていません。
メッセージ 4982、レベル 16、状態 1、行 1
ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ のチェック制約では、切り替え先テーブル ‘PartitionTEST.dbo.BaseTable’ のパーティション 1 で定義された範囲では許可されない値が許可されます。
ALTER TABLE BaseTable SWITCH PARTITION 1 TO BaseTable_Archive
アーカイブのテーブルには、チェック制約がついておらず、スイッチ後のベーステーブルで許可されない値が格納されている可能性があります。
それを保証するためにアーカイブのテーブルにチェック制約を付与します。
ALTER TABLE [dbo].[BaseTable_Archive] ADD CONSTRAINT [BaseTable_Archive_CHK_RANGE] CHECK (([Col2]<'2002/1/1'))
今回はスイッチ先がパーティションテーブルではないため、以下のような書き方でもスイッチ可能です。
ALTER TABLE BaseTable_Archive SWITCH TO BaseTable PARTITION 1
今回のサンプルは必ずからのテーブルに対してスイッチする (年ごとにアーカイブテーブルを用意する) ことを想定していますので、複数のパーティションをアーカイブしようとすると以下のようなエラーになります。
メッセージ 4905、レベル 16、状態 1、行 1
ALTER TABLE SWITCH ステートメントが失敗しました。切り替え先テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ を空にしてください。
大規模データを効率よく処理する場合、パーティションの利用は考慮しなくてはいけないと思いますので、この辺のデザインパターンは自分でも整理しておかないとなと思いました。